Re: Reusing cached prepared statement slow after 5 executions

On 27 June 2011 07:50, Rob Gansevles <rgansevles(at)gmail(dot)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