SEO

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


psql (PostgreSQL) 8.1.5
postgresql-jdbc-8.3-603



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Guillaume Cottenceau wrote:
> Kris Jurka <books 'at' ejurka.com> writes:
>
>> When using a PreparedStatement the server must come up with a plan
>> that works for all parameter values. Since the parameter is unknown,
>> the generated plan doesn't use an index. Your options are to
>> interpolate the parameter yourself or connect using the
>> protocolVersion=2 URL option which will make the driver do the
>> interpolation prior to passing the query on to the server.
>
> Kris, actually, is this behaviour considered a bug or a feature?

It was discussed at some length recently, check the list archives. IIRC
the consensus was to fix the server - essentially, add a mode where the
driver can tell the server "please replan this statement every time you
get new parameters", which would allow all the normal planner
optimizations (constant folding, etc) to be made since it does not need
to produce a general-purpose plan for all possible parameter values.

-O

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Steven varga wrote:
>
> having about 10^6 records in a table indexed on names 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.
>
> PreparedStatement count =
> connection.prepareStatement("SELECT count(*) FROM
> upc WHERE name like upper(?)||'%' ");
>

When using a PreparedStatement the server must come up with a plan that
works for all parameter values. Since the parameter is unknown, the
generated plan doesn't use an index. Your options are to interpolate
the parameter yourself or connect using the protocolVersion=2 URL option
which will make the driver do the interpolation prior to passing the
query on to the server.

Kris Jurka

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc