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
