postgres中的视图和物化视图

创建环境表　　

--创建表
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)
);

INSERT into teacher(id,sname) SELECT id,id || 'lili' from generate_series(1,3) as id;

INSERT INTO student (sid,teacher_id,tname) SELECT id,tea,id || 'qqqv' from generate_series(1,4) as id , generate_series(1,3) as tea;

创建普通视图

CREATE VIEW teacher_V as SELECT id,b.tname from teacher a JOIN student b ON a.id = b.teacher_id;

创建物化视图

CREATE MATERIALIZED VIEW teacher_M as SELECT id,b.tname from teacher a JOIN student b ON a.id = b.teacher_id;

区别分析

　　01,插入数据

INSERT into student VALUES (10001,2,'lipeng');

　　02,普通视图查看

SELECT * from teacher_V
   .....
    3    3qqqv
    1    4qqqv
    2    4qqqv
    3    4qqqv
    2    lipeng

　　03,物化视图查看

SELECT * from teacher_M
......
3    3qqqv
1    4qqqv
2    4qqqv
3    4qqqv

　　04, 结果对比

　　　从结果上发现了问题,我插入了值, 普通视图能直接获取到,但是物化视图缺不能获取到

　　  05, 物化视图如何获取?

kingledb=# \help  refresh materialized view
Command:     REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

刷新物化视图

　　　　

refresh MATERIALIZED VIEW teacher_M with no data;


--查询--


SELECT * from teacher_M
> ERROR:  materialized view "teacher_m" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

发现刷新了这个报错了?

    其实这个是no data的参数, 因为视图进行了更改,你使用no data 刷新就会报错,这个适用于 快速无数据刷新
    如果产生了数据的话需要使用 

refresh MATERIALIZED VIEW teacher_M with  data;

然后 查询 就可以了

　　06, 查询执行计划

EXPLAIN SELECT * from teacher_V;
Hash Join  (cost=17.20..156.66 rows=3024 width=11)
  Hash Cond: (b.teacher_id = a.id)
  ->  Seq Scan on student b  (cost=0.00..28.90 rows=1890 width=11)
  ->  Hash  (cost=13.20..13.20 rows=320 width=4)
        ->  Seq Scan on teacher a  (cost=0.00..13.20 rows=320 width=4)


EXPLAIN SELECT * from teacher_M
    Seq Scan on teacher_m  (cost=0.00..31.40 rows=2140 width=10)

　　　　会发现这个物化视图直接视图抽取结果,所以每次数据是特别块出来的,普通视图还会去每个表查询

　　　07,场景分析

　　　　物化视图适合的场景应该是对数据的实时性要求不高的场景。