On Sat, Jul 25, 2026 at 5:04 AM Jacob Brazeal <[email protected]> wrote: > --setup > CREATE TABLE tenk1 AS > SELECT g AS unique1, g%2 AS two, g%10 AS ten, g%20 AS twenty, g%100 AS hundred > FROM generate_series(0,9999) g; > CREATE INDEX tenk1_unique1 ON tenk1(unique1); > CREATE INDEX tenk1_hundred ON tenk1(hundred); > ANALYZE tenk1; > > SET enable_seqscan=off; SET enable_mergejoin=off; SET work_mem='64kB'; > > Now return the query below with each of these settings: > > SET enable_memoize = on; -- 82000 (incorrect) > SET enable_memoize = off; -- 100000 (correct) > > SELECT sum(c) FROM ( > SELECT t0.unique1, > (SELECT count(*) FROM tenk1 t2 JOIN tenk1 t1 > ON t1.unique1 = t2.hundred + t0.ten > WHERE t1.twenty = t0.ten) AS c > FROM tenk1 t0 WHERE t0.unique1 < 200) s;
Thanks for the report! ExecReScanMemoize() purges the whole cache only when a parameter that is not part of the cache key changes. It assumes that a change to a cache-key parameter is handled by looking up a different cache entry. But that assumption fails when a parameter is buried inside a larger cache-key expression and also appears elsewhere in the subplan. In the reported case, there is a cache key of "t2.hundred + t0.ten" and a separate filter "t1.twenty = t0.ten". The problem is that different (t2.hundred, t0.ten) pairs can produce the same cache-key value while the filter selects different rows. I think we can fix it by dropping from keyparamids any parameter that also appears outside the cache-key expressions. See the attached patch. - Richard
v1-0001-Flush-Memoize-cache-when-a-buried-cache-key-param.patch
Description: Binary data
