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(at)gmail(dot)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.
>