When a query runs in PostgreSQL, it doesn't immediately start reading data from tables. The planner first examines different ways to execute the query and selects the one it estimates will be the most efficient. Depending on the structure of the query, the chosen execution plan may include additional plan nodes that help reduce unnecessary work during execution.
One of these plan nodes is materialize. It is not added to every query, and many users rarely notice it until it appears in the query execution plan. This node is mainly used to keeping the output of another plan node available so it can be read again without repeating the same operation. This can reduce repeated scans and improve the performance of certain execution plans.
show enable_material ;
Result :
enable_material
-----------------
on
(1 row)
We can check more metadata about this parameter from pg_settings.
select * from pg_settings where name = 'enable_material';
Result :
-[ RECORD 1 ]---+----------------------------------------------
name | enable_material
setting | on
unit |
category | Query Tuning / Planner Method Configuration
short_desc | Enables the planner's use of materialization.
extra_desc |
context | user
vartype | bool
source | session
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Create the sample tables for test the working of materialized nodes in the query execution plan.
CREATE TABLE res_partner
(
id int PRIMARY KEY,
name text
);
CREATE TABLE sale_order
(
id serial PRIMARY KEY,
partner_id int NOT NULL
);
Insert some random values
INSERT INTO res_partner
SELECT
i,
'Partner ' || i
FROM generate_series(1,1000) AS g(i);
INSERT INTO sale_order(partner_id)
SELECT
(random()*999 + 1)::int
FROM generate_series(1,500000);
Disable the hashjoin and merge join temporarily.
SET enable_hashjoin = off;
SET enable_mergejoin = off;
Enable the nestloop and material node like this.
SET enable_nestloop = on;
SET enable_material = on;
Now execute a query that has no subquery like this.
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT *
FROM sale_order s
JOIN res_partner p
ON s.partner_id = p.id;
Result :
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.28..142988.71 rows=500138 width=44) (actual time=2.269..1923.106 rows=500000.00 loops=1)
Output: s.id, s.partner_id, p.id, p.name
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1502214
-> Nested Loop (cost=0.28..91974.91 rows=294199 width=44) (actual time=2.543..1595.200 rows=250000.00 loops=2)
Output: s.id, s.partner_id, p.id, p.name
Inner Unique: true
Buffers: shared hit=1502214
Worker 0: actual time=2.974..2095.981 rows=331768.00 loops=1
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.258 ms (Deform 0.093 ms), Inlining 0.000 ms, Optimization 0.170 ms, Emission 2.767 ms, Total 3.194 ms
Buffers: shared hit=996773
-> Parallel Seq Scan on public.sale_order s (cost=0.00..5154.99 rows=294199 width=8) (actual time=0.008..310.893 rows=250000.00 loops=2)
Output: s.id, s.partner_id
Buffers: shared hit=2213
Worker 0: actual time=0.009..414.307 rows=331768.00 loops=1
Buffers: shared hit=1468
-> Index Scan using res_partner_pkey on public.res_partner p (cost=0.28..0.30 rows=1 width=36) (actual time=0.001..0.001 rows=1.00 loops=500000)
Output: p.id, p.name
Index Cond: (p.id = s.partner_id)
Index Searches: 500000
Buffers: shared hit=1500001
Worker 0: actual time=0.001..0.001 rows=1.00 loops=331768
Buffers: shared hit=995305
Planning Time: 0.092 ms
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.467 ms (Deform 0.174 ms), Inlining 0.000 ms, Optimization 0.302 ms, Emission 4.715 ms, Total 5.483 ms
Execution Time: 2536.672 ms
(33 rows)
Based on this execution plan, there is no materialized node here. This is the normal execution plan that uses the gather node and the parallel workers that scan sequentially in parallel mode.
Here we can see that the two workers were launched during this query execution
Gather (cost=1000.28..142988.71 rows=500138 width=44) (actual time=2.269..1923.106 rows=500000.00 loops=1)
Output: s.id, s.partner_id, p.id, p.name
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1502214
Now execute another query that should have a sub query like this.
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT *
FROM sale_order s
CROSS JOIN
(
SELECT *
FROM res_partner
) p limit 100000;
Result :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1251.45 rows=100000 width=23) (actual time=0.037..606.793 rows=100000.00 loops=1)
Output: s.id, s.partner_id, res_partner.id, res_partner.name
Buffers: shared hit=8
-> Nested Loop (cost=0.00..6257231.50 rows=500000000 width=23) (actual time=0.033..368.331 rows=100000.00 loops=1)
Output: s.id, s.partner_id, res_partner.id, res_partner.name
Buffers: shared hit=8
-> Seq Scan on public.sale_order s (cost=0.00..7213.00 rows=500000 width=8) (actual time=0.012..0.146 rows=100.00 loops=1)
Output: s.id, s.partner_id
Buffers: shared hit=2
-> Materialize (cost=0.00..21.00 rows=1000 width=15) (actual time=0.001..1.236 rows=1000.00 loops=100)
Output: res_partner.id, res_partner.name
Storage: Memory Maximum Storage: 56kB
Buffers: shared hit=6
-> Seq Scan on public.res_partner (cost=0.00..16.00 rows=1000 width=15) (actual time=0.009..1.256 rows=1000.00 loops=1)
Output: res_partner.id, res_partner.name
Buffers: shared hit=6
Planning Time: 0.096 ms
Execution Time: 727.342 ms
(18 rows)
Here we can see that this execution plan contains the materialized nodes.
-> Materialize (cost=0.00..21.00 rows=1000 width=15) (actual time=0.001..1.236 rows=1000.00 loops=100)
Output: res_partner.id, res_partner.name
Storage: Memory Maximum Storage: 56kB
Buffers: shared hit=6
Here we can see the query outputs from the materialised nodes named res_partner.id and res_partner.name. And also, we can see the maximum storage and the count of records hits from the cache.
Now set off to the variable named enable_material.
set enable_material = 'off';
Now execute the same query that has the sub query.
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT *
FROM sale_order s
CROSS JOIN
(
SELECT *
FROM res_partner
) p limit 100000;
Result :
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2442.60 rows=100000 width=23) (actual time=0.031..672.272 rows=100000.00 loops=1)
Output: s.id, s.partner_id, res_partner.id, res_partner.name
Buffers: shared hit=446
-> Nested Loop (cost=0.00..12213016.00 rows=500000000 width=23) (actual time=0.027..405.548 rows=100000.00 loops=1)
Output: s.id, s.partner_id, res_partner.id, res_partner.name
Buffers: shared hit=446
-> Seq Scan on public.res_partner (cost=0.00..16.00 rows=1000 width=15) (actual time=0.015..0.018 rows=1.00 loops=1)
Output: res_partner.id, res_partner.name
Buffers: shared hit=2
-> Seq Scan on public.sale_order s (cost=0.00..7213.00 rows=500000 width=8) (actual time=0.006..134.840 rows=100000.00 loops=1)
Output: s.id, s.partner_id
Buffers: shared hit=444
Planning Time: 0.104 ms
Execution Time: 804.976 ms
(14 rows)
Here we can see the query execution plan with no materialized nodes. Here the planner chooses the normal nested loop algorithm and the sequential scanning to fetch the records from the related table.
Now we can see the execution time difference when the value of the parameter named enable_material is on and off
enable_material - on
Execution Time: 727.342 ms
enable_material - off
Execution Time: 804.976 ms
The materialize node is a small but useful optimization of the PostgreSQL database that can be added to an execution plan when it expects the same set of rows to be read more than once. Instead of repeating the same operation, PostgreSQL stores the rows temporarily and reuses them whenever needed. This can reduce unnecessary work and improve execution time, especially in nested loop joins where the inner side does not change between iterations.