Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXc1a-00BYxN-1J for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 07:07:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXc1X-008VQv-7A for pgsql-general@arkaria.postgresql.org; Wed, 07 Feb 2024 07:07:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXc1W-008VPI-0l for pgsql-general@lists.postgresql.org; Wed, 07 Feb 2024 07:07:10 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXc1O-005UjK-Lw for pgsql-general@postgresql.org; Wed, 07 Feb 2024 07:07:08 +0000 Received: by mail-pl1-x636.google.com with SMTP id d9443c01a7336-1d746ce7d13so3197285ad.0 for ; Tue, 06 Feb 2024 23:07:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=vanmulligen-ca.20230601.gappssmtp.com; s=20230601; t=1707289621; x=1707894421; darn=postgresql.org; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=2W9M2hJDY80/9R6R2ku1ch9mQYVhNPZzB59GCu+b8UQ=; b=vLNA1kuufrghKw5vhP1y6/A9O/9iSXWIxPlUAg3eKACLOQgmrfpTfYg+idrGbXK2lY nCxbBQDf9zIjZsBUKqmgzReU7Ng8iIFFVTVu7/2mecSIElDkq7jJ6Gu7wQ0sGoxHDhT7 PfV++9pD/dRgYjBaP4G+yuekWlMQg29wm3nD/fiWCBB65WN/Rb3Wh3vRxr4QNECXWoR+ Fh67tzZ4BwSJ6G7DaRL+wvONJKXkH9g5ARzGqM12ZI1A2e14rRq0Fxjnfnz/eBS6IzX4 I7IfM82CFZiJfQCIv2tUXXDASaD8VvTgNYrv+XsVcOjTwbrOKmdYer8Pn/LOoIXUkzBK /Yhw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707289621; x=1707894421; h=in-reply-to:from:content-language:references:cc:to:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=2W9M2hJDY80/9R6R2ku1ch9mQYVhNPZzB59GCu+b8UQ=; b=oFFR3pe00/eyR+JBhFfnBHN0CdC4ErarKSscmXbjg0NuPzG4lKJbaHZZWV7sq3LeZD 94QyqJzTj7/XkKqofBblxyqMBn8Mkm44dQA0y811K8XGrd64utH8WhTpjvoXmL2MUKS4 7l7FmIhHlTbp4YdNWH93AA+L/DkPp8HW2ESvVtqELXyiVFaOOtGP1gF81VSH9ij/jiu3 1+n+BrDuJ0zoO/2wkIrLw1oZ+/KfW8LofTlf0biUUG7TJsTSByPsfsqFTc/KjJ76Tq1v PAgUMPZWf4pV0J0IDsQrG0APBeQ3aR/83jWLqwVr0OGT9Cs4zJsv6xuTn8jDr0ec9mXu SaMQ== X-Gm-Message-State: AOJu0YyyMXjWKbcdjWT17t7D2z5zIdumKKkj87/Jc8an1wLZZFHr0VLN TiJCNfllNnESvXqfADCtXTr76hlJx3chuUqWam5xHF3mmX/9TzgvQQCSAnT9mkM= X-Google-Smtp-Source: AGHT+IEx/o/T5Rma8Vw1udCfL85pA0ILicib70Sm2+0iRTIQkL96Cj227ourf+Ttkza6wlnKDqlb5A== X-Received: by 2002:a17:903:428f:b0:1d9:7ab0:5e20 with SMTP id ju15-20020a170903428f00b001d97ab05e20mr3062300plb.69.1707289620759; Tue, 06 Feb 2024 23:07:00 -0800 (PST) X-Forwarded-Encrypted: i=1; AJvYcCU+faQpW77Zjy5asO128CqmzB6wz//fQ7BmNmmNjI0eTtKl4dnxFfSwgJ+dQ1ttOpRgP7VkOzy5FYpt3t/SmLb1Mckm6Gq/5w== Received: from [192.168.0.248] (S0106f492bf756549.vf.shawcable.net. [70.68.9.112]) by smtp.gmail.com with ESMTPSA id ku4-20020a170903288400b001d8d7323ea2sm683176plb.74.2024.02.06.23.06.59 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 06 Feb 2024 23:07:00 -0800 (PST) Content-Type: multipart/alternative; boundary="------------ItuqiM1FoptJoQtb0w2GABNN" Message-ID: Date: Tue, 6 Feb 2024 23:06:58 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Why doesn't Postgres apply limit on groups when retrieving N results per group using WHERE IN + ORDER BY To: "David G. Johnston" , Ron Johnson Cc: pgsql-general References: Content-Language: en-US From: Sean v In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ItuqiM1FoptJoQtb0w2GABNN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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. > --------------ItuqiM1FoptJoQtb0w2GABNN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

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 =3D company_users.user_id
   ORDER  BY created_a=
