| From: | Andres Freund <andres(at)anarazel(dot)de> |
|---|---|
| To: | Adam Matthews <matthewsar(at)gmail(dot)com> |
| Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 |
| Date: | 2026-11-16 22:39:28 |
| Message-ID: | ebb5xq23apicvvkurpacc6rlzvyjk37ytkgyetm76g3d63gzuq@5fqpgmczusif |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
On 2026-11-16 12:51:25 -0800, Adam Matthews wrote:
> PostgreSQL version: 16.16 (Debian 16.16-1.pgdg13+1, official docker image
> postgres:16), compared with 16.15 (Debian 16.15-1.pgdg13+2)
> OS: Debian 13 (docker), x86_64. Default configuration apart from
> autovacuum=off for a stable repro.
>
> Description
> -----------
> After the official postgres:16 image moved from 16.15 to 16.16, our test
> suite slowed down 1.6-2.5x.
> Almost all of the extra time was in one query that django-tenants'
> clone_schema() function runs per
> table: a self-join of pg_indexes that pairs a table's indexes in a source
> and a destination schema.
> Per call it went from 0.5-0.9 ms to 45-300 ms.
> On 16.15 the planner joins pg_namespace (the schemaname filter) early, so
> only that table's indexes in
> the two target schemas reach the expensive join filter (pg_get_indexdef() +
> regexp_replace()). On 16.16
> the pg_namespace joins move to the top of the plan. Every same-named table
> in every schema gets paired
> first, the join filter runs on all of those pairs (14,400 rather than 117
> in the repro below), and the
> schema filter is applied last. Cost grows with the square of the number of
> schemas that share table
> names, which is the normal layout for schema-per-tenant applications.
>
> The row estimates are the same on both versions (both misestimate
> pg_class_relname_nsp_index at
> rows=1, actual 40, because the catalogs haven't been analyzed since the
> DDL). Only the join order
> differs, and both plans have the same total cost estimate (44.18), so this
> looks like a tie between
> join orders that 16.16 breaks the other way. One other difference in the
> plans: the estimated width
> for pg_class i is 72 on 16.15 and 68 on 16.16. Running ANALYZE first gives
> the good plan on both
> versions, so the regression shows when catalog statistics are stale. That
> is the usual state right
> after heavy DDL, such as creating schemas in a test run or during a tenant
> clone, before autovacuum
> gets to the catalogs.
> Same data, same query:
> 16.15: Execution Time 3.451 ms (Rows Removed by Join Filter: 9 + 117 +
> 117)
> 16.16: Execution Time 177.401 ms (Rows Removed by Join Filter: 14400;
> pg_namespace scans "never executed")
Given that this is just a different choice of equivalently costed plans, in a
database without useful stats, I don't see what we could potentially do about
this. Anything that would slightly prefer the old plan could just as well
cause regressions for lots of other queries.
The planner has no information here, so it's really just guessing based on
stats before your many schemas + tables + indexes were created. I think
you're going to have to make sure that something analyzes the catalog at some
point.
Or you can make the query better, I'm pretty sure you can write that to be a
lot faster, regardless of stats.
But really, you really can't rely on query plans to work well if you query
data that has a few orders of magnitude more data since it was last analyzed.
Greetings,
Andres Freund
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-11-16 22:46:17 | Re: BUG #19906: Backend crash (signal 11) creating a LANGUAGE sql function that returns a composite type from a sub- |
| Previous Message | David Rowley | 2026-11-16 22:34:21 | Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY |