How to Understand Memoized Nodes in the Query Execution Plan

When a query is executed, PostgreSQL first creates an execution plan. Instead of following a single fixed approach, the planner examines different ways to execute the query and selects the plan it estimates will perform best.

One optimization the planner can use is a memoize node. The enable_memoize parameter controls whether the planner is allowed to add a memoize node to an execution plan. When a memoize node is used, postgresql caches the results of parameterized scans, such as index lookups performed during a nested loop join. If the same parameter value is encountered again, PostgreSQL returns the cached result instead of performing the scan again.

This can significantly reduce repeated index lookups when the same values appear many times during query execution.

show enable_memoize ;

Result :

 enable_memoize 
-----------------
 on
(1 row)

We can check the metadata of this parameter named enable_memoize from the pg_settings catalogue like this.

select * from pg_settings where name = 'enable_memoize';

Result :

-[ RECORD 1 ]---+--------------------------------------------
name            | enable_memoize
setting         | on
unit            | 
category        | Query Tuning / Planner Method Configuration
short_desc      | Enables the planner's use of memoization.
extra_desc      | 
context         | user
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | on
reset_val       | on
sourcefile      | 
sourceline      | 
pending_restart | f

Now we can easily understand how the memoize node works in postgres by the query examples below.

Create a sample table.

CREATE TABLE customers
(
    id integer PRIMARY KEY,
    name text
);
CREATE TABLE orders
(
    id integer PRIMARY KEY,
    customer_id integer
);

Insert the values.

INSERT INTO customers
SELECT
    i,
    'Customer ' || i
FROM generate_series(1,1000) AS g(i);
INSERT INTO orders
SELECT
    i,
    (random()*999 + 1)::int
FROM generate_series(1,1000000) AS g(i);

Execute vacuum and analyze for getting the better statistics for the postgres planner.

VACUUM ANALYZE customers;
VACUUM ANALYZE orders;

Now disable the hash join and merge join.

SET enable_hashjoin = off;
SET enable_mergejoin = off;

Enable the nestloop option in postgres

SET enable_nestloop = on;

By default the value of this parameter named enable_memoize is on.

show enable_memoize ;

Result:

 enable_memoize 
----------------
 on
(1 row)

Now execute the below join query with explain analyze to show the query plan provided by postgres.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
    *
FROM orders o
JOIN customers c
ON c.id = o.customer_id;

Result :

                                                                   QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..39702.85 rows=1000000 width=24) (actual time=0.026..6049.795 rows=1000000.00 loops=1)
   Output: o.id, o.customer_id, c.id, c.name
   Inner Unique: true
   Buffers: shared hit=7425
   ->  Seq Scan on public.orders o  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.006..1205.532 rows=1000000.00 loops=1)
         Output: o.id, o.customer_id
         Buffers: shared hit=4425
   ->  Memoize  (cost=0.29..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=1000000)
         Output: c.id, c.name
         Cache Key: o.customer_id
         Cache Mode: logical
         Hits: 999000  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 115kB
         Buffers: shared hit=3000
         ->  Index Scan using customers_pkey on public.customers c  (cost=0.28..0.29 rows=1 width=16) (actual time=0.002..0.002 rows=1.00 loops=1000)
               Output: c.id, c.name
               Index Cond: (c.id = o.customer_id)
               Index Searches: 1000
               Buffers: shared hit=3000
 Planning:
   Buffers: shared hit=73
 Planning Time: 0.218 ms
 Execution Time: 7212.668 ms
(22 rows)

Explanation :

Plan - 1 ( enable_memoize = on )

Here we can see the memoize node participation in the query plan.

  ->  Memoize  (cost=0.29..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=1000000)
         Output: c.id, c.name
         Cache Key: o.customer_id
         Cache Mode: logical
         Hits: 999000  Misses: 1000  Evictions: 0  Overflows: 0  Memory

Postgresql performs a sequential scanning on the orders table and reads one order at a time.

For each customer_id, it first checks the Memoize cache.

If that customer has already been looked up (cache hit), the postgresql immediately returns the cached row without accessing the customers table again.

