SQL解释计划：什么是Materialize？

Question

我要求 PostgreSQL 解释我的查询。其中一部分解释是：

table_name --> Materialize

materialize函数是什么？我正在连接两个表，不是视图或类似的东西。

- Claudiu

Answer 1

Materialize节点指的是它下面树中任何内容(可能是扫描、完整的联接或类似操作)的输出在上方节点执行之前会被存储到内存中。通常情况下，当外部节点需要一个可重新扫描的来源时，才会这样做。

因此，在您的情况下，规划器确定了扫描您其中一个表格的结果将适合于内存，并且这将使选择需要重新扫描的上层联接操作变得更加便宜。

- Magnus Hagander

---

我更喜欢这个答案——实现的原因不仅仅是不使用索引。 - rfusca

简单来说，它会存储中间结果。 - Grant Johnson

你可以使用不同的缓冲区配置变量来影响触发此行为的阈值，同时也可以设置 "set from_collapse_limit=1; set join_collapse_limit=1"。这对于测试或运行时查询计划优化非常有用。 - Joe Atzberger

你有文档链接吗？我找不到任何文档。 - metdos

Answer 2

这意味着它不能使用任何索引（或类似的方法）来使连接更高效，因此作为最后的手段，它会将其中一个表的结果物化，以便在与另一个表连接时有一个较小的数据集可供使用。

- Guffa

---

这只是部分正确的。在许多（大多数）情况下，它不会仅仅因为无法使用索引而放置一个materialize节点 - 它只会在每个表上进行顺序扫描。将seqscan的输出材料化并没有太大的区别 - 我发现它更常见地出现在树的较高位置，而不仅仅是在扫描节点上方。 - Magnus Hagander

@Magnus：是的，如果有任何其他方法来连接这些表，系统会使用它，但在这种情况下显然没有，因此最后的选择是使用物化视图。 - Guffa

不，它不会作为最后的选择这样做。最后的选择是只进行顺序扫描而不进行物化。它这样做是因为可以采用更智能的计划，并且不必退回到最后的手段。 - Magnus Hagander

@Magnus：Materialize总是作为最后一个选项插入：“如果Merge Join运算符的内部输入集不是由Seq Scan、Index Scan、Sort或Materialize运算符产生的，则规划器/优化器将在计划中插入一个Materialize运算符。” http://www.iphelp.ru/faq/15/ch04lev1sec3.html - Guffa

@Guffa：只有在连接是合并连接时才是真的。这不太可能被选择为最后的计划。它可能会作为最后的手段发生，但不会使用序列扫描（正如我们在这里所拥有的）-你引用的文本实际上告诉你这一点。顺便说一句，你提到的书的版本是针对古老版本的PostgreSQL - 对于那里面的大多数事情，我会至少检查两次。 - Magnus Hagander

@Guffa：我的理解是，只有在以下两种情况下才会插入Materialise节点：(a)存在一个廉价的子计划需要多次扫描其输入行（例如普通嵌套循环连接的内部）；(b)重新生成这些行将会很昂贵（例如如果输入来自大表的顺序扫描，但预计只会产生少量行）。 - j_random_hacker

Answer 3

在合并连接和嵌套循环连接中，数据库将会“重新扫描”内部循环。 基本上就像：

for each row in outer table:
    for each row in inner table:
        # do something

规划器将实现内部循环表，这意味着将整个表加载到内存缓冲区中，以避免昂贵的磁盘IO成本。

一个有用的链接。

- Yihe

Answer 4

从更实证的角度来看，您可以执行EXPLAIN ANALYZE并保存这些结果，然后更改标志：

set enable_material=off;

运行相同的EXPLAIN ANALYZE，逐行比较结果，您将看到确切的更改，无论查询时间是更好还是更差等。尝试调整查询配置标志的长列表并观察其效果。

https://www.postgresql.org/docs/current/runtime-config-query.html

- Tel

Answer 5

我们可以说，Materialize命令将创建一个表的视图（就像内存中的虚拟表或快照）。

它用于通过以更多信息化的上下文呈现数据来增强数据的可见性，并通过隐藏关键或敏感数据使不需要查看它的用户无法访问它/或者我们不想向他们展示它来控制对数据的访问。

使用Materialize/Snap-Shot的主要好处是减少之后查询该表时的成本。执行计划同样说明了如果使用Materialize与未使用相比，我们可以获得多少收益！ 解释如何检查执行计划

->  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)

我忽略了这个-->，因为在我的知识中没有这样的运算符，而且--会注释掉后面的表达式，我假设你只是指这个->。

希望这可以帮到你。

- MarmiK

---

哦！现在我明白了，:) 对于混淆我感到很抱歉。链接 - MarmiK