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.