Re: BUG #19076: Generic query plan is extremely slow

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