From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nicolas Paris <nicolas.paris@riseup.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: LEFT JOIN LATERAL optimisation at plan time
Date: Tue, 18 Sep 2018 18:21:14 -0400
Message-ID: <4691.1537309274@sss.pgh.pa.us> (raw)
In-Reply-To: <20180918070636.tp243ftlwpzrfrsd@riseup.net>
References: <20180918070636.tp243ftlwpzrfrsd@riseup.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, nicolas.paris@riseup.net, pgsql-performance@lists.postgresql.org
Subject: Re: LEFT JOIN LATERAL optimisation at plan time
In-Reply-To: <4691.1537309274@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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