Thread: Jdbc/postgres performance
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***
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
> -----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. >
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
"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
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***
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***
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
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
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