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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Robert Zenz
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?
Reply | Threaded
Open this post in threaded view
|

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

Laurenz Albe
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

Previous Thread Next Thread