| From: | Adam Matthews <matthewsar(at)gmail(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Andres Freund <andres(at)anarazel(dot)de>, 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-17 04:21:55 |
| Message-ID: | CANdfE4cBH+SfjB1+uK3ko=G1_PMEFmQvb8ooBKgB6=Xj7KLJhA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andres Freund <andres(at)anarazel(dot)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
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-11-17 05:03:04 | BUG #19909: BRIN redo: BufferIsPinned assert in brin_xlog_insert_update() if index was dropped or truncated |
| Previous Message | Raja Sai pranav | 2026-11-17 04:17:06 | Re: BUG #19776: pg_combinebackup ignores the truncation_block_length of an intermediate incremental backup |