Alex Reece <awre...@gmail.com> 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 would require generating "parameterized paths" for subqueries. While I do not think there's any fundamental technical reason anymore that we couldn't do so, there's considerable risk of wasting a lot of planner cycles chasing unprofitable plan alternatives. Anyway it was totally impractical before 9.6's upper-planner-pathification changes, and not all of the dust has settled from that rewrite. > But I expected it to be equivalent to the plan from this query: 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. regards, tom lane