Re: Ignored join clause

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>  Andrew> My suspicion is that this is an interaction between lateral and
>  Andrew> join reordering. Looking into it further.

> I think I was wrong, and that in fact this is a much more general
> problem which amounts to a lack of communication between
> get_joinrel_parampathinfo and extract_actual_join_clauses.

I've not got to the bottom of it yet either, but I notice that if you
replace the VALUES thingy with a plain table, the bug goes away:

regression=# create table q1(x int, y int);
CREATE TABLE
regression=# insert into q1 values (1, 10), (2, 20);
INSERT 0 2
regression=# SELECT *
FROM t
LEFT JOIN q1 ON q1.x = t.x
LEFT JOIN unnest(ys) q2 (y) ON q2.y = q1.y;
 x |   ys    | x | y  | y
---+---------+---+----+----
 1 | {10,20} | 1 | 10 | 10
(1 row)

The plan's much saner-looking, too:

 Nested Loop Left Join  (cost=246.68..32758.05 rows=14351 width=48)
   Output: t.x, t.ys, q1.x, q1.y, q2.y
   Join Filter: (q2.y = q1.y)
   ->  Merge Left Join  (cost=246.68..468.29 rows=14351 width=44)
         Output: t.x, t.ys, q1.x, q1.y
         Merge Cond: (t.x = q1.x)
         ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
               Output: t.x, t.ys
               Sort Key: t.x
               ->  Seq Scan on public.t  (cost=0.00..22.70 rows=1270 width=36)
                     Output: t.x, t.ys
         ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
               Output: q1.x, q1.y
               Sort Key: q1.x
               ->  Seq Scan on public.q1  (cost=0.00..32.60 rows=2260 width=8)
                     Output: q1.x, q1.y
   ->  Function Scan on pg_catalog.unnest q2  (cost=0.00..1.00 rows=100 width=4)
         Output: q2.y
         Function Call: unnest(t.ys)

So I'm suspicious that the real issue here has to do with the weird
subselect representation we use for VALUES; either that's broken in
itself somehow, or more likely the planner gets confused while
flattening it.

            regards, tom lane