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