视图和物化视图区别
HGDB中的视图是在查询的时候进行扫描子表的操作,而物化视图则是实实在在地将数据存成一张表。
物化视图是在HGDB 3.0.0之后才有的功能。
比较下视图和物化视图的性能
创建两个表
CREATE TABLE teacher (
id int NOT NULL,
sname varchar(100)
);
CREATE TABLE student (
sid int NOT NULL,
teacher_id int NOT NULL DEFAULT 0,
tname varchar(100)
);
创建一个视图
CREATE OR REPLACE VIEW student_view AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
创建一个物化视图
CREATE MATERIALIZED VIEW student_view_m AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
进行查询explain:
highgo=# explain select * from student_view;
QUERY PLAN
------------------------------------------------------------------------
Hash Left Join (cost=17.20..49.12 rows=512 width=448)
Hash Cond: (student.teacher_id = teacher.id)
-> Seq Scan on student (cost=0.00..13.20 rows=320 width=226)
-> Hash (cost=13.20..13.20 rows=320 width=222)
-> Seq Scan on teacher (cost=0.00..13.20 rows=320 width=222)
(5 rows)
highgo=# explain select * from student_view_m;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on student_view_m (cost=0.00..11.70 rows=170 width=448)
(1 row)
可以看出,student_view去每个表中进行查询,而student_view_m 直接去视图表查询,而物化视图的查询效率确确实实高于视图不少。
物化视图的数据填充
物化视图既然是一个实实在在存在的表,它就需要有数据填充过程,数据填充的命令是REFRESH MATERIALIZED VIEW
highgo=# \h REFRESH
Command: REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]
HGDB中的视图是在查询的时候进行扫描子表的操作,而物化视图则是实实在在地将数据存成一张表。
物化视图是在HGDB 3.0.0之后才有的功能。
比较下视图和物化视图的性能
创建两个表
CREATE TABLE teacher (
id int NOT NULL,
sname varchar(100)
);
CREATE TABLE student (
sid int NOT NULL,
teacher_id int NOT NULL DEFAULT 0,
tname varchar(100)
);
创建一个视图
CREATE OR REPLACE VIEW student_view AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
创建一个物化视图
CREATE MATERIALIZED VIEW student_view_m AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
进行查询explain:
highgo=# explain select * from student_view;
QUERY PLAN
------------------------------------------------------------------------
Hash Left Join (cost=17.20..49.12 rows=512 width=448)
Hash Cond: (student.teacher_id = teacher.id)
-> Seq Scan on student (cost=0.00..13.20 rows=320 width=226)
-> Hash (cost=13.20..13.20 rows=320 width=222)
-> Seq Scan on teacher (cost=0.00..13.20 rows=320 width=222)
(5 rows)
highgo=# explain select * from student_view_m;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on student_view_m (cost=0.00..11.70 rows=170 width=448)
(1 row)
可以看出,student_view去每个表中进行查询,而student_view_m 直接去视图表查询,而物化视图的查询效率确确实实高于视图不少。
物化视图的数据填充
物化视图既然是一个实实在在存在的表,它就需要有数据填充过程,数据填充的命令是REFRESH MATERIALIZED VIEW
highgo=# \h REFRESH
Command: REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]