From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Adam Matthews <matthewsar@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
Date: Mon, 16 Nov 2026 18:28:21 -0500
Message-ID: <271346.1794871701@sss.pgh.pa.us> (raw)
In-Reply-To: <ebb5xq23apicvvkurpacc6rlzvyjk37ytkgyetm76g3d63gzuq@5fqpgmczusif>
References: <CANdfE4fm=Bm6tKNVdGkU_8O8m4tO-yHh8w63sibWT0-oFiRfYA@mail.gmail.com>
<ebb5xq23apicvvkurpacc6rlzvyjk37ytkgyetm76g3d63gzuq@5fqpgmczusif>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, matthewsar@gmail.com
Subject: Re: 16.16: join order regression for pg_indexes self-join (schema filter applied last), ~50x slower than 16.15
In-Reply-To: <271346.1794871701@sss.pgh.pa.us>
References: <CANdfE4fm=Bm6tKNVdGkU_8O8m4tO-yHh8w63sibWT0-oFiRfYA@mail.gmail.com> <ebb5xq23apicvvkurpacc6rlzvyjk37ytkgyetm76g3d63gzuq@5fqpgmczusif> <271346.1794871701@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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