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
In-reply-to: <20180918070636.tp243ftlwpzrfrsd@riseup.net>
References: <20180918070636.tp243ftlwpzrfrsd@riseup.net>
Comments: In-reply-to Nicolas Paris <nicolas.paris@riseup.net>
	message dated "Tue, 18 Sep 2018 09:06:36 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4690.1537309274.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 18 Sep 2018 18:21:14 -0400
Message-ID: <4691.1537309274@sss.pgh.pa.us>
Precedence: bulk

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 =3D table2.a g=
roup by a) joined ON TRUE

The way you've set that up, the constraint required to deduce uniqueness
(i.e. the table1.a =3D 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