Reusing cached prepared statement slow after 5 executions

This is likely the case where the first few "prepared statements" are not truly prepared. Once you hit five the cache kicks in and computes a generic query plan to cache. Since this plan is generic, where the first five were specific, it exhibits worse performance than queries where the where clause is known.

It's isn't a bug but you should see if you can get psql to reproduce the behavior by manually issuing a prepare. If you can do so you remove JDBC from the equation and make testing much easier.

You could also just rewrite the query to give the query planner a hand.

David J.

On Jun 26, 2011, at 10:52, Rob Gansevles <rgansevles@gmail.com> wrote:

David Johnston <polobo@yahoo.com> writes:

This is likely the case where the first few "prepared statements" are
not truly prepared. Once you hit five the cache kicks in and computes
a generic query plan to cache.

Not so much that as that JDBC decides that it should tell the backend to
start using a prepared plan. See the JDBC docs.

regards, tom lane

I can confirm, when I call ps.setPrepareThreshold(1) the query is slow
immediately, so the plan must be different with the server prepared
statements.

Thanks,

Rob

On 27 June 2011 07:50, Rob Gansevles <rgansevles@gmail.com> wrote:

I can confirm, when I call ps.setPrepareThreshold(1) the query is slow
immediately, so the plan must be different with the server prepared
statements.

You can confirm that from psql by doing

EXPLAIN ANALYSE SELECT ... ;

and then

PREPARE ps( ... ) AS SELECT ... ;
EXPLAIN ANALYSE EXECUTE ps ( ... ) ;

using your query and the parameters in question.

It is entirely possible that the plan chosen for the prepared
statement will be worse than the one used when the parameters are
known at planning time. The prepared statement doesn't know what
parameters are going to be used, so it can't always come up with the
best plan. See the notes in the PREPARE manual page:
http://www.postgresql.org/docs/9.0/static/sql-prepare.html

Regards,
Dean

Dean Rasheed wrote:

I can confirm, when I call ps.setPrepareThreshold(1) the query is

slow

immediately, so the plan must be different with the server prepared
statements.

You can confirm that from psql by doing

EXPLAIN ANALYSE SELECT ... ;

and then

PREPARE ps( ... ) AS SELECT ... ;
EXPLAIN ANALYSE EXECUTE ps ( ... ) ;

using your query and the parameters in question.

It is entirely possible that the plan chosen for the prepared
statement will be worse than the one used when the parameters are
known at planning time. The prepared statement doesn't know what
parameters are going to be used, so it can't always come up with the
best plan. See the notes in the PREPARE manual page:
http://www.postgresql.org/docs/9.0/static/sql-prepare.html

Could the parameter cursor_tuple_fraction play a role here too?

Yours,
Laurenz Albe

This behavior is in some way needed, as PreparedStatement is, commonly, used once, just to pass parameters without escaping.

Regards,
Radek

-----Original Message-----
From: Rob Gansevles
Sent: 27 czerwca 2011 08:50
To: pgsql-general
Subject: Re: [GENERAL] Reusing cached prepared statement slow after 5 executions

I can confirm, when I call ps.setPrepareThreshold(1) the query is slow
immediately, so the plan must be different with the server prepared
statements.

Thanks,

Rob

On Sun, Jun 26, 2011 at 5:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Johnston <polobo@yahoo.com> writes:

This is likely the case where the first few "prepared statements" are
not truly prepared.  Once you hit five the cache kicks in and computes
a generic query plan to cache.

Not so much that as that JDBC decides that it should tell the backend to
start using a prepared plan.  See the JDBC docs.

                       regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general