=?UTF-8?Q?Nicklas_Av=c3=a9n?= <nicklas.aven@xxxxxxxxxxxxx> writes:
> I also, in the first query, changed the where clause to filter on
> machine_key in table contractor _access. Just to illustrate the problem
> better.
> Both queries filter on the same table which is joined the same way. But
> in the second example the where clause is not pushed to the subquery
The filters are totally different though. In one case you provide
where ci.machine_key = '887655635442600'
and there is also a join condition
l.machine_key=ci.machine_key
>From these two things the planner can deduce
l.machine_key='887655635442600'
which is a restriction condition that it knows how to push down into the
"l" subquery. Furthermore, it can also deduce that it can restrict
all of the left-joined tables to consider only that value of their
join keys.
In query #2 you have no constant value for machine_key so none of that
happens.
IIRC, the propagated value doesn't have to be a constant, exactly,
just a fixed expression. So you might consider something like
<query 1 as written, up to the WHERE>
where ci.machine_key = (select machine_key from contractor_access
where t4e_contractor_id = 'nicklas.aven@xxxxxxxxxxxxx');
when you need to drive the lookup from something other than raw
machine_key. This'll fail, as-is, if there's more than one
contractor_access row with t4e_contractor_id =
'nicklas.aven@xxxxxxxxxxxxx', but you can probably adapt the idea
to make it work.
regards, tom lane
