Parameter · Query Tuning / Planner Method Configuration
enable_memoize
Enables the planner's use of memoization.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
In a nested loop where the same lookup value comes up again and again, Memoize remembers the answer for each value. The second time the value appears, it answers from the cache instead of searching the index again. Turn it off if a Memoize node turns out slower than expected.
For DBAs and operators
Memoize (PostgreSQL 14+) sits between a Nested Loop and its parameterized inner scan. Its cache key is the outer value (Cache Key: u.c), and its memory is limited by work_mem × hash_mem_multiplier, evicting least recently used entries. EXPLAIN ANALYZE shows Hits, Misses, Evictions and Overflows.
Measured on 18.6: 1,000 outer rows with only 10 distinct join values: Nested Loop + Memoize over an index scan, cost 2,555. With enable_memoize = off the planner chose a Merge Join instead, cost 3,022 (EXPLAIN ANALYZE of the Memoize plan: Hits: 990 Misses: 10).
The win depends on the planner's estimate of distinct outer values (n_distinct). If it guesses few distinct values but there are many, the cache misses constantly and adds overhead: compare Misses with the number of outer rows in EXPLAIN ANALYZE. This switch is the first thing to try when a nested loop with Memoize misbehaves.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_nestloop enable_material hash_mem_multiplier work_mem