Re: Problems pushing down WHERE-clause to underlying view

=?UTF-8?Q?Nicklas_Av=c3=a9n?= <nicklas(dot)aven(at)jordogskog(dot)no> 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(dot)aven(at)jordogskog(dot)no');

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(dot)aven(at)jordogskog(dot)no', but you can probably adapt the idea
to make it work.

regards, tom lane