SQL SQL解释计划：什么是Materialize？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3029841/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL explain plan: what is Materialize?
提问by Claudiu
I asked PostgreSQL to explain my query. Part of the explanation was:
我要求 PostgreSQL 解释我的查询。部分解释是：
table_name --> Materialize
What does materialize do? I'm joining two tables, not views or anything like that.
物化有什么作用？我要加入两个表，而不是视图或类似的东西。
回答by Magnus Hagander
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.
因此，在您的情况下，规划器正在确定对您的一个表进行扫描的结果将适合内存，并且直到可以选择需要重新扫描但仍然便宜的上连接操作。
回答by Guffa
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.
这意味着它不能使用任何索引（或类似方法）来使连接有效，因此作为最后的手段，将其中一个表的结果具体化，以便在连接另一个表时使用较小的集合。
回答by Yihe
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.
规划器将实现内部循环表，这意味着将整个表加载到内存缓冲区中以避免昂贵的磁盘 IO 成本。
A useful link.
一个有用的链接。
回答by Tel
From a more empirical point of view, you can do your EXPLAIN ANALYZEand keep those results, then change the flag:
从更经验的角度来看，您可以执行EXPLAIN ANALYZE并保留这些结果，然后更改标志：
set enable_material=off;
Go and run the same EXPLAIN ANALYZEagain 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.
EXPLAIN ANALYZE再次运行相同的程序以逐行比较结果，您将确切地看到发生了哪些变化，查询时间是好是坏等等。修改一长串查询配置标志并观察效果。
https://www.postgresql.org/docs/current/runtime-config-query.html
https://www.postgresql.org/docs/current/runtime-config-query.html
回答by MarmiK
We can say the Materializecommand will create a View of a table(just like a virtual table OR a SnapShotof table in Memory)
我们可以说该Materialize命令将创建一个View of a table（就像虚拟表或SnapShot内存中的表一样）
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!
使用 Materialize/Snap-Shot 的主要好处是减少之后对该表的查询成本。在执行计划中同样指出，如果我们使用 materialize 与不使用它相比，我们可以获得多少好处！
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..
我希望这有帮助..
