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 1vEGBZ-008Wqk-3H for pgsql-general@arkaria.postgresql.org; Thu, 30 Oct 2025 00:06:36 +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 1vEGBW-004bkP-Rf for pgsql-general@arkaria.postgresql.org; Thu, 30 Oct 2025 00:06:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEGBW-004bkH-Fe for pgsql-general@lists.postgresql.org; Thu, 30 Oct 2025 00:06:33 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEGBP-004yFw-2T for pgsql-general@lists.postgresql.org; Thu, 30 Oct 2025 00:06:33 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-63e076e24f2so780612a12.3 for ; Wed, 29 Oct 2025 17:06:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761782787; x=1762387587; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=MIRHVlJy+URDFwkWxbvYlwKAho6JYExycv6i6kakQYk=; b=VKu7K4U+rlZdPvp+MZ4zN8EfX6GmX5mjp5vAJDS8EejMxfaE5/PXv6Vbz2tyCZ9mUl 5X17hXe34GRtexWLqxYug/tHgMVqyKG6HqYRVsy4S4xN9D+JkLX9xTujem7iR1JwhJXl /sBRg3ijM/7JKgQVJ4jGuXBygEm9zZpluyFfrLCas8aBPkwIWaiJKQ+cEXh4S7evL11Q aolf/f1BP0ZRZIYBxlXmu1p7wfMx/1W2D5XloZBJlVKSmhxki8ER3x5blqkqRBcYudhu RzthV83tulDWlYYKr1a0hIJVza28M1Azr8y9CLNJr1odmAq7f8f4aUCqaw4CemKCkplJ SHwg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761782787; x=1762387587; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=MIRHVlJy+URDFwkWxbvYlwKAho6JYExycv6i6kakQYk=; b=b/YyYZyFZjo7N16UlzC1AE5HmmQqLIDLHzhOsMaeSIng5Ly6BuBIbeA0HqHDbDTIBs 6B/6ZIubSY/HSqA5TlOg+T7pR2cWcvQriMVHGwJB/vummRwLIeiIiU3KF/1R7rIU9OIJ 8HDV62YRpwqwW67Nh5IWPZnffiOz6sXPqpkByTxNDZwZvV5o39p+I7hY5/6ifDCBqC7j huj7eiGTyLoa8Nl1OShCqKcpsdJ/YqVKtH16GIvSlBNa0yW2ZgPsDXQiIqNotLAv+J2B EXPdd9Zc5WDAS4GRe5D9QC4V/XLM6lRDKiJ7UVnZm6k8hgnN+cmrQQngoHOcZiZNoPxe DaWg== X-Gm-Message-State: AOJu0Yz9hZ3hQRufwGr9hxs3Mp8CTygJRvxciyQAlwtiWdEiYBZ7xenN PYsLjjfqVbrOBvCg6DbuvVrXlma/0Gjh5Fr/xrclk0nGCHp6YtCXPqThG+03/9btQA56hWl1u1z y59HkcXg2tc+cWEL2tvxb+WSSzWcn3xxDaA== X-Gm-Gg: ASbGncs1XPE2DuvQaZB+sMR2W0N8NaYlS7iKfSLG0MUFEAHZ9kcy1GY4/xjVPyjl8ws uKeF0GZAFETBnc1gME1iasW/qlp62B66VAtgdmSfMa3Bhqs3xCCOPFJjWlfRwEmNBYIwJtKU69I DQo1r2Hp6nYF9WdFmOPOLRKYp3SAFGH2KY4Bg0omsCtkx1gxQ31782Q0GkUWgcy578GxMbY2t/z gEcBerUJmPyiOyQVFIWPsQYLPLFYpNNaLYhFaQqXMz8DY9jvlqw2nnOCOQ= X-Google-Smtp-Source: AGHT+IGGsMZsaGrqkXwbzEMFaZXzAkzBLOaJfXp/F+m6g/IaQekGyVOC4Q98K/BmDxXvq8L56InET/DE3TsvLAnIXDU= X-Received: by 2002:a05:6402:5c8:b0:63c:1066:8bec with SMTP id 4fb4d7f45d1cf-64061a519f7mr812853a12.35.1761782786671; Wed, 29 Oct 2025 17:06:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jacob Jackson Date: Wed, 29 Oct 2025 20:06:15 -0400 X-Gm-Features: AWmQ_bldvEIRDTboVMkYJwrc-sAzJdvcGic0dGx0hlMS6B-9vh01THXbuXdtCdw Message-ID: Subject: Re: Postgres memoizing inner loop of join when outer loop join key is guaranteed unique? To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002bc66606425505d5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002bc66606425505d5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The performance/cost difference persists with identical keys. Why would the planner for the memorized query use different statistics/calculations in the first place? On Wed, Oct 29, 2025 at 5:48=E2=80=AFPM Ron Johnson wrote: > You're running slightly different queries: > enable_memoize=3Don: Index Cond: ("user" =3D '0'::bigint) > enable_memoize=3Doff: Index Cond: ("user" =3D '3477145805513'::bigint) > > All buffer counts look to be the same on both, and 514 is just 1.5% > smaller than 522. That looks like statistical noise to me. > > > On Wed, Oct 29, 2025 at 5:34=E2=80=AFPM Jacob Jackson > wrote: > >> I was curious to see whether there was any reason I wasn't seeing for >> Postgres to decide the memoized version was lower cost and try to memoiz= e >> these operations. >> >> On Wed, Oct 29, 2025 at 3:20=E2=80=AFPM Ron Johnson >> wrote: >> >>> What's the actual problem? Does enable_memoize=3Don return incorrect >>> results? >>> >>> Because a 45 microsecond (yes, 45 microseconds: 0.138 milliseconds =3D = 138 >>> microseconds; same for the others) slowdown isn't something I'd get too >>> worked up about. >>> >>> On Wed, Oct 29, 2025 at 2:29=E2=80=AFPM Jacob Jackson >>> wrote: >>> >>>> Hello. I was looking at some query plans recently and noticed somethin= g >>>> that didn't make sense. I have a query that joins a table of questions= with >>>> results for each question (using a table with a composite primary key = of >>>> question id and a user id), filtered by user id. The question IDs and = the >>>> combined question-userIds are guaranteed unique due to being primary k= eys, >>>> and yet Postgres still memoizes the inner loop results. Any ideas why?= Is >>>> this just a failure of the query planner (I would be happy to explore >>>> creating a PR), did I not properly guarantee uniqueness, or is there >>>> another reason for memoization? The memoized version is consistently >>>> slightly slower in my testing, despite the calculated cost being lower= . >>>> Here are the query plans: >>>> >>>> enable_memoize=3Don: >>>> >>>> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM questions LEFT JOIN >>>> "QuestionUserStatus" ON questions.id =3D "QuestionUserStatus".question >>>> WHERE "QuestionUserStatus".user =3D 0; >>>> >>>> Nested Loop (cost=3D0.71..514.09 rows=3D277 width=3D1381) (actual >>>> time=3D0.021..0.520 rows=3D231 loops=3D1) >>>> =E2=94=9C Buffers: shared hit=3D859 >>>> =E2=94=9C Index Scan using "QuestionUserStatus_user_question_pk" on >>>> "QuestionUserStatus" (cost=3D0.42..178.88 rows=3D277 width=3D18) (act= ual >>>> time=3D0.014..0.114 rows=3D231 loops=3D1) >>>> =E2=94=82 =E2=94=9C Index Cond: ("user" =3D '0'::bigint) >>>> =E2=94=82 =E2=94=94 Buffers: shared hit=3D166 >>>> =E2=94=94 Memoize (cost=3D0.29..1.25 rows=3D1 width=3D1363) (actual >>>> time=3D0.001..0.001 rows=3D1 loops=3D231) >>>> =E2=94=9C Cache Key: "QuestionUserStatus".question >>>> =E2=94=9C Cache Mode: logical >>>> =E2=94=9C Hits: 0 Misses: 231 Evictions: 0 Overflows: 0 Memory U= sage: >>>> 320kB >>>> =E2=94=9C Buffers: shared hit=3D693 >>>> =E2=94=94 Index Scan using questions_pkey on questions (cost=3D0.28= ..1.24 >>>> rows=3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D23= 1) >>>> =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".question) >>>> =E2=94=94 Buffers: shared hit=3D693 >>>> Planning: >>>> =E2=94=94 Buffers: shared hit=3D6 >>>> Planning Time: 0.183 ms >>>> Execution Time: 0.548 ms >>>> >>>> enable_memoize=3Doff: >>>> >>>> Nested Loop (cost=3D0.70..521.98 rows=3D277 width=3D1381) (actual >>>> time=3D0.018..0.421 rows=3D231 loops=3D1) >>>> =E2=94=9C Buffers: shared hit=3D859 >>>> =E2=94=9C Index Scan using "QuestionUserStatus_user_question_pk" on >>>> "QuestionUserStatus" (cost=3D0.42..178.88 rows=3D277 width=3D18) (act= ual >>>> time=3D0.014..0.099 rows=3D231 loops=3D1) >>>> =E2=94=82 =E2=94=9C Index Cond: ("user" =3D '3477145805513'::bigint) >>>> =E2=94=82 =E2=94=94 Buffers: shared hit=3D166 >>>> =E2=94=94 Index Scan using questions_pkey on questions (cost=3D0.28..= 1.24 rows=3D1 >>>> width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231) >>>> =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".question) >>>> =E2=94=94 Buffers: shared hit=3D693 >>>> Planning: >>>> =E2=94=94 Buffers: shared hit=3D6 >>>> Planning Time: 0.197 ms >>>> Execution Time: 0.444 ms >>>> >>>> Thanks for the help, >>>> Jacob >>>> >>> > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000002bc66606425505d5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The performance/cost difference persists with identical ke= ys. Why would the planner for the memorized query use different statistics/= calculations in the first place?

