Re: Bad plan chosen for union all

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