SQL explain plan: what is Materialize?
SqlPostgresqlSql Execution-PlanSql Problem Overview
I asked PostgreSQL to explain my query. Part of the explanation was:
table_name --> Materialize
What does materialize do? I'm joining two tables, not views or anything like that.
Sql Solutions
Solution 1 - Sql
A materialize node means the output of whatever is below it in the tree (which can be a scan, or a full set of joins or something like that) is materalized into memory before the upper node is executed. This is usually done when the outer node needs a source that it can re-scan for some reason or other.
So in your case, the planner is determining that the result of a scan on one of your tables will fit in memory, and it till make it possible to choose an upper join operation that requires rescans while still being cheaper.
Solution 2 - Sql
It means that it can't use any index (or similar method) to make the join efficient, so as a last resort is materializes the result from one of the tables to have a smaller set to work with when joining against the other table.
Solution 3 - Sql
In merge join and nested loop join, the database will "rescan" the inner loop. Basically like:
for each row in outer table:
for each row in inner table:
# do something
The planner will materializes the inner loop table which means load the whole table in an in-memory buffer to avoid the expensive disk IO cost.
A useful link.
Solution 4 - Sql
We can say the Materialize command will create a View of a table(just like a virtual table OR a SnapShot of table in Memory)
It is used to enhance visibility of the data by presenting it in a more informational context and to control access to the data by hiding critical or sensitive data from users who don't need to see it/or to whom we don't what to show it.
The main benefit of using Materialize/Snap-Shot is to reduce the cost of query on that table afterwards. In execution plan the same states that how much benefit we may fetch if we use materialize compare to when it is not used!
Explain to check execution plan
-> Materialize (cost=0.29..8.51 rows=10 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
Index Cond: (unique2 < 10)
I have ignored this --> as there is not such operator in my knowledge, plus the -- will comment the expression afterwards, I have assumed you meant this -> only.
I hope this helps..
Solution 5 - Sql
From a more empirical point of view, you can do your EXPLAIN ANALYZE and keep those results, then change the flag:
set enable_material=off;
Go and run the same EXPLAIN ANALYZE again to compare the results line by line and you will see exactly what changes, whether the query time is better or worse, etc. Tinker around with the long list of query config flags and observe the effects.
https://www.postgresql.org/docs/current/runtime-config-query.html