16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
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
Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
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).
Thanks again for taking a look.
Adam
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
Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
Andres Freund 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
Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
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.
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