Subject: Postgres memoizing inner loop of join when outer loop join key is guaranteed unique?

From: Jacob Jackson <[email-masked]>
Date: Wed, 29 Oct 2025 14:28:40 -0400

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=on:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM questions LEFT JOIN
"QuestionUserStatus" ON questions.id = "QuestionUserStatus".question WHERE
"QuestionUserStatus".user = 0;

Nested Loop  (cost=0.71..514.09 rows=277 width=1381) (actual
time=0.021..0.520 rows=231 loops=1)
├ Buffers: shared hit=859
├ Index Scan using "QuestionUserStatus_user_question_pk" on
"QuestionUserStatus"  (cost=0.42..178.88 rows=277 width=18) (actual
time=0.014..0.114 rows=231 loops=1)
│ ├ Index Cond: ("user" = '0'::bigint)
│ └ Buffers: shared hit=166
└ Memoize  (cost=0.29..1.25 rows=1 width=1363) (actual time=0.001..0.001
rows=1 loops=231)
  ├ Cache Key: "QuestionUserStatus".question
  ├ Cache Mode: logical
  ├ Hits: 0  Misses: 231  Evictions: 0  Overflows: 0  Memory Usage: 320kB
  ├ Buffers: shared hit=693
  └ Index Scan using questions_pkey on questions  (cost=0.28..1.24 rows=1
width=1363) (actual time=0.001..0.001 rows=1 loops=231)
    ├ Index Cond: (id = "QuestionUserStatus".question)
    └ Buffers: shared hit=693
Planning:
└ Buffers: shared hit=6
Planning Time: 0.183 ms
Execution Time: 0.548 ms

enable_memoize=off:

Nested Loop  (cost=0.70..521.98 rows=277 width=1381) (actual
time=0.018..0.421 rows=231 loops=1)
├ Buffers: shared hit=859
├ Index Scan using "QuestionUserStatus_user_question_pk" on
"QuestionUserStatus"  (cost=0.42..178.88 rows=277 width=18) (actual
time=0.014..0.099 rows=231 loops=1)
│ ├ Index Cond: ("user" = '3477145805513'::bigint)
│ └ Buffers: shared hit=166
└ Index Scan using questions_pkey on questions  (cost=0.28..1.24 rows=1
width=1363) (actual time=0.001..0.001 rows=1 loops=231)
  ├ Index Cond: (id = "QuestionUserStatus".question)
  └ Buffers: shared hit=693
Planning:
└ Buffers: shared hit=6
Planning Time: 0.197 ms
Execution Time: 0.444 ms

Thanks for the help,
Jacob

---

From: Ron Johnson <[email-masked]>
Date: Wed, 29 Oct 2025 15:20:23 -0400

What's the actual problem?  Does enable_memoize=on return incorrect results?

Because a 45 microsecond (yes, 45 microseconds: 0.138 milliseconds = 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 PM Jacob Jackson <[email-masked]>
wrote:

> "QuestionUserStatus" ON questions.id = "QuestionUserStatus".question
> WHERE "QuestionUserStatus".user = 0;
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Jacob Jackson <[email-masked]>
Date: Wed, 29 Oct 2025 17:34:08 -0400

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.

---

From: Ron Johnson <[email-masked]>
Date: Wed, 29 Oct 2025 17:48:23 -0400

You're running slightly different queries:
enable_memoize=on: Index Cond: ("user" = '0'::bigint)
enable_memoize=off: Index Cond: ("user" = '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 PM Jacob Jackson <[email-masked]>
wrote:

>>>   └ Index Scan using questions_pkey on questions  (cost=0.28..1.24
>>> rows=1 width=1363) (actual time=0.001..0.001 rows=1 loops=231)

---

From: Jacob Jackson <[email-masked]>
Date: Wed, 29 Oct 2025 20:06:15 -0400

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 PM Ron Johnson <[email-masked]> wrote:

> 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.
>
>
>>>> └ Memoize  (cost=0.29..1.25 rows=1 width=1363) (actual
>>>> time=0.001..0.001 rows=1 loops=231)