MIME-Version: 1.0
From: Jacob Jackson <jej.jackson.08@gmail.com>
Date: Wed, 29 Oct 2025 14:28:40 -0400
Message-ID: <CAAiQw3yBPrCw6ZLeTwVS4QhKDWgJkmmp9LnGPdodxeQmn=kqVg@mail.gmail.com>
Subject: Postgres memoizing inner loop of join when outer loop join key is
 guaranteed unique?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e43cde0642504db4"
Archived-At: <https://www.postgresql.org/message-id/CAAiQw3yBPrCw6ZLeTwVS4QhKDWgJkmmp9LnGPdodxeQmn%3DkqVg%40mail.gmail.com>
Precedence: bulk

--000000000000e43cde0642504db4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 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 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 WHER=
E
"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) (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  (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 Usage:=
 320kB
  =E2=94=9C Buffers: shared hit=3D693
  =E2=94=94 Index Scan using questions_pkey on questions  (cost=3D0.28..1.2=
4 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) (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 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

--000000000000e43cde0642504db4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello. I was looking at some query=C2=A0plans recently and=
 noticed something that didn&#39;t make sense. I have a query that joins a =
table of questions with results for each question (using a table with a com=
posite 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 still memoizes the inner loop results=
. Any ideas why? Is this just a failure of the query planner (I would be ha=
ppy to explore creating a PR), did I not properly=C2=A0guarantee uniqueness=
, or is there another reason for memoization? The memoized=C2=A0version is =
consistently slightly slower in my testing, despite the calculated=C2=A0cos=
t being lower. Here are the query plans:<div><br></div><div><font face=3D"m=
onospace">enable_memoize=3Don:</font></div><div><font face=3D"monospace"><b=
r></font></div><div><font face=3D"monospace">EXPLAIN (ANALYZE, BUFFERS) SEL=
ECT * FROM questions LEFT JOIN &quot;QuestionUserStatus&quot; ON <a href=3D=
"http://questions.id">questions.id</a> =3D &quot;QuestionUserStatus&quot;.q=
uestion WHERE &quot;QuestionUserStatus&quot;.user =3D 0;</font></div><div><=
font face=3D"monospace"><br></font></div><div><font face=3D"monospace">Nest=
ed Loop =C2=A0(cost=3D0.71..514.09 rows=3D277 width=3D1381) (actual time=3D=
0.021..0.520 rows=3D231 loops=3D1)<br>=E2=94=9C Buffers: shared hit=3D859<b=
r>=E2=94=9C Index Scan using &quot;QuestionUserStatus_user_question_pk&quot=
; on &quot;QuestionUserStatus&quot; =C2=A0(cost=3D0.42..178.88 rows=3D277 w=
idth=3D18) (actual time=3D0.014..0.114 rows=3D231 loops=3D1)<br>=E2=94=82 =
=E2=94=9C Index Cond: (&quot;user&quot; =3D &#39;0&#39;::bigint)<br>=E2=94=
=82 =E2=94=94 Buffers: shared hit=3D166<br>=E2=94=94 Memoize =C2=A0(cost=3D=
0.29..1.25 rows=3D1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loop=
s=3D231)<br>=C2=A0 =E2=94=9C Cache Key: &quot;QuestionUserStatus&quot;.ques=
tion<br>=C2=A0 =E2=94=9C Cache Mode: logical<br>=C2=A0 =E2=94=9C Hits: 0 =
=C2=A0Misses: 231 =C2=A0Evictions: 0 =C2=A0Overflows: 0 =C2=A0Memory Usage:=
 320kB<br>=C2=A0 =E2=94=9C Buffers: shared hit=3D693<br>=C2=A0 =E2=94=94 In=
dex Scan using questions_pkey on questions =C2=A0(cost=3D0.28..1.24 rows=3D=
1 width=3D1363) (actual time=3D0.001..0.001 rows=3D1 loops=3D231)<br>=C2=A0=
 =C2=A0 =E2=94=9C Index Cond: (id =3D &quot;QuestionUserStatus&quot;.questi=
on)<br>=C2=A0 =C2=A0 =E2=94=94 Buffers: shared hit=3D693<br>Planning:<br>=
=E2=94=94 Buffers: shared hit=3D6<br>Planning Time: 0.183 ms<br>Execution T=
ime: 0.548 ms</font></div><div><font face=3D"monospace"><br></font></div><d=
iv><font face=3D"monospace">enable_memoize=3Doff:</font></div><div><font fa=
ce=3D"monospace"><br></font></div><div><font face=3D"monospace">Nested Loop=
 =C2=A0(cost=3D0.70..521.98 rows=3D277 width=3D1381) (actual time=3D0.018..=
0.421 rows=3D231 loops=3D1)<br>=E2=94=9C Buffers: shared hit=3D859<br>=E2=
=94=9C Index Scan using &quot;QuestionUserStatus_user_question_pk&quot; on =
&quot;QuestionUserStatus&quot; =C2=A0(cost=3D0.42..178.88 rows=3D277 width=
=3D18) (actual time=3D0.014..0.099 rows=3D231 loops=3D1)<br>=E2=94=82 =E2=
=94=9C Index Cond: (&quot;user&quot; =3D &#39;3477145805513&#39;::bigint)<b=
r>=E2=94=82 =E2=94=94 Buffers: shared hit=3D166<br>=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)<br>=C2=A0 =E2=94=9C I=
ndex Cond: (id =3D &quot;QuestionUserStatus&quot;.question)<br>=C2=A0 =E2=
=94=94 Buffers: shared hit=3D693<br>Planning:<br>=E2=94=94 Buffers: shared =
hit=3D6<br>Planning Time: 0.197 ms<br>Execution Time: 0.444 ms</font></div>=
<div><font face=3D"monospace"><br></font></div><div><font face=3D"arial, sa=
ns-serif">Thanks for the help,</font></div><div><font face=3D"arial, sans-s=
erif">Jacob</font></div></div>

--000000000000e43cde0642504db4--