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 protected]> 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!
