Memoize and Materialize: Reusing Work Inside a Nested Loop
When the inner side of a nested loop is asked for the same key over and over, PostgreSQL can put a Memoize node in front of it, caching results so a repeated lookup is answered from memory. Materialize plays a related role: it stores an input once so the plan can re-read it without recomputing. Both nodes are signs that the planner expected heavy repetition, and Memoize's hit-and-miss counts in EXPLAIN ANALYZE tell you whether that bet paid off. A Memoize with almost no hits is wasted memory and a hint that the estimate was wrong.
Questions this Concept answers
Why does a `Memoize` node pay off only under certain conditions?
J
jeremy
Text
Reading PostgreSQL EXPLAIN and EXPLAIN ANALYZE Output
When a database repeats the same lookup over and over inside a loop, it can put a Memoize step in front of that lookup to remember answers it has already found. The plan output shows a cache key plus…