On Wed, 3 Jan 2024 at 13:05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> QUERY PLAN
> ------------------------------------------------------------------------------------------------
> Nested Loop Left Join (cost=0.46..82.87 rows=315 width=1)
> Join Filter: (t3.t2_id = t2.id)
> -> Nested Loop Left Join (cost=0.30..32.29 rows=315 width=32)
> -> Nested Loop Left Join (cost=0.15..17.29 rows=315 width=32)
> -> Seq Scan on t4 (cost=0.00..6.15 rows=315 width=32)
> -> Memoize (cost=0.15..0.19 rows=1 width=16)
> Cache Key: t4.t2_id
> Cache Mode: logical
> -> Index Only Scan using t2_pkey on t2 (cost=0.14..0.18 rows=1 width=16)
> Index Cond: (id = t4.t2_id)
> -> Memoize (cost=0.15..0.20 rows=1 width=16)
> Cache Key: t4.t1_id
> Cache Mode: logical
> -> Index Only Scan using t1_pkey on t1 (cost=0.14..0.19 rows=1 width=16)
> Index Cond: (id = t4.t1_id)
> -> Memoize (cost=0.16..0.55 rows=1 width=33)
> Cache Key: t1.id, t1.id
> Cache Mode: logical
> -> Index Scan using t3_t1_id_index on t3 (cost=0.15..0.54 rows=1 width=33)
> Index Cond: (t1_id = t1.id)
> (20 rows)
>
> It's that last Memoize node with the duplicate cache keys that is
> failing. (It's unclear to me whether having duplicate cache keys
> is supposed to be a valid situation, and even less clear whether
> that has any direct connection to the failure.) What I guess
> is happening is that the single-row flag is getting set because
> t1.id is a primary key, despite the fact that it's coming from
> inside a join that could result in duplicates.
The problem is that singlerow is set entirely based on innerunique.
That's wrong because in this case, the unique properties include the
Join Filter, not just the parameterized qual.
I think the fix should be in get_memoize_path(). What we pass to
create_memoize_path() for the "singlerow" parameter needs to be more
than just extra->inner_unique. The Join Filter must also be empty.
David