From: | jwollman <jwollman(at)yahoo(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Slow statement when using JDBC |
Date: | 2013-07-30 18:32:34 |
Message-ID: | 1375209154731-5765663.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
There was no end to this thread so, as I have just run into this problem, I
thought I post.
I can validate Yazen problem using Prepared statements as not a JDBC problem
but actually a problem that exists in pre-9.2 versions of Postgres.
I work for a company that provides a product with an embedded database. We
currently have Postgres version 9.0.3 and have run into the exact same
problem: Prepared Statements with Bind Variables run significantly slower
than statements without Bind Variables either via JDBC or in database
itself.
I took the slow running Prepared Statement with Bind Variables from our
application and replicated the same exact problem by running it against same
version 9.0.3 of Postgres in pg_Admin using Prepare foo as <prepared
statement>; Execute foo ('xxxx', 'yyyy');
The query took 40,000ms to run.
(I also took same query and resolved bind variables to actual
values and ran in the same manner as a prepared statement. The query took
111ms to run.)
I also took the same query with Bind Variables and ran as Prepared Statement
using the same dataset but on version 9.2.4 of Postgres.
The query took 77ms and used a reasonable explain plan.
Conclusion: this appears to be a database problem due to odd explain plan
for Prepared Statements with Bind Variables. Kevin Grittner's comment in
this thread seems to be a good explanation.
Good news: that this was validated as a bug and was fixed by Tom Lane in
version 9.2 of Postgres. See:
http://stackoverflow.com/questions/12368338/how-to-make-postgresql-optimizer-to-build-execution-plan-after-binding-parameter
<http://stackoverflow.com/questions/12368338/how-to-make-postgresql-optimizer-to-build-execution-plan-after-binding-parameter>
Thanks,
Jon Wollman
Portfolio Data Architect, Rapid7
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-statement-when-using-JDBC-tp3368379p5765663.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeremy Whiting | 2013-07-31 11:36:51 | Re: [BUGS] Incorrect response code after XA recovery |
Previous Message | Alvaro Herrera | 2013-07-30 16:17:27 | Re: [BUGS] Incorrect response code after XA recovery |