| From: | Adam Matthews <matthewsar(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 |
| Date: | 2026-11-16 20:51:25 |
| Message-ID: | CANdfE4fm=Bm6tKNVdGkU_8O8m4tO-yHh8w63sibWT0-oFiRfYA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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")
Repro (psql, fresh cluster, autovacuum=off)
-------------------------------------------
-- N schemas with identical table names, each table with a PK and two
indexes
\set nschemas 40
SELECT format('CREATE SCHEMA s%s', s) FROM generate_series(0, :nschemas -
1) s \gexec
SELECT format('CREATE TABLE s%s.t%s (id serial PRIMARY KEY, a int, b text,
c timestamptz)', s, t),
format('CREATE INDEX ON s%s.t%s (a)', s, t),
format('CREATE INDEX ON s%s.t%s (b, c)', s, t)
FROM generate_series(0, :nschemas - 1) s, generate_series(0, 249) t \gexec
-- (no ANALYZE: catalogs keep the stats from initdb, as after heavy DDL
before autovacuum runs)
-- Pair the indexes of one table in two schemas (django-tenants
clone_schema() does this per table)
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY ON)
SELECT old.indexname, new.indexname
FROM pg_indexes old, pg_indexes new
WHERE old.schemaname = 's0'
AND new.schemaname = 's1'
AND old.tablename = new.tablename
AND old.tablename = 't7'
AND old.indexname <> new.indexname
AND regexp_replace(old.indexdef, E'.*USING', '') =
regexp_replace(new.indexdef, E'.*USING', '')
ORDER BY old.indexdef, new.indexdef;
Plan on 16.15
-------------
Sort (cost=44.18..44.19 rows=1 width=192) (actual rows=0 loops=1)
Sort Key: (pg_get_indexdef(i.oid)), (pg_get_indexdef(i_1.oid))
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=10.02..44.17 rows=1 width=192) (actual rows=0
loops=1)
Join Filter: ((i.relname <> i_1.relname) AND
(regexp_replace(pg_get_indexdef(i.oid), '.*USING'::text, ''::text) =
regexp_replace(pg_get_indexdef(i_1.oid), '.*USING'::text, ''::text)))
Rows Removed by Join Filter: 9
-> Nested Loop (cost=5.01..22.07 rows=1 width=132) (actual
rows=3 loops=1)
Join Filter: (c.relnamespace = n.oid)
Rows Removed by Join Filter: 117
-> Nested Loop (cost=5.01..21.01 rows=1 width=136) (actual
rows=120 loops=1)
-> Nested Loop (cost=4.72..20.50 rows=1 width=72)
(actual rows=120 loops=1)
-> Index Scan using pg_class_relname_nsp_index
on pg_class c (cost=0.41..8.44 rows=1 width=72) (actual rows=40 loops=1)
Index Cond: (relname = 't7'::name)
Filter: (relkind = ANY
('{r,m,p}'::"char"[]))
-> Bitmap Heap Scan on pg_index x
(cost=4.31..12.04 rows=2 width=8) (actual rows=3 loops=40)
Recheck Cond: (indrelid = c.oid)
Heap Blocks: exact=42
-> Bitmap Index Scan on
pg_index_indrelid_index (cost=0.00..4.30 rows=2 width=0) (actual rows=3
loops=40)
Index Cond: (indrelid = c.oid)
-> Index Scan using pg_class_oid_index on pg_class i
(cost=0.29..0.51 rows=1 width=72) (actual rows=1 loops=120)
Index Cond: (oid = x.indexrelid)
Filter: (relkind = ANY ('{i,I}'::"char"[]))
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=1
width=4) (actual rows=1 loops=120)
Filter: (nspname = 's0'::name)
Rows Removed by Filter: 42
-> Nested Loop (cost=5.01..22.07 rows=1 width=132) (actual
rows=3 loops=3)
Join Filter: (c_1.relnamespace = n_1.oid)
Rows Removed by Join Filter: 117
-> Nested Loop (cost=5.01..21.01 rows=1 width=136) (actual
rows=120 loops=3)
-> Nested Loop (cost=4.72..20.50 rows=1 width=72)
(actual rows=120 loops=3)
-> Index Scan using pg_class_relname_nsp_index
on pg_class c_1 (cost=0.41..8.44 rows=1 width=72) (actual rows=40 loops=3)
Index Cond: (relname = 't7'::name)
Filter: (relkind = ANY
('{r,m,p}'::"char"[]))
-> Bitmap Heap Scan on pg_index x_1
(cost=4.31..12.04 rows=2 width=8) (actual rows=3 loops=120)
Recheck Cond: (indrelid = c_1.oid)
Heap Blocks: exact=126
-> Bitmap Index Scan on
pg_index_indrelid_index (cost=0.00..4.30 rows=2 width=0) (actual rows=3
loops=120)
Index Cond: (indrelid = c_1.oid)
-> Index Scan using pg_class_oid_index on pg_class
i_1 (cost=0.29..0.51 rows=1 width=72) (actual rows=1 loops=360)
Index Cond: (oid = x_1.indexrelid)
Filter: (relkind = ANY ('{i,I}'::"char"[]))
-> Seq Scan on pg_namespace n_1 (cost=0.00..1.05 rows=1
width=4) (actual rows=1 loops=360)
Filter: (nspname = 's1'::name)
Rows Removed by Filter: 42
Planning Time: 1.915 ms
Execution Time: 3.451 ms
Plan on 16.16
-------------
Sort (cost=44.18..44.19 rows=1 width=192) (actual rows=0 loops=1)
Sort Key: (pg_get_indexdef(i.oid)), (pg_get_indexdef(i_1.oid))
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=10.02..44.17 rows=1 width=192) (actual rows=0
loops=1)
Join Filter: (c_1.relnamespace = n_1.oid)
-> Nested Loop (cost=10.02..43.11 rows=1 width=140) (actual
rows=0 loops=1)
Join Filter: (c.relnamespace = n.oid)
-> Nested Loop (cost=10.02..42.04 rows=1 width=144)
(actual rows=0 loops=1)
Join Filter: ((i.relname <> i_1.relname) AND
(regexp_replace(pg_get_indexdef(i.oid), '.*USING'::text, ''::text) =
regexp_replace(pg_get_indexdef(i_1.oid), '.*USING'::text, ''::text)))
Rows Removed by Join Filter: 14400
-> Nested Loop (cost=5.01..21.01 rows=1 width=136)
(actual rows=120 loops=1)
-> Nested Loop (cost=4.72..20.50 rows=1
width=72) (actual rows=120 loops=1)
-> Index Scan using
pg_class_relname_nsp_index on pg_class c_1 (cost=0.41..8.44 rows=1
width=72) (actual rows=40 loops=1)
Index Cond: (relname = 't7'::name)
Filter: (relkind = ANY
('{r,m,p}'::"char"[]))
-> Bitmap Heap Scan on pg_index x_1
(cost=4.31..12.04 rows=2 width=8) (actual rows=3 loops=40)
Recheck Cond: (indrelid = c_1.oid)
Heap Blocks: exact=42
-> Bitmap Index Scan on
pg_index_indrelid_index (cost=0.00..4.30 rows=2 width=0) (actual rows=3
loops=40)
Index Cond: (indrelid =
c_1.oid)
-> Index Scan using pg_class_oid_index on
pg_class i_1 (cost=0.29..0.51 rows=1 width=68) (actual rows=1 loops=120)
Index Cond: (oid = x_1.indexrelid)
Filter: (relkind = ANY ('{i,I}'::"char"[]))
-> Nested Loop (cost=5.01..21.01 rows=1 width=136)
(actual rows=120 loops=120)
-> Nested Loop (cost=4.72..20.50 rows=1
width=72) (actual rows=120 loops=120)
-> Index Scan using
pg_class_relname_nsp_index on pg_class c (cost=0.41..8.44 rows=1 width=72)
(actual rows=40 loops=120)
Index Cond: (relname = 't7'::name)
Filter: (relkind = ANY
('{r,m,p}'::"char"[]))
-> Bitmap Heap Scan on pg_index x
(cost=4.31..12.04 rows=2 width=8) (actual rows=3 loops=4800)
Recheck Cond: (indrelid = c.oid)
Heap Blocks: exact=5040
-> Bitmap Index Scan on
pg_index_indrelid_index (cost=0.00..4.30 rows=2 width=0) (actual rows=3
loops=4800)
Index Cond: (indrelid = c.oid)
-> Index Scan using pg_class_oid_index on
pg_class i (cost=0.29..0.51 rows=1 width=68) (actual rows=1 loops=14400)
Index Cond: (oid = x.indexrelid)
Filter: (relkind = ANY ('{i,I}'::"char"[]))
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=1
width=4) (never executed)
Filter: (nspname = 's0'::name)
-> Seq Scan on pg_namespace n_1 (cost=0.00..1.05 rows=1 width=4)
(never executed)
Filter: (nspname = 's1'::name)
Planning Time: 1.666 ms
Execution Time: 177.401 ms
Real-world query
----------------
django-tenants clone_schema() (
https://github.com/django-tenants/django-tenants, django_tenants/clone.py),
per table:
SELECT old.indexname, new.indexname
FROM pg_indexes old, pg_indexes new
WHERE old.schemaname = source_schema
AND new.schemaname = dest_schema
AND old.tablename = new.tablename
AND old.tablename = tblname
AND old.indexname <> new.indexname
AND regexp_replace(old.indexdef, E'.*USING','') =
regexp_replace(new.indexdef, E'.*USING','')
ORDER BY old.indexdef, new.indexdef;
We've pinned 16.15 in CI for now. Happy to test a patch or run more
variants.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Haas | 2026-11-16 21:36:55 | Re: BUG #19908: pg_upgrade --check crashes instance if running in a different PID Namespace then the old cluster |
| Previous Message | Tom Lane | 2026-11-16 20:40:28 | Re: BUG #19908: pg_upgrade --check crashes instance if running in a different PID Namespace then the old cluster |