Skip site navigation (1) Skip section navigation (2)

performance issue

From: Steven varga <varga(at)yorku(dot)ca>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: performance issue
Date: 2008-03-27 04:46:03
Message-ID: Pine.LNX.4.64.0803262321270.18973@localhost (view raw or flat)
Thread:
Lists: pgsql-jdbc
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



Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-03-27 05:20:01
Subject: Re: performance issue
Previous:From: Kris JurkaDate: 2008-03-27 03:23:07
Subject: Re: Advice fetching Array data with JDBC 8.3 driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group