On 16 February 2019 06:02:50 GMT+01:00, Tom Lane <t...@sss.pgh.pa.us> wrote: >=?UTF-8?Q?Nicklas_Av=c3=a9n?= <nicklas.a...@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.a...@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.a...@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.
