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

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Robert Zenz <robert(dot)zenz(at)sibvisions(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.
Date: 2018-02-13 09:32:20
Message-ID: 1518514340.2525.16.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Luca Ferrari 2018-02-13 10:03:01 Re: session_replication_role meaning?
Previous Message Robert Zenz 2018-02-13 09:25:22 Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.