Date: Tue, 18 Sep 2018 09:06:36 +0200
From: Nicolas Paris <nicolas.paris@riseup.net>
To: pgsql-performance@lists.postgresql.org
Subject: LEFT JOIN LATERAL optimisation at plan time
Message-ID: <20180918070636.tp243ftlwpzrfrsd@riseup.net>
Mail-Followup-To: pgsql-performance@lists.postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Priority: normal
Precedence: bulk

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