pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
5+ messages / 3 participants
[nested] [flat]
* 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
@ 2026-11-16 20:51 Adam Matthews <matthewsar@gmail.com>
2026-11-16 22:39 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 5+ messages in thread
From: Adam Matthews @ 2026-11-16 20:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
2026-11-16 20:51 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
@ 2026-11-16 22:39 ` Andres Freund <andres@anarazel.de>
2026-11-16 23:06 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
2026-11-16 23:28 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Andres Freund @ 2026-11-16 22:39 UTC (permalink / raw)
To: Adam Matthews <matthewsar@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
2026-11-16 20:51 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
2026-11-16 22:39 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Andres Freund <andres@anarazel.de>
@ 2026-11-16 23:06 ` Adam Matthews <matthewsar@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Adam Matthews @ 2026-11-16 23:06 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Andres,
Thanks for the quick reply, that makes sense. Our CI builds a database with
many schemas in a few minutes, and autovacuum never gets to analyze the
catalog in that window, so the planner was choosing blind.
We're fixing it on our side in two ways. Our test setup will analyze
pg_class, pg_index and pg_namespace once it has created the schemas. We've
also sent django-tenants a rewrite of the query that doesn't depend on
catalog stats (django-tenants#1288
<https://github.com/django-tenants/django-tenants/pull/1288;).
Thanks again for taking a look.
Adam
On Mon, Nov 16, 2026 at 4:39 PM, Andres Freund <andres@anarazel.de> wrote:
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
2026-11-16 20:51 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
2026-11-16 22:39 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Andres Freund <andres@anarazel.de>
@ 2026-11-16 23:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-17 04:21 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-11-16 23:28 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Adam Matthews <matthewsar@gmail.com>; pgsql-bugs@lists.postgresql.org
Andres Freund <andres@anarazel.de> writes:
> On 2026-11-16 12:51:25 -0800, Adam Matthews wrote:
>> 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.
> 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.
This is a guess because I've not tested, but I wonder whether the
proximate cause of this plan change wasn't commit 2ebf25e7d.
Specifically, I notice that the initial form of the query contains
10 relations (since pg_indexes references 5 tables), which we are
later able to trim to 8 relations by removing the unnecessary
joins to pg_tablespace. That's significant because the default
values of join_collapse_limit/from_collapse_limit are 8, meaning
that the trimmed form can be processed as a single query search
space whereas with 10 relations there would be some fundamentally-
arbitrary restriction on the join search. And the reason 2ebf25e7d
is relevant is that the old remove_rel_from_joinlist() code deleted
the removed rel from that structure but did nothing about flattening
subproblems, even if they'd now fit within the collapse limits.
So the planner would still be operating under an arbitrary join
order constraint. Post-2ebf25e7d, we recompute the joinlist from
scratch after outer join removal, so now we come up with a one-level
joinlist permitting a full search of the join order space.
Bottom line then is that it was pure luck that pre-16.16 did what
you wanted; probably if you cranked the collapse limits to 10 or
more, 16.15 would also deliver a plan you don't like.
I'm too lazy to install a back-rev server to check this, but
I did try the test case on master, and I observe that setting
from_collapse_limit = 4 makes it find a reasonably fast plan.
(Not as good as what it comes up with after ANALYZE, but not as
awful as the default result.) So that seems to validate my suspicion
that forcing the two pg_indexes expansions into separate join-search
silos is what makes 16.15 do what you want.
I concur with Andres' suggestion that analyzing the system catalogs
after creating a bunch of objects is the Right Thing. But if you
don't want to do the Right Thing for some reason, you could use
from_collapse_limit, or create optimization fences in the query
via OFFSET 0 sub-selects.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
2026-11-16 20:51 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
2026-11-16 22:39 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Andres Freund <andres@anarazel.de>
2026-11-16 23:28 ` Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-17 04:21 ` Adam Matthews <matthewsar@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Adam Matthews @ 2026-11-17 04:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-bugs@lists.postgresql.org
Hi Tom, Andres,
Thanks both. Tom, your theory checks out. On our repro (40 schemas × 250
tables, autovacuum off, no ANALYZE):
- 16.15 with join/from_collapse_limit = 10 or 12: 210–390 ms, the same
plan 16.16 chooses at the default 8.
- 16.16 with the limits set to 4: 2.5–4 ms, the same plan 16.15 chooses
at the default.
So 16.15 was fast by luck, as you said.
We'll do the Right Thing in our test setup and ANALYZE pg_class, pg_index
and pg_namespace once the schemas are created. The query itself comes from
django-tenants, so we've also sent them the OFFSET 0 sub-select version,
which doesn't depend on stats or collapse limits: https://
github.com/django-tenants/django-tenants/pull/1288
Appreciate you both taking the time to dig into it.
On Mon, Nov 16, 2026 at 5:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andres Freund <andres@anarazel.de> writes:
>
> On 2026-11-16 12:51:25 -0800, Adam Matthews wrote:
>
> 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.
>
> 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.
>
> This is a guess because I've not tested, but I wonder whether the
> proximate cause of this plan change wasn't commit 2ebf25e7d. Specifically,
> I notice that the initial form of the query contains 10 relations (since
> pg_indexes references 5 tables), which we are later able to trim to 8
> relations by removing the unnecessary joins to pg_tablespace. That's
> significant because the default values of
> join_collapse_limit/from_collapse_limit are 8, meaning that the trimmed
> form can be processed as a single query search space whereas with 10
> relations there would be some fundamentally- arbitrary restriction on the
> join search. And the reason 2ebf25e7d is relevant is that the old
> remove_rel_from_joinlist() code deleted the removed rel from that structure
> but did nothing about flattening subproblems, even if they'd now fit within
> the collapse limits. So the planner would still be operating under an
> arbitrary join order constraint. Post-2ebf25e7d, we recompute the joinlist
> from scratch after outer join removal, so now we come up with a one-level
> joinlist permitting a full search of the join order space.
>
> Bottom line then is that it was pure luck that pre-16.16 did what you
> wanted; probably if you cranked the collapse limits to 10 or more, 16.15
> would also deliver a plan you don't like.
>
> I'm too lazy to install a back-rev server to check this, but I did try the
> test case on master, and I observe that setting from_collapse_limit = 4
> makes it find a reasonably fast plan.
> (Not as good as what it comes up with after ANALYZE, but not as awful as
> the default result.) So that seems to validate my suspicion that forcing
> the two pg_indexes expansions into separate join-search silos is what makes
> 16.15 do what you want.
>
> I concur with Andres' suggestion that analyzing the system catalogs after
> creating a bunch of objects is the Right Thing. But if you don't want to do
> the Right Thing for some reason, you could use from_collapse_limit, or
> create optimization fences in the query via OFFSET 0 sub-selects.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-11-17 04:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-16 20:51 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 Adam Matthews <matthewsar@gmail.com>
2026-11-16 22:39 ` Andres Freund <andres@anarazel.de>
2026-11-16 23:06 ` Adam Matthews <matthewsar@gmail.com>
2026-11-16 23:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-17 04:21 ` Adam Matthews <matthewsar@gmail.com>
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