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

Re: Jdbc/postgres performance

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: "Behl, Rohit ((Infosys))" <Rohit(dot)Behl(at)uk(dot)bp(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Jdbc/postgres performance
Date: 2006-10-22 15:11:17
Message-ID: 5D3EE611-A725-44D3-9FA5-35D7FE938BF2@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 17-Oct-06, at 3:05 PM, Behl, Rohit ((Infosys)) wrote:

> Hi
>
> We are facing performance problems in postgres while executing a  
> query. When I execute this query on the server it takes 5-10  
> seconds. Also I get good performance while executing this query  
> from my code in java with the hard codes values. I face severe  
> performance problems when I run it using a prepared statement.
>
> The query is as follows:
>
> Select events.event_id, ctrl.real_name, events.tsds, events.value,  
> events.lds, events.correction, ctrl.type, ctrl.freq from  
> iso_midw_data_update_events events, iso_midw_control ctrl where  
> events.obj_id = ctrl.obj_id and events.event_id > 68971124 order by  
> events.event_id limit 2000
>
> The above query executes in 5-10 seconds.
>
> However the below query executes in 8 mins:
>
> Select events.event_id, ctrl.real_name, events.tsds, events.value,  
> events.lds, events.correction, ctrl.type, ctrl.freq from table  
> events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and  
> events.event_id > ?::bigint order by events.event_id limit ?
> setLong(1, 68971124);
>
> setInt(2, 2000);
>
> The table has close to 5 million rows. The table has the following  
> index:
>
> iso_midw_data_update_events_event_id_key
>
> iso_midw_data_update_events_lds_idx
>
> iso_midw_data_update_events_obj_id_idx
>
>
> The table is described as follows:
>
> Columns_name data_type type_name        column_size
>
> lds             2       numeric         13
>
> obj_id          2       numeric         6
>
> tsds            2       numeric         13
>
> value           12      varchar         22
>
> correction      2       numeric         1
>
> delta_lds_tsds  2       numeric         13
>
> event_id        -5      bigserial       8
>
> Please tell me what I am missing while setting the prepared  
> statement. I am using postgres7.4.2. and postgresql-8.1-407.jdbc3.jar.

Try the same query with protocolVersion=2. There are some issues with  
prepared statements being slower if the parameters are not the same  
type as the column being compared to.

protocol version 2 will issue the query exactly the same as psql  
does. Also note that your two queries are not identical. In the  
prepared query you cast to bigint ?

Version 8.1.x handles this better I think.
>
> Thanks
>
>
> Regards
>
> Rohit
>

In response to

pgsql-performance by date

Next:From: Bucky JordanDate: 2006-10-22 18:59:39
Subject: Re: New hardware thoughts
Previous:From: Ben SuffolkDate: 2006-10-21 18:17:37
Subject: Re: Optimizing disk throughput on quad Opteron

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