| From: | Adam Matthews <matthewsar(at)gmail(dot)com> |
|---|---|
| To: | Andres Freund <andres(at)anarazel(dot)de> |
| 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 23:06:58 |
| Message-ID: | CANdfE4cnqAJb6Dic-_A0AjDdkAQWc4Z-iO1rQ3LpnXBA2JcDbQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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(at)anarazel(dot)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
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-11-16 23:28:21 | Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15 |
| Previous Message | Robert Haas | 2026-11-16 23:01:01 | Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key |