Materialize

名称

物化算子

介绍

物化算子用在一些子选择上，优化器认为物化一些子选择要比重复的去扫描数据代价要低，因此物化操作经常用在 Join操作 中对内表的处理上。

举例

--清理环境
DROP TABLE t1 CASCADE;
DROP TABLE t2 CASCADE;

--创建环境
CREATE TABLE t1(a_id int, a date);
INSERT INTO t1 VALUES(1,'2005-1-1');
INSERT INTO t1 VALUES(2,'2006-1-1');
INSERT INTO t1 VALUES(3,'2007-1-1');
ANALYZE t1;

CREATE TABLE t2(b_id int,b text);
INSERT INTO t2 VALUES(1,'Tianjin');
INSERT INTO t2 VALUES(2,'Beking');
INSERT INTO t2 VALUES(3,'Chongqing');
INSERT INTO t2 VALUES(4,null);

ANALYZE t2;

EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON t2.b_id = 1;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Nested Loop(Left Join)  (cost=7.05..14.14 rows=3 width=28) (actual time=0.27..0.28 rows=3 loops=1)
   ->  Seq Scan on T1  (cost=0.00..7.03 rows=3 width=12) (actual time=0.02..0.03 rows=3 loops=1)
   ->  Materialize  (cost=7.05..7.06 rows=1 width=16) (actual time=0.08..0.08 rows=1 loops=3)
         ->  Seq Scan on T2  (cost=0.00..7.05 rows=1 width=16) (actual time=0.01..0.03 rows=1 loops=1)
               Scan Key: (B_ID = 1)
 Planning Time: 0.32 msec
 Execution Time: 0.19 msec
(7 rows)