To: pgsql-performance@postgresql.org
From: Thomas Kellerer <shammat@gmx.net>
Subject: Query runs slower as prepared statement - identical execution plans
Message-ID: <919dfbb7-8704-d93d-07c1-51446b3c07bf@gmx.net>
Date: Wed, 26 Jan 2022 08:18:59 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/919dfbb7-8704-d93d-07c1-51446b3c07bf%40gmx.net>
Precedence: bulk

Hello,

I have a strange case of a query that runs substantially slower when run a=
s a
Java PreparedStatement with placeholders, compared to using constant value=
s in
the SQL string.

In my experience, the reason for this is usually a different execution pla=
n 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 =3D ANY ($2)
     and some_uuid_column =3D ANY (.....)

For various reasons the list of values for the some_uuid_column =3D ANY(..=
) condition
is always passed as constant values.

The plan is quite reasonable using a Bitmap Heap Scan in both cases on "so=
me_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 s=
o 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?