Thread: Jdbc/postgres performance

Jdbc/postgres performance

From
"Rohit_Behl"
Date:
 Hi

We are facing performance problems in postgres while executing a query. When I execute this query on the server it
takes5-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
fromiso_midw_data_update_events events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and events.event_id >
68971124order 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
fromtable events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and events.event_id > ?::bigint order by
events.event_idlimit ? 

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.


Thanks


Regards

Rohit


**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you
arenot the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not
tocopy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This
e-mailmay contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for
anydamage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before
openingthe e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to
orfrom this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. 
***INFOSYS******** End of Disclaimer ********INFOSYS***

Re: Jdbc/postgres performance

From
"Merlin Moncure"
Date:
On 10/17/06, Rohit_Behl <Rohit_Behl@infosys.com> wrote:
> Select events.event_id, ctrl.real_name, events.tsds, events.value, events.lds, events.correction, ctrl.type,
ctrl.freqfrom table events, iso_midw_control ctrl where events.obj_id = ctrl.obj_id and events.event_id > ?::bigint
orderby events.event_id limit ? 

unfortunately parameterized limit statements cause problems due to the
fact the planner has a hard coded 'guess' of 10% of rows returned when
the plan is generated.  I mention this everyime query hints proposal
comes up :-).

best you can do is to try turning off seqscan and possibly bitmap scan
when the plan is generated.

merlin

Re: Jdbc/postgres performance

From
"Bucky Jordan"
Date:
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Merlin Moncure
> Sent: Tuesday, October 17, 2006 4:29 PM
> To: Rohit_Behl
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Jdbc/postgres performance
>
> On 10/17/06, Rohit_Behl <Rohit_Behl@infosys.com> wrote:
> > 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 ?
>
> unfortunately parameterized limit statements cause problems due to the
> fact the planner has a hard coded 'guess' of 10% of rows returned when
> the plan is generated.  I mention this everyime query hints proposal
> comes up :-).

I'm not sure that this has anything to do with hints (yes, I know hints
are a popular topic as of late..) but from the 8.1 Manual:

"This is because when the statement is planned and the planner attempts
to determine the optimal query plan, the actual values of any parameters
specified in the statement are unavailable."

After a quick search on the JDBC list, it looks like there's some recent
discussion on the subject of how to give the planner better insight for
prepared statements (the subject is "Blind Message" if you're
looking...).

So, I'm off to go read there and perhaps join the jdbc mailing list too.


But, a more general postgres question. I assume if I want to turn
prepared statements off altogether (say I'm using a jdbc abstraction
layer that likes parameterized statements, and there's other benefits to
parameterizing other than just saving on db parse/plan) can I set
max_prepared_transactions to 0? Is there any other option outside of
JDBC? (I'll be moving my other questions over to the JDBC list...)

Also, others might be interested in the JDBC documentation, which is
separate from the main Postgres manual and can be found at:
http://jdbc.postgresql.org/documentation/


- Bucky


> best you can do is to try turning off seqscan and possibly bitmap scan
> when the plan is generated.
>


Re: Jdbc/postgres performance

From
"Merlin Moncure"
Date:
On 10/18/06, Bucky Jordan <bjordan@lumeta.com> wrote:
> > On 10/17/06, Rohit_Behl <Rohit_Behl@infosys.com> wrote:
> > > 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 ?
> >

> After a quick search on the JDBC list, it looks like there's some recent
> discussion on the subject of how to give the planner better insight for
> prepared statements (the subject is "Blind Message" if you're
> looking...).
>
> So, I'm off to go read there and perhaps join the jdbc mailing list too.

this is not really a jdbc issue, just a practical problem with
prepared statements...except for the mechanism if any the jdbc driver
allows you to choose if a statement is prepared.

> But, a more general postgres question. I assume if I want to turn
> prepared statements off altogether (say I'm using a jdbc abstraction

you turn off prepared statements by not invoking sql prepare or
PQprepare.  (or, if jdbc implements its own protocol client, it's
version of PQprepare).

> layer that likes parameterized statements, and there's other benefits to
> parameterizing other than just saving on db parse/plan) can I set
> max_prepared_transactions to 0? Is there any other option outside of

this setting is for 2pc and is not relevent to the discussion :)  even
if it were, im not so sure about a setting designed to enforce a
partcular method of querying.

yes, you are correct this is not exactly the use case for hints being
discussed in -hackers. however, imho, this is much more important and
relevant so long as prepared statements continue to work the way they
do.

merlin

Re: Jdbc/postgres performance

From
Tom Lane
Date:
"Merlin Moncure" <mmoncure@gmail.com> writes:
> this is not really a jdbc issue, just a practical problem with
> prepared statements...

Specifically, that the OP is running a 7.4 backend, which was our
first venture into prepared parameterized statements.  PG 8.1 will
do better, 8.2 should do better yet.

            regards, tom lane

Re: Jdbc/postgres performance

From
"Rohit_Behl"
Date:
Hi

I made the following changes to the conf file:

enable_indexscan = true

enable_seqscan = false

We also have a large amount of data being inserted into our tables. I was just wondering if this could have an impact
onthe inserts since I guess this change is on the database. 

Please let me know.

Thanks

Regards

Rohit


________________________________

From: Rohit_Behl
Sent: Wed 18/10/2006 11:10
To: Merlin Moncure
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Jdbc/postgres performance



Hi Merlin

