| From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
|---|---|
| To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
| Cc: | "AJ Welch *EXTERN*" <awelch0100(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Use cases for lateral that do not involve a set returning function |
| Date: | 2014-12-10 19:29:54 |
| Message-ID: | CAHyXU0wSp23EyDjKB4_yKJpwodettR9R964BanFmx67NYMpuOw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Tue, Dec 9, 2014 at 4:24 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Adrian Klaver | 2014-12-10 19:34:18 | Re: invalid memory alloc request size |
| Previous Message | Jack Douglas | 2014-12-10 19:19:09 | Re: new index type with clustering in mind. |