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