From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Query runs slower as prepared statement - identical execution plans
Date: Wed, 26 Jan 2022 08:18:59 +0100
Message-ID: <919dfbb7-8704-d93d-07c1-51446b3c07bf@gmx.net> (raw)
Hello,
I have a strange case of a query that runs substantially slower when run as a
Java PreparedStatement with placeholders, compared to using constant values in
the SQL string.
In my experience, the reason for this is usually a different execution plan for the
prepared statement.
However in this case, the plans are identical but the prepared statements runs substantially
slower than the "non-prepared" plan: 1800ms to 2000ms vs. 250ms to 350ms
I can't disclose the query, but the basic structure is this:
select ...
from some_table
where jsonb_column #>> $1 = ANY ($2)
and some_uuid_column = ANY (.....)
For various reasons the list of values for the some_uuid_column = ANY(..) condition
is always passed as constant values.
The plan is quite reasonable using a Bitmap Heap Scan in both cases on "some_uuid_column"
I uploaded the (anonymized) plans to explain.depesz:
Fast execution: https://explain.depesz.com/s/QyFR
Slow execution: https://explain.depesz.com/s/mcQz
The "prepared" plan was created using psql, not through JDBC:
PREPARE p1(text,text) AS ...
EXPLAIN (analyze, buffers, timing, verbose)
EXECUTE p1 ('{...}', '{....}')
But the runtime is pretty much what I see when doing this through Java.
My question is: why is processing the query through a prepared statement so much slower?
This happens on a test system running Postgres 13.2 on CentOS, and another test system
running 13.5 on Ubuntu.
For the time being, we can switch off the use of a PreparedStatement, but I'm also
interesting to know the underlying root cause.
Any ideas?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: shammat@gmx.net
Subject: Re: Query runs slower as prepared statement - identical execution plans
In-Reply-To: <919dfbb7-8704-d93d-07c1-51446b3c07bf@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox