Re: Bad plan chosen for union all

2017-11-28 Thread Alex Reece
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

Re: Bad plan chosen for union all

2017-11-28 Thread Tom Lane
Alex Reece writes: > I managed to reduce my test case: the following query does not take > advantage of the index on contribution metrics. Yeah. What you're wishing is that the planner would push a join condition down into a subquery, but it won't do that at present. Doing so

Re: Bad plan chosen for union all

2017-11-28 Thread Alex Reece
I managed to reduce my test case: the following query does not take advantage of the index on contribution metrics. explain select cim.yield from earnings JOIN contributions on contributions.id = earnings.note_id JOIN ( SELECT contribution_id, max(CASE metrics.name WHEN 'Yield'::text THEN

Bad plan chosen for union all

2017-11-28 Thread Alex Reece
I'm on PostgreSQL 9.6.5 and getting an awkwardly bad plan chosen for my query. I want to do: select investments.id, cim.yield FROM contributions JOIN investments ON contributions.investment_id = investments.id JOIN contribution_investment_metrics_view cim ON cim.investment_id = investments.id