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 1vEE29-007v0o-Qz for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 21:48:45 +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 1vEE28-003yRx-IP for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 21:48:43 +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 1vEE28-003yRo-1a for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 21:48:43 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEE20-004SFi-1l for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 21:48:42 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-44dbb7e3c21so232954b6e.0 for ; Wed, 29 Oct 2025 14:48:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761774516; x=1762379316; 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=I/qmHu3Vc1x2dbDuBr0aM8n6KijHX66rPn88qyOLX6Y=; b=Rm4IIk8bxMznx3JBymnkwNm6MaPpxAyCBl1imwh1wRNBmruo97TfdZxWrHRBQE3fJ3 GDyS7FMUAyRVBindutYVpJwJaxIpvbOSC6ZNEWsDD5I/l9iOdtjJb4baZB8z/FTAE853 KeZWnwD6brAlq707JzFzSv04IJFv29PipGme0NbEkccVG0koTnUR6N2lJGqA3uUHHhb2 gp0M2p4S459Mp3xgzEkPSWiCeNQ0m5ZRHiZ/58BfSSYFQWgxC8KlQrUKcKOCI4doDXqa rOHCCI0bssEuUKfcgDghzcuW3MBGLSoQe6GC72oeTDcCBrNxJwBuyGS2dBCSaPLdcKNq QErQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761774516; x=1762379316; 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=I/qmHu3Vc1x2dbDuBr0aM8n6KijHX66rPn88qyOLX6Y=; b=QU3A5erw2ecuWjcpeRIB0YAGHyB4AB91E1i31zrEQHhQ8e6O8hlxz5cU2Z6iSy78HY TCYgKHHJPTARMBiJia7ZEWpflA72k+EDOceCpg16mkoCOKZtPBguuMJMUdp01KZ35QNI WZ+VknrMp2/zRaOmK2PaG/oV6tYi/7Hc465ng1yECVlmBSKqhsHusPO5YxdVhsdkMeeI m6G0OtwHGYvWvu1KL37ukOdeUIYpn77yfi+s1ZkfzmWQOepdnwATmYlDDv+4kD7q7mDq JkYrv3/WhCInJdrL/hT/J4PtAjitLK/LG/nVAWtW6wCB9Ir6dPG2V0/JBrhh0qGeqF7u RFww== X-Gm-Message-State: AOJu0YyqtmkCJ9/ajmWYMD0ucV/1sXtb+ARHul+gcNzL781qd1UtN0JX rJjmupF99q06CTvxlJtkW8p0NAXLpw5ISyAQ4/iL1s6L+py63PkFr5yuqdvboft6Pq7gvtx6fMn GMKu96PAWVpnEXLVtQlFdkxSSB9CHygg= X-Gm-Gg: ASbGncslUT8wsqIJrL0myOwfb5WK5DKfkZPPv4U2LwqLyEDwwmJIphY3IuAwRugI3aw ZoRvQbfM/As6FkWlpjsgC2kDkbeCISu1Jpctu1GaqKxE94yBnrSgbkcyr3Ii2koR1oq9RYVgT27 1I6Vwr2AiSLw6R4qiIT1XVtusxulN0P3pKnz+VX4sfWqfwXm3ZU3gtCf5Gy8g5MKKpSGNZWmtCa CM/vsejIRY18udH7lvY5TLYPWL5jiKJTFIia9ziJVw834AwqNnFQDzsEbgKRA== X-Google-Smtp-Source: AGHT+IFRofAUkLHjdBtWO4M0R3NJWEy0GaXZGifNDALvP9jUTzIHp4ay9DtUE8YaNVnWv9AZu0cdSOGq8z93T5ASRi0= X-Received: by 2002:a05:6808:1445:b0:43f:77d6:aeca with SMTP id 5614622812f47-44f89e78632mr438564b6e.38.1761774515700; Wed, 29 Oct 2025 14:48:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 29 Oct 2025 17:48:23 -0400 X-Gm-Features: AWmQ_bm9gE6Az5usZiahrbcLmLARPPH61wGX2gXR77WfKQrAFytr7kI7HGvLlxI Message-ID: Subject: Re: Postgres memoizing inner loop of join when outer loop join key is guaranteed unique? To: Jacob Jackson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002ec9fb064253183b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002ec9fb064253183b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 memoize > 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 1= 38 >> 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 something >>> 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 o= f >>> question id and a user id), filtered by user id. The question IDs and t= he >>> combined question-userIds are guaranteed unique due to being primary ke= ys, >>> 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) (actu= al >>> 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 ti= me=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 Us= age: 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=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.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) (actu= al >>> 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 >>> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000002ec9fb064253183b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You're running slightly different queries:
<= div>enable_memoize=3Don:=C2=A0= Index Cond: ("user" =3D = 9;0'::bigint)
enable_memoize=3Doff:=C2=A0Index Cond: ("user" =3D '3477145805513'::bigi= nt)

All buffer=C2=A0counts look to be the s= ame on=C2=A0both, and 514 is just 1.5% smaller than 522.=C2=A0 That looks l= ike statistical noise to me.


On W= ed, Oct 29, 2025 at 5:34=E2=80=AFPM Jacob Jackson <jej.jackson.08@gmail.com> wrote:
I was curio= us to see whether there was any reason I wasn't seeing for Postgres to = decide the memoized version was lower cost and try to memoize these operati= ons.

On Wed, Oct 29, 2025 at 3:20=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com&g= t; wrote:
What's the actual problem?=C2=A0 Does=C2=A0enable_memoize=3Don=C2=A0return incorrect= results?

Because a 45 microsecond (yes, 45 micros= econds: 0.138 milliseconds =3D 138 microseconds; same for the others) slowd= own isn't something I'd get too worked up about.

On Wed, Oct 29, 2025 at 2:29=E2=80=AFPM Jacob Jackson &l= t;jej.jackson= .08@gmail.com> wrote:
Hello. I was looking a= t some query=C2=A0plans recently and noticed something that didn't make= sense. I have a query that joins a table of questions with results for eac= h 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 keys, and yet Postgres s= till 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 no= t properly=C2=A0guarantee uniqueness, or is there another reason for memoiz= ation? The memoized=C2=A0version is consistently slightly slower in my test= ing, despite the calculated=C2=A0cost being lower. Here are the query plans= :

enable_memoize=3Don:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM questions LEFT JOIN "Qu= estionUserStatus" ON questions.id =3D "QuestionUserStatus".question WHERE "Q= uestionUserStatus".user =3D 0;

Nested 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=3D859
=E2=94=9C Index= Scan using "QuestionUserStatus_user_question_pk" on "Questi= onUserStatus" =C2=A0(cost=3D0.42..178.88 rows=3D277 width=3D18) (actua= l time=3D0.014..0.114 rows=3D231 loops=3D1)
=E2=94=82 =E2=94=9C Index Co= nd: ("user" =3D '0'::bigint)
=E2=94=82 =E2=94=94 Buffe= rs: 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 loops=3D231)
=C2=A0 = =E2=94=9C Cache Key: "QuestionUserStatus".question
=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 Index Scan using qu= estions_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".question)
=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

enable_memoize=3Doff:
Nested Loop =C2=A0(cost=3D0.70= ..521.98 rows=3D277 width=3D1381) (actual time=3D0.018..0.421 rows=3D231 lo= ops=3D1)
=E2=94=9C Buffers: shared hit=3D859
=E2=94=9C Index Scan usi= ng "QuestionUserStatus_user_question_pk" on "QuestionUserSta= tus" =C2=A0(cost=3D0.42..178.88 rows=3D277 width=3D18) (actual time=3D= 0.014..0.099 rows=3D231 loops=3D1)
=E2=94=82 =E2=94=9C Index Cond: (&quo= t;user" =3D '3477145805513'::bigint)
=E2=94=82 =E2=94=94 Bu= ffers: shared hit=3D166
=E2=94=94 Index Scan using questions_pkey on que= stions =C2=A0(cost=3D0.28..1.24 rows=3D1 width=3D1363) (actual time=3D0.001= ..0.001 rows=3D1 loops=3D231)
=C2=A0 =E2=94=9C Index 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 h= elp,
Jacob
=C2=A0
--
Death to <Redacted>, a= nd butter sauce.
Don't boil me, I'm still alive.
&= lt;Redacted> lobster!
--0000000000002ec9fb064253183b--