On Wed, Oct 29, 2025 = at 5:48=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
You're running slightly= different queries:
enable_= memoize=3Don:=C2=A0Index Cond:= ("user" =3D '0'::bigint)
enable_memoize=3Doff:=C2=A0= Index Cond: ("user" =3D = 9;3477145805513'::bigint)

All buffer=C2= =A0counts look to be the same on=C2=A0both, and 514 is just 1.5% smaller th= an 522.=C2=A0 That looks like statistical noise to me.

=

= On Wed, Oct 29, 2025 at 5:34=E2=80=AFPM Jacob Jackson <jej.jackson.08@gmail.com&g= t; wrote:
I was curious to see whether there was any reason I wasn't s= eeing for Postgres to decide the memoized version was lower cost and try to= memoize these operations.

On Wed, Oct 29, 2025 at 3:20=E2=80=AFPM Ron Johns= on <ronljoh= nsonjr@gmail.com> wrote:
What's the actual problem?=C2=A0 = Does=C2=A0enable_memoize=3Don= =C2=A0return incorrect results?

Because a 45 micro= second (yes, 45 microseconds: 0.138 milliseconds =3D 138 microseconds; same= for the others) slowdown isn't something I'd get too worked up abo= ut.

On Wed, Oct 29, 2025 at 2:29=E2=80= =AFPM Jacob Jackson <jej.jackson.08@gmail.com> wrote:
Hello. I was looking at some query=C2=A0plans recently and noticed somethi= ng that didn't make sense. I have a query that joins a table of questio= ns with results for each question (using a table with a composite primary k= ey of question id and a user id), filtered by user id. The question IDs and= the combined question-userIds are guaranteed unique due to being primary k= eys, and yet Postgres still memoizes the inner loop results. Any ideas why?= Is this just a failure of the query planner (I would be happy to explore c= reating a PR), did I not properly=C2=A0guarantee uniqueness, or is there an= other reason for memoization? The memoized=C2=A0version is consistently sli= ghtly slower in my testing, despite the calculated=C2=A0cost being lower. H= ere are the query plans:

enable= _memoize=3Don:

<= div>EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM quest= ions LEFT JOIN "QuestionUserStatus" ON questions.id =3D "QuestionUserStatus"= .question WHERE "QuestionUserStatus".user =3D 0;

Ne= sted Loop =C2=A0(cost=3D0.71..514.09 rows=3D277 width=3D1381) (actual time= =3D0.021..0.520 rows=3D231 loops=3D1)
=E2=94=9C Buffers: shared hit=3D85= 9
=E2=94=9C Index Scan using "QuestionUserStatus_user_question_pk&q= uot; on "QuestionUserStatus" =C2=A0(cost=3D0.42..178.88 rows=3D27= 7 width=3D18) (actual time=3D0.014..0.114 rows=3D231 loops=3D1)
=E2=94= =82 =E2=94=9C Index Cond: ("user" =3D '0'::bigint)
=E2= =94=82 =E2=94=94 Buffers: shared hit=3D166
=E2=94=94 Memoize =C2=A0(cost= =3D0.29..1.25 rows=3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 l= oops=3D231)
=C2=A0 =E2=94=9C Cache Key: "QuestionUserStatus".q= uestion
=C2=A0 =E2=94=9C Cache Mode: logical
=C2=A0 =E2=94=9C Hits: 0= =C2=A0Misses: 231 =C2=A0Evictions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage= : 320kB
=C2=A0 =E2=94=9C Buffers: shared hit=3D693
=C2=A0 =E2=94=94 I= ndex Scan using questions_pkey on questions =C2=A0(cost=3D0.28..1.24 rows= =3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)
=C2= =A0 =C2=A0 =E2=94=9C Index Cond: (id =3D "QuestionUserStatus".que= stion)
=C2=A0 =C2=A0 =E2=94=94 Buffers: shared hit=3D693
Planning:=E2=94=94 Buffers: shared hit=3D6
Planning Time: 0.183 ms
Execution = Time: 0.548 ms

<= div>enable_memoize=3Doff:

Nested Loo= p =C2=A0(cost=3D0.70..521.98 rows=3D277 width=3D1381) (actual time=3D0.018.= .0.421 rows=3D231 loops=3D1)
=E2=94=9C Buffers: shared hit=3D859
=E2= =94=9C Index Scan using "QuestionUserStatus_user_question_pk" on = "QuestionUserStatus" =C2=A0(cost=3D0.42..178.88 rows=3D277 width= =3D18) (actual time=3D0.014..0.099 rows=3D231 loops=3D1)
=E2=94=82 =E2= =94=9C Index Cond: ("user" =3D '3477145805513'::bigint)=E2=94=82 =E2=94=94 Buffers: shared hit=3D166
=E2=94=94 Index Scan usi= ng questions_pkey on questions =C2=A0(cost=3D0.28..1.24 rows=3D1 width=3D13= 63) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)
=C2=A0 =E2=94=9C I= ndex Cond: (id =3D "QuestionUserStatus".question)
=C2=A0 =E2= =94=94 Buffers: shared hit=3D693
Planning:
=E2=94=94 Buffers: shared = hit=3D6
Planning Time: 0.197 ms
Execution Time: 0.444 ms
=

Thanks for the help,
Jacob
<= /blockquote>
=C2=A0
-= -
De= ath to <Redacted>, and butter sauce.
Don't boil me, I'm s= till alive.
<Redacted> lobster!
<= /div>
--0000000000002bc66606425505d5--