Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT67h-000000006XJ-2X4y for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 23:28:30 +0000 Received: from localhost ([127.0.0.1]:58010 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xT67f-00000001tIS-3qNC for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 23:28:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:41972) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT67f-00000001tIK-2oSM for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 23:28:27 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT67d-000000004Ok-0isg for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 23:28:27 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 6AGNSLCi271347; Mon, 16 Nov 2026 18:28:21 -0500 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 6AGNSLCi271347 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1794871701; bh=HmXbTXEy23gzKlwJdHfqgnoVtPQGOYQi1Gw2DTCTDvk=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=eGotZzqL+TVfOnbuzYkhSsH3R8hEuOCftfj6mLKBxKkTGaWFpkB5Obz2VI5qjEc7u pz06ij/CmCq7srKJcld1gvBdWKjlmY+0K7c9XrAqa9/U9PXsDmF21/+Hk3lu/azhvl 1OZldRbrn1l/Svnxklq5XZfwXitjRm7MNF9wD2w8= From: Tom Lane To: Andres Freund cc: Adam Matthews , 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 In-reply-to: References: Comments: In-reply-to Andres Freund message dated "Mon, 16 Nov 2026 17:39:28 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <271345.1794871701.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 16 Nov 2026 18:28:21 -0500 Message-ID: <271346.1794871701@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 tes= t >> 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 sour= ce >> 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 wel= l > 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 =3D 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