Re: Use cases for lateral that do not involve a set returning function - Mailing list pgsql-general

On Tue, Dec 9, 2014 at 4:24 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> SELECT ...
> FROM people p
>      LEFT JOIN LATERAL (SELECT * FROM names n
>                         WHERE n.people_id = p.people_id
>                         AND current_timestamp > n.validfrom
>                         ORDER BY n.validfrom DESC LIMIT 1) n
>         ON TRUE
> WHERE p.id = ...
>
> With the correct index this touched fewer blocks and worked faster.
> Also, though this is of course a matter of taste, it is more readable.
>
> Of course this forces a nested loop, but that is not bad as such.
> In my case it was not problem (I tried to hint at that with the WHERE clause).

I don't know...forcing a nestloop is a dealbreaker for many
distributions of data.

merlin