From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>
Cc: Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Killing off removed rels properly
Date: Mon, 20 Feb 2023 13:11:43 -0500
Message-ID: <3628050.1676916703@sss.pgh.pa.us> (raw)
In-Reply-To: <3587195.1676912023@sss.pgh.pa.us>
References: <229905.1676062220@sss.pgh.pa.us>
<CAMbWs4_zhe22XqurMdcTx0PpSovHnCJSii3LXiutF_Hpdb9bxg@mail.gmail.com>
<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>
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 = st.sid WHEN NOT MATCHED THEN INSERT
> VALUES (st.sid);
> QUERY PLAN
> -------------------------------------------------------------
> Merge on tt (cost=0.00..35.50 rows=0 width=0)
> -> Seq Scan on st (cost=0.00..35.50 rows=2550 width=10)
> (2 rows)
> which is fairly nonsensical and doesn't match v15's plan:
> Merge on tt (cost=0.15..544.88 rows=0 width=0)
> Merge on ttp tt_1
> -> Nested Loop Left Join (cost=0.15..544.88 rows=32512 width=14)
> -> Seq Scan on st (cost=0.00..35.50 rows=2550 width=4)
> -> Index Scan using ttp_pkey on ttp tt_1 (cost=0.15..0.19 rows=1 widt
> h=14)
> Index Cond: (tid = 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 <drowley@postgresql.org>
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
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, exclusion@gmail.com, guofenglinux@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Killing off removed rels properly
In-Reply-To: <3628050.1676916703@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