On 16 February 2019 06:02:50 GMT+01:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
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.
