From: Laurenz Albe <laurenz.albe@cybertec.at>
To: premal.patel@veeva.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19076: Generic query plan is extremely slow
Date: Wed, 08 Oct 2025 10:21:16 +0200
Message-ID: <281ae4783e0174e1cb77a560936b86a9f542bba4.camel@cybertec.at> (raw)
In-Reply-To: <19076-4d897c04a44a9627@postgresql.org>
References: <19076-4d897c04a44a9627@postgresql.org>
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)
>
> 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. This
> is even if I do not change the parameters and simple re-run the same query
> 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:
>
> SELECT accounts_contacts.account_id, count(contacts.id) AS count
> FROM accounts_contacts JOIN contacts ON contacts.id =
> accounts_contacts.contact_id
> WHERE accounts_contacts.account_id IN (...) AND contacts.tenant_id =
> $1::UUID AND contacts.status = $2::VARCHAR
> GROUP BY accounts_contacts.account_id
>
> Here is the EXPLAIN output from the first 5 executions using the custom
> query plan:
>
> [hash join]
>
> Here is the EXPLAIN output from executions 6+ using the generic query plan:
>
> [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
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-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, premal.patel@veeva.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19076: Generic query plan is extremely slow
In-Reply-To: <281ae4783e0174e1cb77a560936b86a9f542bba4.camel@cybertec.at>
* 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