postgresql explain query中的materialize

概述今天在GREENPLUM的执行计划中看到了materialize，不知道这个步骤是做什么的，查了下文档，没有相关解释。没事，GREENPLUM不行还有POSTGRESQL呢，GOOGLE了一把，找到了答案。 Q: What does materialize do? I'm joining two tables, not views or anything like that.  A: A mate

今天在GREENPLUM的执行计划中看到了materialize，不知道这个步骤是做什么的，查了下文档，没有相关解释。没事，GREENPLUM不行还有POSTGREsql呢，Google了一把，找到了答案。

Q:

What does materialize do? I'm joining two tables,not vIEws or anything like that.

A:

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.

具体的sql如下

aligputf8=# select count(1) from ttt1;
count
-------
10000
(1 row)

aligputf8=#
aligputf8=# select count(1) from ttt2;
count
-------
10000
(1 row)

aligputf8=# explain select * from ttt1 as a,ttt2 as b where a.ID=3;
query PLAN
---------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice2; segments: 6) (cost=0.00..1409.07 rows=1667 wIDth=8)
-> nested Loop (cost=0.00..1409.07 rows=1667 wIDth=8)
-> broadcast Motion 1:6 (slice1; segments: 1) (cost=0.00..137.07 rows=6 wIDth=4)
-> Seq Scan on ttt1 a (cost=0.00..137.00 rows=1 wIDth=4)
Filter: ID = 3
-> Seq Scan on ttt2 b (cost=0.00..112.00 rows=1667 wIDth=4)
(6 rows)

aligputf8=# explain select * from ttt1 as a,ttt2 as b where a.ID=3;
aligputf8=#
aligputf8=# explain select * from ttt1 as a,ttt2 as b where a.ID<3000;
query PLAN
------------------------------------------------------------------------------------------------
Gather Motion 6:1 (slice2; segments: 6) (cost=122.00..3599268.93 rows=4998334 wIDth=8)
-> nested Loop (cost=122.00..3599268.93 rows=4998334 wIDth=8)
-> broadcast Motion 6:6 (slice1; segments: 6) (cost=0.00..346.93 rows=2999 wIDth=4)
-> Seq Scan on ttt1 a (cost=0.00..137.00 rows=500 wIDth=4)
Filter: ID < 3000
-> Materialize (cost=122.00..222.00 rows=1667 wIDth=4)
-> Seq Scan on ttt2 b (cost=0.00..112.00 rows=1667 wIDth=4)
(7 rows)

我的理解就是PG为了加快nested loop循环的速度，把b表的数据缓存在了内存中。我们可以看到前面一个sql，只对b表数据进行1次扫描，因此并不需要进行缓存；后者需要进行2999次扫描。

总结

以上是内存溢出为你收集整理的postgresql explain query中的materialize全部内容，希望文章能够帮你解决postgresql explain query中的materialize所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。