t DESC LIMIT <=
span
    class=3D"hljs-number"
style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; fon=
t-variant: inherit; font-weight: inherit; font-stretch: inherit; line-hei=
ght: inherit; font-family: inherit; font-optical-sizing: inherit; font-ke=
rning: inherit; font-feature-settings: inherit; font-variation-settings: =
inherit; font-size: 13px; vertical-align: baseline; box-sizing: inherit; =
--theme-base-primary-color-h: 196.31067961; --theme-base-primary-color-s:=
 100%; --theme-base-primary-color-l: 40.39215686%; --theme-primary-custom=
-100: hsl(var(--theme-base-primary-color-h), var(--theme-base-primary-col=
or-s), calc(var(--theme-base-primary-color-l) + ((100% - var(--theme-base=
-primary-color-l)) * .9))); --theme-primary-custom-200: hsl(var(--theme-b=
ase-primary-color-h), var(--theme-base-primary-color-s), calc(var(--theme=
-base-primary-color-l) + ((100% - var(--theme-base-primary-color-l)) * .7=
5))); --theme-primary-custom-300: hsl(var(--theme-base-primary-color-h), =
var(--theme-base-primary-color-s), calc(var(--theme-base-primary-color-l)=
 + ((100% - var(--theme-base-primary-color-l)) * .5))); --theme-primary-c=
ustom-400: hsl(var(--theme-base-primary-color-h), var(--theme-base-primar=
y-color-s), var(--theme-base-primary-color-l)); --theme-primary-custom-50=
0: hsl(var(--theme-base-primary-color-h), var(--theme-base-primary-color-=
s), calc(var(--theme-base-primary-color-l) + (var(--theme-base-primary-co=
lor-l) * -.3))); --theme-primary-custom-600: hsl(var(--theme-base-primary=
-color-h), var(--theme-base-primary-color-s), calc(var(--theme-base-prima=
ry-color-l) + (var(--theme-base-primary-color-l) * -.6))); --theme-primar=
y-custom: var(--theme-primary-custom-400); --theme-button-color: var(--th=
eme-primary); --theme-button-hover-color: var(--theme-primary-400); --the=
me-button-hover-background-color: var(--theme-primary-100); --theme-butto=
n-active-background-color: var(--theme-primary-200); --theme-button-selec=
ted-color: var(--theme-primary-600); --theme-button-selected-background-c=
olor: var(--theme-primary-300); --theme-button-outlined-border-color: var=
(--theme-primary-400); --theme-button-outlined-selected-border-color: var=
(--theme-primary-500); --theme-button-primary-color: var(--white); --them=
e-button-primary-active-color: var(--white); --theme-button-primary-hover=
-color: var(--white); --theme-button-primary-selected-color: var(--white)=
; --theme-button-primary-background-color: var(--theme-primary-400); --th=
eme-button-primary-active-background-color: var(--theme-primary-600); --t=
heme-button-primary-hover-background-color: var(--theme-primary-500); --t=
heme-button-primary-selected-background-color: var(--theme-primary-600); =
--theme-background-color: #FAFAFA; --theme-background-position: top left;=
 --theme-background-repeat: repeat; --theme-background-size: auto; --them=
e-background-attachment: auto; --theme-content-background-color: var(--wh=
ite); --theme-content-border-color: var(--black-225); --theme-header-back=
ground-color: transparent; --theme-header-background-position: center lef=
t; --theme-header-background-repeat: repeat; --theme-header-background-si=
ze: auto; --theme-header-background-border-bottom: 0; --theme-header-link=
-color: var(--theme-primary); --theme-header-sponsored-color: hsl(210,8%,=
25%); --theme-header-foreground-color: transparent; --theme-header-foregr=
ound-position: right center; --theme-header-foreground-repeat: no-repeat;=
 --theme-header-foreground-size: auto; --theme-footer-background-color: #=
