Re: Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

Robert Zenz wrote:
> We are seeing a quite heavy slow down when using prepared statements in 10.1.
>
> I haven't done some thorough testing, to be honest, but what we are having is a
> select from a view (complexity of it should not matter in my opinion), something
> like this:
>
> prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and
> COLUMNB = $2;
>
> -- Actual test code follows.
>
> -- Takes ~2 seconds.
> select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;
>
> -- Takes ~10 seconds.
> execute TEST ('N', 35);
>
> Both return the same amount of rows, order of execution does not matter, these
> times are reproducible. If the same select statement is executed through JDBC it
> takes roughly 6 seconds (execution time only, no data fetched at that point).
> I'm a little bit at a loss here. Is such a slow down "expected", did we simply
> miss that prepared statements are slower? Or is there something else going on
> that we are simply not aware of?

Most likely, you are seeing the effects of a generic plan being used.

During the first five executions, the prepared statement will run a
"custom plan" generated with the actual parameters. If the cost estimate
of these plans is not cheaper than the cost estimate of the generic plan
(without substituting the actual parameters), the generic plan will be
used from the sixth execution on.

You can compare the execution plans generated with

EXPLAIN (ANALYZE, BUFFERS) EXECUTE test ('N', 35);

and

EXPLAIN (ANALYZE, BUFFERS) select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;

Yours,
Laurenz Albe