Re: performance issue

[Mark Lewis <mark(dot)lewis(at)mir3(dot)com>]

On Wed, 2008-03-26 at 23:46 -0500, Steven varga wrote:
> hi,
> 
> having about 10^6 records in a table indexed on names (http://www.upcdatabase.com)
> and doing a query from psql I get response time in millisec order on the 
> other hand when executing the same query through JDBC it hangs about 80 
> seconds.
> This is about 10^5 decrease in speed.
> 
> After profiling the JDBC driver simply waits for data 
> PGStream.ReceiveChar() spending enough time to do a sequencial scan on all 
> 10^6 records.
> 
>   Can anyone give a suggestion why is this lag?
> 
> 
> 
> PSQL:
> 
> test=# EXPLAIN ANALYZE SELECT count(*) from upc  where name like 'Aba%';
> QUERY PLAN
>   Aggregate  (cost=14897.01..14897.02 rows=1 width=0) (actual 
> time=0.380..0.381 rows=1 loops=1)
>     ->  Bitmap Heap Scan on upc (cost=57.67..14885.47 rows=4612 
> width=0) (actual time=0.216..0.326 rows=13 loops=1)
>           Filter: ((name)::text ~~ 'Aba%'::text)
>           ->  Bitmap Index Scan on upc_name  (cost=0.00..57.67 
> rows=4612 width=0) (actual time=0.155..0.155 rows=13 loops=1)
>                 Index Cond: (((name)::text >= 'Aba'::character varying) AND 
> ((name)::text < 'Abb'::character varying))
>   Total runtime: 0.670 ms
> (6 rows)
> 
> 
> JDBC:
> 
>   PreparedStatement count =
>          connection.prepareStatement("SELECT count(*) FROM
>  	upc WHERE name like upper(?)||'%' ");
> 
>   count.setString(1,"Aba");
>   ResultSet  rs = count.executeQuery(); //<== this takes about 82 seconds 
> to execute

Wait a minute, the statements you're running in psql and jdbc aren't
even the same.  In psql you are supplying a literal string, but in the
jdbc version you are using the upper function and the concatenate
function to assemble the like clause.  The use of those functions would
preclude most optimizations anyway-- I suspect that running "select
count(*) from upc where name like upper('Abc')||'%'" from psql would be
slow too.

That said, to get fast behavior you would probably need to do both:
change the query and also either set protocolVersion=2 or insert the
string yourself.  

-- Mark Lewis