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