Re: Problems pushing down WHERE-clause to underlying view

Nicklas Avén <nicklas.aven@jordogskog.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.aven@jordogskog.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.aven@jordogskog.no', but you can probably adapt the idea
to make it work.

			regards, tom lane