Postgres Pro
Downloads
Home   >   mailing lists

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

From Ron Johnson
Subject Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY
Date
Msg-id CANzqJaDC2MAVD9wsuCSNOPC+aFXWoWy8Ya0rnGNVJ454A-BYjA@mail.gmail.com
Whole thread Raw
In response to Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY  (Sean v <sean@vanmulligen.ca>)
Responses Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
On Mon, Feb 5, 2024 at 7:23 AM Sean v <sean@vanmulligen.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???

pgsql-general by date:

Previous
From: arun chirappurath
Date:
Subject: Unused indexes
Next
From: "David G. Johnston"
Date:
Subject: Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY