Re: Problems pushing down WHERE-clause to underlying view

On 16 February 2019 06:02:50 GMT+01:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>=?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

Thanks Tom
This is what I suspected was happening.
What I was hoping though was that the planner could see that the contractor_access table only contains a few hundred rows, and that the logs table with millions of rows with an index on machine_key should be reduced as much as possible before start grouping.
At first I thought this didn't happen because the logs table is hidden in a subquery. But since it works when filtering directly on machine_key I guess that is not the problem.

But I am still a little confused why I cannot trick this with lateral as I showed in the first mail.

I guess I will have to rewrite this into a function and only give one machine_key at a time to this query.

I think I have bumped into this before, and I might even have asked the same question som years aho, I think I recognize you answer. Sorry for not learning.

Thanks

Nicklas

Sent from my Android device with K-9 Mail. Please excuse my brevity.