On Mon, Feb 5, 2024 at 7:23 AM Sean v <sean(at)vanmulligen(dot)ca> wrote:
> This is related to a question I asked on dbs.stackexchange.com:
> https://dba.stackexchange.com/questions/335501/why-doesnt-postgres-apply-limit-on-groups-when-retrieving-n-results-per-group
>
> But to reiterate - I have a query like this:
>
> SELECT "orders".*
>
> FROM "orders"
>
> WHERE (user_id IN ?, ?, ?)
>
> ORDER BY "orders"."created_at" LIMIT 50
>
[snip]
> So my question is twofold:
> - why doesn't Postgres use the composite index, and then retrieve only the
> minimum necessary amount of rows (50 per user) using the query I posted
> above?
>
>
But your query *does not* list the first 50 rows *per user*. It only
returns the first 50 rows of:
SELECT "orders".*
FROM "orders"
WHERE (user_id IN ?, ?, ?)
ORDER BY "orders"."created_at"
Who knows which users are going to be in that list???