News for hackers. Stuff that matters.
Re:Lesser-known Postgres features by bbirk on Tuesday November 09, 2021 @05:12PM (#29168212)
I would use lateral join. Alternatively you can use window function instead of lateral join, buy from my experience lateral joins are usually faster for this kind of top-N queries in postgres.

select user_table.user_id, tmp.post_id, tmp.text, tmp.timestamp

from user_table

left outer join lateral (

select post_id, text, timestamp

from post

where post.user_id = user_table.user_id

order by timestamp

limit 1

) tmp on true

order by user_id

limit 30;

There are no comments on this comment yet.