I have disabled seq-scan and now it works like a charm. Thanks it was a saver.



Regards

Rohit



On 10/18/06, Bucky Jordan <bjordan@lumeta.com> wrote:

> > On 10/17/06, Rohit_Behl <Rohit_Behl@infosys.com> wrote:

> > > 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 ?

> >

> After a quick search on the JDBC list, it looks like there's some

> recent discussion on the subject of how to give the planner better

> insight for prepared statements (the subject is "Blind Message" if

> you're looking...).

>

> So, I'm off to go read there and perhaps join the jdbc mailing list too.

this is not really a jdbc issue, just a practical problem with prepared statements...except for the mechanism if any
thejdbc driver allows you to choose if a statement is prepared. 

> But, a more general postgres question. I assume if I want to turn

> prepared statements off altogether (say I'm using a jdbc abstraction

you turn off prepared statements by not invoking sql prepare or PQprepare. (or, if jdbc implements its own protocol
client,it's version of PQprepare). 

> layer that likes parameterized statements, and there's other benefits

> to parameterizing other than just saving on db parse/plan) can I set

> max_prepared_transactions to 0? Is there any other option outside of

this setting is for 2pc and is not relevent to the discussion :) even if it were, im not so sure about a setting
designedto enforce a partcular method of querying. 

yes, you are correct this is not exactly the use case for hints being discussed in -hackers. however, imho, this is
muchmore important and relevant so long as prepared statements continue to work the way they do. 

merlin



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you
arenot the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not
tocopy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This
e-mailmay contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for
anydamage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before
openingthe e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to
orfrom this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. 
***INFOSYS******** End of Disclaimer ********INFOSYS***

Re: Jdbc/postgres performance

From
"Rohit_Behl"
Date:
Hi Merlin

I have disabled seq-scan and now it works like a charm. Thanks it was a saver.



Regards

Rohit



On 10/18/06, Bucky Jordan <bjordan@lumeta.com> wrote:

> > On 10/17/06, Rohit_Behl <Rohit_Behl@infosys.com> wrote:

> > > 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 ?

> >

> After a quick search on the JDBC list, it looks like there's some

> recent discussion on the subject of how to give the planner better

> insight for prepared statements (the subject is "Blind Message" if

> you're looking...).

>

> So, I'm off to go read there and perhaps join the jdbc mailing list too.

this is not really a jdbc issue, just a practical problem with prepared statements...except for the mechanism if any
thejdbc driver allows you to choose if a statement is prepared. 

> But, a more general postgres question. I assume if I want to turn

> prepared statements off altogether (say I'm using a jdbc abstraction

you turn off prepared statements by not invoking sql prepare or PQprepare. (or, if jdbc implements its own protocol
client,it's version of PQprepare). 

> layer that likes parameterized statements, and there's other benefits

> to parameterizing other than just saving on db parse/plan) can I set

> max_prepared_transactions to 0? Is there any other option outside of

this setting is for 2pc and is not relevent to the discussion :) even if it were, im not so sure about a setting
designedto enforce a partcular method of querying. 

yes, you are correct this is not exactly the use case for hints being discussed in -hackers. however, imho, this is
muchmore important and relevant so long as prepared statements continue to work the way they do. 

merlin



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you
arenot the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not
tocopy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This
e-mailmay contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for
anydamage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before
openingthe e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to
orfrom this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. 
***INFOSYS******** End of Disclaimer ********INFOSYS***

Re: Jdbc/postgres performance

From
"Heikki Linnakangas"
Date:
Rohit_Behl wrote:
> Hi
>
> I made the following changes to the conf file:
>
> enable_indexscan = true
>
> enable_seqscan = false
>
> We also have a large amount of data being inserted into our tables. I was just wondering if this could have an impact
onthe inserts since I guess this change is on the database. 

enable_seqscan shouldn't affect plain inserts, but it will affect
*every* query in the system.

I would suggest using setting  "prepareThreshold=0" in the JDBC driver
connection URL, or calling pstmt.setPrepareThreshold(0) in the
application. That tells the driver not to use server-side prepare, and
the query will be re-planned every time you execute it with the real
values of the parameters.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Jdbc/postgres performance

From
"Merlin Moncure"
Date:
On 10/18/06, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
> I would suggest using setting  "prepareThreshold=0" in the JDBC driver
> connection URL, or calling pstmt.setPrepareThreshold(0) in the
> application. That tells the driver not to use server-side prepare, and
> the query will be re-planned every time you execute it with the real
> values of the parameters.

that works.  I think another alternative is to just turn off seqscan
temporarily for the session:
set enable_seqscan=false;

and re-enable it after prepareing the statement.  however I agree that
seqscan should be enabled for normal operation. in fact, this becomes
more and more important as your database becomes really big due to
poor random i/o of hard drives.

merlin

Re: Jdbc/postgres performance

From
"Merlin Moncure"
Date:
On 10/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > this is not really a jdbc issue, just a practical problem with
> > prepared statements...
>
> Specifically, that the OP is running a 7.4 backend, which was our
> first venture into prepared parameterized statements.  PG 8.1 will
> do better, 8.2 should do better yet.

I haven't looked at 8.2 because I no longer work at my previous
position, but I was significantly affected by this problem through the
8.1 release.  The speed advantages of preparing certain types queries
are dramatic and there are some decent use cases for pramaterizing
limit and other input parameters that are difficult to guess.

merlin