pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
LEFT JOIN LATERAL optimisation at plan time 2+ messages / 2 participants [nested] [flat]
* LEFT JOIN LATERAL optimisation at plan time @ 2018-09-18 07:06 Nicolas Paris <nicolas.paris@riseup.net> 2018-09-18 22:21 ` Re: LEFT JOIN LATERAL optimisation at plan time Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Nicolas Paris @ 2018-09-18 07:06 UTC (permalink / raw) To: pgsql-performance@lists.postgresql.org Hi, For a traditional LEFT JOIN, in case the SELECT does not mention a field from a joined table being unique , the planner removes the join. Eg: SELECT a, b --,c FROM table1 LEFT JOIN (select a, c from table2 group by a) joined USING (a) However this behavior is not the same for LATERAL JOINS SELECT a, b --,c FROM table1 LEFT JOIN LATERAL (select a, c from table2 where table1.a = table2.a group by a) joined ON TRUE In this case, the planner still consider the joined table. My guess is it could remove it . Any thought ? -- nicolas ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: LEFT JOIN LATERAL optimisation at plan time 2018-09-18 07:06 LEFT JOIN LATERAL optimisation at plan time Nicolas Paris <nicolas.paris@riseup.net> @ 2018-09-18 22:21 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2018-09-18 22:21 UTC (permalink / raw) To: Nicolas Paris <nicolas.paris@riseup.net>; +Cc: pgsql-performance@lists.postgresql.org Nicolas Paris <nicolas.paris@riseup.net> writes: > For a traditional LEFT JOIN, in case the SELECT does not mention a field > from a joined table being unique , the planner removes the join. Eg: > SELECT a, b --,c > FROM table1 > LEFT JOIN (select a, c from table2 group by a) joined USING (a) > However this behavior is not the same for LATERAL JOINS > SELECT a, b --,c > FROM table1 > LEFT JOIN LATERAL (select a, c from table2 where table1.a = table2.a group by a) joined ON TRUE The way you've set that up, the constraint required to deduce uniqueness (i.e. the table1.a = table2.a clause) is hidden inside a non-trivial subquery; and, where it's placed, it isn't actually guaranteeing anything so far as the inner query is concerned, ie the select from table2 could easily return multiple rows. I'm not too surprised that the outer planner level doesn't make this deduction. > In this case, the planner still consider the joined table. My guess is > it could remove it . It looks to me like it would require a substantial amount of additional code and plan-time effort to find cases like this. I'm not convinced that the cost-benefit ratio is attractive. Maybe in some hypothetical future where we're able to flatten sub-selects even though they contain GROUP BY, it would get easier/cheaper to detect this case. But that's just pie in the sky at the moment. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-09-18 22:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-09-18 07:06 LEFT JOIN LATERAL optimisation at plan time Nicolas Paris <nicolas.paris@riseup.net> 2018-09-18 22:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox