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

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 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> 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 <jej(dot)jackson(dot)08(at)gmail(dot)com>
> 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 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
>>
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>