Message-ID: <281ae4783e0174e1cb77a560936b86a9f542bba4.camel@cybertec.at>
Subject: Re: BUG #19076: Generic query plan is extremely slow
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: premal.patel@veeva.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 08 Oct 2025 10:21:16 +0200
In-Reply-To: <19076-4d897c04a44a9627@postgresql.org>
References: <19076-4d897c04a44a9627@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/281ae4783e0174e1cb77a560936b86a9f542bba4.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-07 at 20:32 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 17.6
> Operating system:   macOS 15.6.1 (observed on other Linux based OS)
>=20
> I have an application which uses a database driver that creates prepared
> statements. I am noticing that for some queries, the first 5 executions
> after the database connection is created are performant. After this, the
> prepared statement uses the generic query plan and is incredibly slow. Th=
is
> is even if I do not change the parameters and simple re-run the same quer=
y
> several times with the same arguments. I have tried re-running ANALYZE on
> the tables and setting STATISTICS to 1000 on the rows in question with no
> luck. Here is what my query looks like:
>=20
> SELECT accounts_contacts.account_id, count(contacts.id) AS count
>     FROM accounts_contacts JOIN contacts ON contacts.id =3D
> accounts_contacts.contact_id
>     WHERE accounts_contacts.account_id IN (...) AND contacts.tenant_id =
=3D
> $1::UUID AND contacts.status =3D $2::VARCHAR
> GROUP BY accounts_contacts.account_id
>=20
> Here is the EXPLAIN output from the first 5 executions using the custom
> query plan:
>=20
> [hash join]
>
> Here is the EXPLAIN output from executions 6+ using the generic query pla=
n:
>
> [nested loop join with over 800000 iterations]

I don't think that is a bug.  The logic that decides whether to use a
generic plan or not is a heuristic, and heuristics are bound to get it
wrong occasionally.

I'd guess that the first five executions happened to use values that
had few "contacts".

Either don't use a prepared statement for this statement, or make sure
that "plan_cache_mode" is set to "force_custom_plan" for that single
prepared statement.

Yours,
Laurenz Albe