If the customer is not in the cache (cache miss), postgresql performs an Index scan on the customers table to fetch the row.

The fetched customer row is then stored in the Memoize cache so that future orders with the same customer_id can reuse it.

Result:

Repeated index lookups are avoided, making the Nested Loop join much faster when many orders reference the same customers.

Statistics

Almost every lookup is served from memory.

Memoize statistics

Now set the memoize option to off and re-execute the same query again.

set enable_memoize = 'off';

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT
    *
FROM orders o
JOIN customers c
ON c.id = o.customer_id;

Result :

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.27..231479.47 rows=1000000 width=24) (actual time=5.885..2089.060 rows=1000000.00 loops=1)
   Output: o.id, o.customer_id, c.id, c.name
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=3004427
   ->  Nested Loop  (cost=0.28..130479.47 rows=416667 width=24) (actual time=4.314..2065.549 rows=333333.33 loops=3)
         Output: o.id, o.customer_id, c.id, c.name
         Inner Unique: true
         Buffers: shared hit=3004427
         Worker 0:  actual time=3.786..2731.234 rows=444090.00 loops=1
           JIT:
             Functions: 6
             Options: Inlining false, Optimization false, Expressions true, Deforming true
             Timing: Generation 0.259 ms (Deform 0.090 ms), Inlining 0.000 ms, Optimization 0.176 ms, Emission 3.554 ms, Total 3.988 ms
           Buffers: shared hit=1334236
         Worker 1:  actual time=3.497..2734.007 rows=442232.00 loops=1
           JIT:
             Functions: 6
             Options: Inlining false, Optimization false, Expressions true, Deforming true
             Timing: Generation 0.257 ms (Deform 0.088 ms), Inlining 0.000 ms, Optimization 0.164 ms, Emission 3.279 ms, Total 3.700 ms
           Buffers: shared hit=1328654
         ->  Parallel Seq Scan on public.orders o  (cost=0.00..8591.67 rows=416667 width=8) (actual time=0.009..408.425 rows=333333.33 loops=3)
               Output: o.id, o.customer_id
               Buffers: shared hit=4425
               Worker 0:  actual time=0.010..543.267 rows=444090.00 loops=1
                 Buffers: shared hit=1965
               Worker 1:  actual time=0.009..542.060 rows=442232.00 loops=1
                 Buffers: shared hit=1957
         ->  Index Scan using customers_pkey on public.customers c  (cost=0.28..0.29 rows=1 width=16) (actual time=0.001..0.001 rows=1.00 loops=1000000)
               Output: c.id, c.name
               Index Cond: (c.id = o.customer_id)
               Index Searches: 1000000
               Buffers: shared hit=3000002
               Worker 0:  actual time=0.001..0.001 rows=1.00 loops=444090
                 Buffers: shared hit=1332271
               Worker 1:  actual time=0.001..0.001 rows=1.00 loops=442232
                 Buffers: shared hit=1326697
 Planning Time: 0.132 ms
 JIT:
   Functions: 18
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.663 ms (Deform 0.241 ms), Inlining 0.000 ms, Optimization 0.472 ms, Emission 12.308 ms, Total 13.444 ms
 Execution Time: 3316.520 ms
(43 rows)

Postgresql performs a parallel sequential scan on the orders table, where multiple worker processes read different portions of the table at the same time.

For every order row, the worker takes the customer_id and immediately performs an index scan on the customers table.

The matching customer row is returned to the nested loop join.

This process is repeated for every order row, even if the same customer_id has already been looked up by the same worker.

Since the memoize node is disabled, PostgreSQL does not cache previously fetched customer rows. Every matching customer is retrieved through a new index lookup.

With Memoize ON, the postgres planner chooses a single-process nested loop with caching.

With Memoize OFF, the postgres planner chose a parallel nested loop with three workers (gather + parallel seq scan).

This is the main difference between when the value of enable_memoize is on and false.

By understanding these memoized nodes concept, we can easily identify the exact caching process during the query execution where the heavy index searches happen.