6C6C6C; --theme-footer-background-position: top left; --theme-footer-back=
ground-repeat: no-repeat; --theme-footer-background-size: auto; --theme-f=
ooter-background-border-top: 0; --theme-footer-title-color: var(--white);=
 --theme-footer-text-color: var(--black-200); --theme-footer-link-color: =
var(--black-150); --theme-footer-link-color-hover: var(--white); --theme-=
footer-divider-color: var(--black-350); --theme-footer-padding-top: 0; --=
theme-footer-padding-bottom: 0; --theme-link-color: #0096CE; --theme-link=
-color-hover: #0E4C99; --theme-link-color-visited: rgba(0,150,206,0.8); -=
-theme-tag-color: var(--theme-secondary-500); --theme-tag-background-colo=
r: var(--theme-secondary-100); --theme-tag-border-color: transparent; --t=
heme-tag-hover-color: var(--theme-secondary-600); --theme-tag-hover-backg=
round-color: var(--theme-secondary-200); --theme-tag-hover-border-color: =
transparent; --theme-tag-required-color: var(--white); --theme-tag-requir=
ed-background-color: var(--theme-secondary-500); --theme-tag-required-bor=
der-color: transparent; --theme-tag-required-hover-color: var(--white); -=
-theme-tag-required-hover-background-color: var(--theme-secondary-400); -=
-theme-tag-required-hover-border-color: transparent; --theme-body-font-fa=
mily: var(--ff-sans); --theme-body-font-color: var(--black-600); --theme-=
post-title-font-family: var(--ff-sans); --theme-post-title-color: #0096CE=
; --theme-post-title-color-hover: #00A8E6; --theme-post-title-color-visit=
ed: rgba(0,150,206,0.75); --theme-post-body-font-family: var(--ff-sans); =
--theme-post-owner-background-color: var(--theme-primary-100); --theme-po=
st-owner-new-background-color: var(--theme-primary-200); --theme-topbar-b=
ottom-border: none; color: var(--highlight-namespace);">50
   ) cu
WHERE  cu.co=
mpany_id =3D ?=20
ORDER BY created_a=
t DESC LIMIT <=
span
    class=3D"hljs-number"
style=3D"margin: 0px; padding: 0px; border: 0px; font-style: inherit; fon=
t-variant: inherit; font-weight: inherit; font-stretch: inherit; line-hei=
ght: inherit; font-family: inherit; font-optical-sizing: inherit; font-ke=
rning: inherit; font-feature-settings: inherit; font-variation-settings: =
inherit; font-size: 13px; vertical-align: baseline; box-sizing: inherit; =
--theme-base-primary-color-h: 196.31067961; --theme-base-primary-color-s:=
 100%; --theme-base-primary-color-l: 40.39215686%; --theme-primary-custom=
-100: hsl(var(--theme-base-primary-color-h), var(--theme-base-primary-col=
or-s), calc(var(--theme-base-primary-color-l) + ((100% - var(--theme-base=
-primary-color-l)) * .9))); --theme-primary-custom-200: hsl(var(--theme-b=
ase-primary-color-h), var(--theme-base-primary-color-s), calc(var(--theme=
-base-primary-color-l) + ((100% - var(--theme-base-primary-color-l)) * .7=
5))); --theme-primary-custom-300: hsl(var(--theme-base-primary-color-h), =
var(--theme-base-primary-color-s), calc(var(--theme-base-primary-color-l)=
 + ((100% - var(--theme-base-primary-color-l)) * .5))); --theme-primary-c=
