Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY

Exactly. I'm really just trying to understand if there's some functional limitation to it being able to do that with how it executes these types of queries, or if its just an optimization that hasn't been built into the query planner yet.

I know I can get it to do precisely this if I use a CROSS JOIN LATERAL:

SELECT o.*
FROM company_users cu
CROSS JOIN LATERAL (   SELECT *   FROM orders o   WHERE o.user_id = company_users.user_id   ORDER  BY created_at DESC LIMIT 50   ) cu
WHERE  cu.company_id = ? 
ORDER BY created_at DESC LIMIT 50

That makes sense to me, it forces a nested loop and executes for each user. But doing a nested select like the query below doesn't use the index or limit the results to 50 per user - even though it does a nested loop just like the lateral join does:

SELECT "orders".* 
FROM "orders" 
WHERE user_id IN (SELECT user_id FROM company_users WHERE company_id = ?)
ORDER BY "orders"."created_at" LIMIT 50

On 2024-02-05 7:58 a.m., David G. Johnston wrote:

On Mon, Feb 5, 2024 at 8:55 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Who knows which users are going to be in that list???

It doesn't matter.  Worse case scenario there is only one user in the result and so all 50 rows are their earliest 50 rows.  The system will thus never need more than the earliest 50 rows per user to answer this question.

David J.