One more thing. Given this:
> The difference here is that, from the perspective of the outer query,
> the WHERE condition is a restriction clause on the "cim" relation,
> not a join clause. So it will get pushed down into the subquery
> without creating any join order constraints on the outer query.
I expected the lateral form of the query to properly use the indexes. Sure
enough, this correctly uses the index:
explain select cim.yield
from earnings
JOIN contributions on contributions.id = earnings.note_id
JOIN LATERAL
( SELECT contribution_id, max(CASE metrics.name WHEN 'Yield'::text THEN projected ELSE
NULL::double precision END) AS yield from contribution_metrics JOIN metrics ON metrics.id = metric WHERE contributions.id =
contribution_id group by contribution_id
) cim ON true
WHERE earnings.id = '\x595400456c1f1400116b3843'
However, when I try to wrap that subquery query again (e.g. as I would need
to if it were a view), it doesn't restrict:
select cim.yield
from earnings
JOIN contributions on contributions.id = earnings.note_id
JOIN LATERAL
( select * from ( SELECT contribution_id, max(CASE metrics.name WHEN 'Yield'::text THEN projected ELSE
NULL::double precision END) AS yield from contribution_metrics JOIN metrics ON metrics.id = metric
group by contribution_id ) my_view WHERE contribution_id = contributions.id
) cim ON true
WHERE earnings.id = '\x595400456c1f1400116b3843'
Is there a way I can get the restriction to be pushed down into my subquery
in this lateral form?
Best,
~Alex