pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean v <sean@vanmulligen.ca>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY
Date: Tue, 6 Feb 2024 23:06:58 -0800
Message-ID: <f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca> (raw)
In-Reply-To: <CAKFQuwZaoBwC9_fXsaiiU3i3hVSx+mZqabvC8mGUpaCNwk9a7g@mail.gmail.com>
References: <CAHr3uZFZfCFyyeksT+jwM-zXZ1JKs2mfGU4RpWKm4yf8yiGM0g@mail.gmail.com>
	<CANzqJaDC2MAVD9wsuCSNOPC+aFXWoWy8Ya0rnGNVJ454A-BYjA@mail.gmail.com>
	<CAKFQuwZaoBwC9_fXsaiiU3i3hVSx+mZqabvC8mGUpaCNwk9a7g@mail.gmail.com>

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.
>

view thread (6+ messages)  latest in thread

Message-ID: <f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca>
Permalink:  ../f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca/
Also on:    postgresql.org/message-id/f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: sean@vanmulligen.ca, david.g.johnston@gmail.com, ronljohnsonjr@gmail.com
  Subject: Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY
  In-Reply-To: <f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca>
  References: <CAHr3uZFZfCFyyeksT+jwM-zXZ1JKs2mfGU4RpWKm4yf8yiGM0g@mail.gmail.com> <CANzqJaDC2MAVD9wsuCSNOPC+aFXWoWy8Ya0rnGNVJ454A-BYjA@mail.gmail.com> <CAKFQuwZaoBwC9_fXsaiiU3i3hVSx+mZqabvC8mGUpaCNwk9a7g@mail.gmail.com> <f02089e5-9aca-4deb-9740-2c26faa138cd@vanmulligen.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox