Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUAdj-0001Oo-TK for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Feb 2023 18:11:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUAdh-0006Lu-Db for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Feb 2023 18:11:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUAdh-0006Lk-49 for pgsql-hackers@lists.postgresql.org; Mon, 20 Feb 2023 18:11:49 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUAde-0005hu-8y for pgsql-hackers@lists.postgresql.org; Mon, 20 Feb 2023 18:11:48 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 31KIBhoI3628051; Mon, 20 Feb 2023 13:11:43 -0500 From: Tom Lane To: David Rowley cc: Alexander Lakhin , Richard Guo , pgsql-hackers@lists.postgresql.org Subject: Re: Killing off removed rels properly In-reply-to: <3587195.1676912023@sss.pgh.pa.us> References: <229905.1676062220@sss.pgh.pa.us> <1343881.1676313553@sss.pgh.pa.us> <36bee393-b351-16ac-93b2-d46d83637e45@gmail.com> <3582657.1676910808@sss.pgh.pa.us> <3587195.1676912023@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Mon, 20 Feb 2023 11:53:43 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3628049.1676916703.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 20 Feb 2023 13:11:43 -0500 Message-ID: <3628050.1676916703@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I wrote: > Hmm, there's something else going on here. After getting rid of the > assertion failure, I see that the plan looks like > # explain MERGE INTO tt USING st ON tt.tid =3D st.sid WHEN NOT MATCHED T= HEN INSERT = > VALUES (st.sid); > QUERY PLAN = > ------------------------------------------------------------- > Merge on tt (cost=3D0.00..35.50 rows=3D0 width=3D0) > -> Seq Scan on st (cost=3D0.00..35.50 rows=3D2550 width=3D10) > (2 rows) > which is fairly nonsensical and doesn't match v15's plan: > Merge on tt (cost=3D0.15..544.88 rows=3D0 width=3D0) > Merge on ttp tt_1 > -> Nested Loop Left Join (cost=3D0.15..544.88 rows=3D32512 width=3D= 14) > -> Seq Scan on st (cost=3D0.00..35.50 rows=3D2550 width=3D4) > -> Index Scan using ttp_pkey on ttp tt_1 (cost=3D0.15..0.19 r= ows=3D1 widt > h=3D14) > Index Cond: (tid =3D st.sid) > It looks like we're somehow triggering the elide-a-left-join code > when we shouldn't? A quick bisect session shows that this broke at 3c569049b7b502bb4952483d19ce622ff0af5fd6 is the first bad commit commit 3c569049b7b502bb4952483d19ce622ff0af5fd6 Author: David Rowley Date: Mon Jan 9 17:15:08 2023 +1300 Allow left join removals and unique joins on partitioned tables = but I suspect that that's merely exposed a pre-existing deficiency in MERGE planning. ttp should not have been a candidate for join removal, because the plan should require fetching (at least) its ctid. I suspect that somebody cowboy-coded the MERGE support in such a way that the required row identity vars don't get added to relation targetlists, or at least not till too late to stop join removal. I've not run it to earth though. But while I'm looking at this, 3c569049b seems kind of broken on its own terms. Why is it populating so little of the IndexOptInfo for a partitioned index? I realize that we're not going to directly plan anything using such an index, but not populating fields like sortopfamily seems like it's at least leaving stuff on the table, and at worst making faulty decisions. regards, tom lane