| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Andres Freund <andres(at)anarazel(dot)de> |
| Cc: | Adam Matthews <matthewsar(at)gmail(dot)com>, 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:28:21 |
| Message-ID: | 271346.1794871701@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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 | Andres Freund | 2026-11-16 23:57:55 | Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key |
| 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 |