ustom-400: hsl(var(--theme-base-primary-color-h), var(--theme-base-primar=
y-color-s), var(--theme-base-primary-color-l)); --theme-primary-custom-50=
0: hsl(var(--theme-base-primary-color-h), var(--theme-base-primary-color-=
s), calc(var(--theme-base-primary-color-l) + (var(--theme-base-primary-co=
lor-l) * -.3))); --theme-primary-custom-600: hsl(var(--theme-base-primary=
-color-h), var(--theme-base-primary-color-s), calc(var(--theme-base-prima=
ry-color-l) + (var(--theme-base-primary-color-l) * -.6))); --theme-primar=
y-custom: var(--theme-primary-custom-400); --theme-button-color: var(--th=
eme-primary); --theme-button-hover-color: var(--theme-primary-400); --the=
me-button-hover-background-color: var(--theme-primary-100); --theme-butto=
n-active-background-color: var(--theme-primary-200); --theme-button-selec=
ted-color: var(--theme-primary-600); --theme-button-selected-background-c=
olor: var(--theme-primary-300); --theme-button-outlined-border-color: var=
(--theme-primary-400); --theme-button-outlined-selected-border-color: var=
(--theme-primary-500); --theme-button-primary-color: var(--white); --them=
e-button-primary-active-color: var(--white); --theme-button-primary-hover=
-color: var(--white); --theme-button-primary-selected-color: var(--white)=
; --theme-button-primary-background-color: var(--theme-primary-400); --th=
eme-button-primary-active-background-color: var(--theme-primary-600); --t=
heme-button-primary-hover-background-color: var(--theme-primary-500); --t=
heme-button-primary-selected-background-color: var(--theme-primary-600); =
--theme-background-color: #FAFAFA; --theme-background-position: top left;=
 --theme-background-repeat: repeat; --theme-background-size: auto; --them=
e-background-attachment: auto; --theme-content-background-color: var(--wh=
ite); --theme-content-border-color: var(--black-225); --theme-header-back=
ground-color: transparent; --theme-header-background-position: center lef=
t; --theme-header-background-repeat: repeat; --theme-header-background-si=
ze: auto; --theme-header-background-border-bottom: 0; --theme-header-link=
-color: var(--theme-primary); --theme-header-sponsored-color: hsl(210,8%,=
25%); --theme-header-foreground-color: transparent; --theme-header-foregr=
ound-position: right center; --theme-header-foreground-repeat: no-repeat;=
 --theme-header-foreground-size: auto; --theme-footer-background-color: #=
6C6C6C; --theme-footer-background-position: top left; --theme-footer-back=
ground-repeat: no-repeat; --theme-footer-background-size: auto; --theme-f=
ooter-background-border-top: 0; --theme-footer-title-color: var(--white);=
 --theme-footer-text-color: var(--black-200); --theme-footer-link-color: =
var(--black-150); --theme-footer-link-color-hover: var(--white); --theme-=
footer-divider-color: var(--black-350); --theme-footer-padding-top: 0; --=
theme-footer-padding-bottom: 0; --theme-link-color: #0096CE; --theme-link=
-color-hover: #0E4C99; --theme-link-color-visited: rgba(0,150,206,0.8); -=
-theme-tag-color: var(--theme-secondary-500); --theme-tag-background-colo=
r: var(--theme-secondary-100); --theme-tag-border-color: transparent; --t=
heme-tag-hover-color: var(--theme-secondary-600); --theme-tag-hover-backg=
round-color: var(--theme-secondary-200); --theme-tag-hover-border-color: =
transparent; --theme-tag-required-color: var(--white); --theme-tag-requir=
ed-background-color: var(--theme-secondary-500); --theme-tag-required-bor=
der-color: transparent; --theme-tag-required-hover-color: var(--white); -=
-theme-tag-required-hover-background-color: var(--theme-secondary-400); -=
-theme-tag-required-hover-border-color: transparent; --theme-body-font-fa=
mily: var(--ff-sans); --theme-body-font-color: var(--black-600); --theme-=
post-title-font-family: var(--ff-sans); --theme-post-title-color: #0096CE=
; --theme-post-title-color-hover: #00A8E6; --theme-post-title-color-visit=
ed: rgba(0,150,206,0.75); --theme-post-body-font-family: var(--ff-sans); =
--theme-post-owner-background-color: var(--theme-primary-100); --theme-po=
st-owner-new-background-color: var(--theme-primary-200); --theme-topbar-b=
ottom-border: none; color: var(--highlight-namespace);">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 "orde=
rs".*=20
FROM "orders=
"=20
WHERE user_i=
d IN (SELECT user_=
id FROM company=
_users WHERE compan=
y_id =3D ?)
ORDER BY "orders".=
"created_at" LIMIT 50


On 2024-02-05 7:58 a.m., David G. Johnston wrote:
On Mon, Fe= b 5, 2024 at 8:55=E2=80=AFAM Ron Johnson <= ronljohnsonjr@gmail.com> wrote:

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


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

David J.

--------------ItuqiM1FoptJoQtb0w2GABNN--