Memoize in PostgreSQL EXPLAIN

What it does

In a Nested Loop, the inner side runs once per outer row. If many outer rows share the same join value (many order lines for the same product), the inner side looks up the same thing again and again. Memoize sits between the loop and the inner side and remembers the result for each key value. The first time a key comes up it runs the inner side and stores the rows (a miss); the next time it returns the stored rows (a hit).

The cache is a hash table limited to work_mem × hash_mem_multiplier (8 MB with defaults on PostgreSQL 15 and later). When it’s full, the least recently used entries are thrown out (evictions).

It was added in PostgreSQL 14 (enable_memoize, on by default). Since 16 it can also sit above a UNION ALL.

When the planner picks it

• A nested loop with a parameterised inner side (an index lookup using the outer row’s value).
• The planner expects the join key to repeat a lot among the outer rows: few distinct values compared with the number of lookups. It estimates that from the column’s n_distinct statistics.
• The inner side is large enough that hashing all of it for a Hash Join would cost more than looking up the distinct keys.

Reading its numbers

->  Memoize  (cost=0.30..1.02 rows=1 width=17) (actual time=0.001..0.001 rows=1.00 loops=6000)
      Cache Key: l.product_id
      Cache Mode: logical
      Hits: 4000  Misses: 2000  Evictions: 0  Overflows: 0  Memory Usage: 235kB
      ->  Index Scan using products_pkey on products p  (… rows=1.00 loops=2000)

• Cache Key: the outer value used to look up the cache.
• Cache Mode: logical compares keys with the data type’s equality operator; binary compares their stored bytes, which the planner uses in some cases such as LATERAL subqueries.
• Hits: lookups answered from the cache. Misses: lookups that ran the inner side. Hits + misses = the Memoize’s loops.
• The child’s loops equals Misses: here the index scan ran 2,000 times instead of 6,000.
• Evictions: entries thrown out to make room. Evicted keys that come back become misses again.
• Overflows: times the rows for a single key didn’t fit in the cache at all.
• Memory Usage: peak cache size.

A useful quick ratio: hits ÷ (hits + misses). Here 67%. In a parallel plan, each process has its own cache, and you’ll see a Worker N: line with each one’s counts.

When it’s a problem

1. Mostly misses. If nearly every lookup is a miss, the cache added overhead and saved nothing. The planner thought keys would repeat and they didn’t, usually because n_distinct for the join column is wrong. Run ANALYZE; for a column whose distinct count the sample keeps getting wrong, set it: ALTER TABLE t ALTER COLUMN c SET (n_distinct = …) and analyse again. To compare plans, SET enable_memoize = off in your session.
2. Evictions. The cache filled and dropped entries that were then needed again. In the example with work_mem at 64 kB, the leader’s cache had 166 hits and 1,091 evictions. Raise work_mem (or hash_mem_multiplier) for the query if the cache is close to fitting.
3. The nested loop is wrong to begin with. Memoize makes a nested loop cheaper, and that can tip the planner towards a nested loop when the outer side is underestimated. If the outer row count is far higher than estimated, fix the estimate first; see Nested Loop.

Example

PostgreSQL 18.6, default settings. A catalogue of 100,000 products, and order lines that only ever use 2,000 of them:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE products (
  id    int PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO products
SELECT i, 'Product ' || i, round((i % 500) + 0.99, 2)
FROM generate_series(1, 100000) AS i;

CREATE TABLE order_lines (
  order_id   bigint NOT NULL,
  line_no    int NOT NULL,
  product_id int NOT NULL,
  qty        int NOT NULL,
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO order_lines
SELECT o, l, 1 + ((o * 31 + l * 17) % 2000) * 50, 1 + (o + l) % 5
FROM generate_series(1, 300000) AS o, generate_series(1, 3) AS l;
VACUUM ANALYZE products, order_lines;

The lines of the first 2,000 orders, with product names:

EXPLAIN (ANALYZE, BUFFERS)
SELECT l.order_id, p.name, l.qty
FROM order_lines l JOIN products p ON p.id = l.product_id
WHERE l.order_id <= 2000;

Nested Loop  (cost=165.20..8211.82 rows=5738 width=25) (actual time=0.185..5.783 rows=6000.00 loops=1)
  Buffers: shared hit=6074
  ->  Bitmap Heap Scan on order_lines l  (cost=164.89..6163.64 rows=5738 width=16) (actual time=0.174..0.645 rows=6000.00 loops=1)
        Recheck Cond: (order_id <= 2000)
        Heap Blocks: exact=41
        Buffers: shared hit=74
        ->  Bitmap Index Scan on order_lines_pkey  (cost=0.00..163.46 rows=5738 width=0) (actual time=0.157..0.157 rows=6000.00 loops=1)
              Index Cond: (order_id <= 2000)
              Index Searches: 1
              Buffers: shared hit=33
  ->  Memoize  (cost=0.30..1.02 rows=1 width=17) (actual time=0.001..0.001 rows=1.00 loops=6000)
        Cache Key: l.product_id
        Cache Mode: logical
        Hits: 4000  Misses: 2000  Evictions: 0  Overflows: 0  Memory Usage: 235kB
        Buffers: shared hit=6000
        ->  Index Scan using products_pkey on products p  (cost=0.29..1.01 rows=1 width=17) (actual time=0.002..0.002 rows=1.00 loops=2000)
              Index Cond: (id = l.product_id)
              Index Searches: 2000
              Buffers: shared hit=6000
Planning:
  Buffers: shared hit=13
Planning Time: 0.150 ms
Execution Time: 5.999 ms

6,000 lines, 2,000 distinct products: each product was looked up once and served from the cache twice more. PostgreSQL 14.24 and 17.11 chose the same plan with the same Hits: 4000 Misses: 2000 line.

With SET work_mem = '64kB', the planner went parallel and the cache, at 128 kB per process, was too small:

->  Memoize  (cost=0.30..1.02 rows=1 width=17) (actual time=0.002..0.002 rows=1.00 loops=6000)
      Cache Key: l.product_id
      Cache Mode: logical
      Hits: 166  Misses: 2183  Evictions: 1091  Overflows: 0  Memory Usage: 129kB
      Buffers: shared hit=14003
      Worker 0:  Hits: 696  Misses: 1372  Evictions: 280  Overflows: 0  Memory Usage: 129kB
      Worker 1:  Hits: 471  Misses: 1112  Evictions: 20  Overflows: 0  Memory Usage: 129kB
      ->  Index Scan using products_pkey on products p  (cost=0.29..1.01 rows=1 width=17) (actual time=0.002..0.002 rows=1.00 loops=4667)

The first line is the leader’s own cache. Across the three processes, 4,667 of 6,000 lookups were misses, and the index scan ran 4,667 times instead of 2,000.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. With your own Anthropic API key, Ask Claude (⌘L) explains a plan; it sends the plan and schema, never rows.