Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7720B9FB322 for ; Sun, 22 Oct 2006 12:11:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54972-02 for ; Sun, 22 Oct 2006 12:11:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by postgresql.org (Postfix) with SMTP id A20C39FB2D3 for ; Sun, 22 Oct 2006 12:11:20 -0300 (ADT) Received: (qmail 983 invoked from network); 22 Oct 2006 15:11:19 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 22 Oct 2006 15:11:19 -0000 In-Reply-To: <686E9FBCE580BC46BB3FE11A277C2DAE0194084C@BP1XEUEX017-C.bp1.ad.bp.com> References: <686E9FBCE580BC46BB3FE11A277C2DAE0194084C@BP1XEUEX017-C.bp1.ad.bp.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: multipart/alternative; boundary=Apple-Mail-37-686567895 Message-Id: <5D3EE611-A725-44D3-9FA5-35D7FE938BF2@fastcrypt.com> Cc: From: Dave Cramer Subject: Re: Jdbc/postgres performance Date: Sun, 22 Oct 2006 11:11:17 -0400 To: "Behl, Rohit ((Infosys))" X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.95 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200610/464 X-Sequence-Number: 21593 --Apple-Mail-37-686567895 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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 > --Apple-Mail-37-686567895 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
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 =3D = 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 =3D = 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

<= P align=3D"LEFT">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=A0=A0=A0=A0=A0=A0=A0 column_size

lds=A0=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0 numeric =A0=A0=A0=A0=A0=A0=A0 13

obj_id=A0 =A0=A0=A0=A0=A0=A0=A0= 2=A0=A0=A0=A0=A0=A0 numeric =A0=A0=A0=A0=A0=A0=A0 6

tsds=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0 numeric =A0=A0=A0=A0=A0=A0=A0 13

value=A0=A0 =A0=A0=A0=A0=A0=A0=A0 12=A0=A0=A0=A0=A0 varchar =A0=A0=A0=A0=A0=A0=A0 22

correction=A0=A0=A0=A0=A0 = 2=A0=A0=A0=A0=A0=A0 numeric =A0=A0=A0=A0=A0=A0=A0 1

delta_lds_tsds=A0 2=A0=A0=A0= =A0=A0=A0 numeric =A0=A0=A0=A0=A0=A0=A0 13

event_id=A0=A0=A0=A0=A0=A0=A0= -5=A0=A0=A0=A0=A0 bigserial=A0=A0=A0=A0=A0=A0 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=3D2. 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.=A0


Thanks

=

Regards

Rohit

=

= --Apple-Mail-37-686567895--