【PostgreSQL-9.6.3】物化视图

PostgreSQL 9.3 以后出现了物化视图。物化视图是由实实在在的数据组成，这是和一般视图的根本区别。

1. 物化视图创建语法如下：

--创建语法CREATE MATERIALIZED VIEW table_name    [ (column_name [, ...] ) ]    [ WITH ( storage_parameter [= value] [, ... ] ) ]    [ TABLESPACE tablespace_name ]    AS query    [ WITH [ NO ] DATA ]--刷新语法REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name    [ WITH [ NO ] DATA ]

2. 创建物化视图示例

--创建测试表test=# CREATE TABLE TEST01 (ID INT);CREATE TABLEtest=# INSERT INTO TEST01 SELECT GENERATE_SERIES(1,1000000);INSERT 0 1000000--创建物化视图test=# CREATE MATERIALIZED VIEW V_TEST01 AS SELECT * FROM TEST01;SELECT 1000000test=# test=# INSERT INTO TEST01 VALUES (1000001);INSERT 0 1test=# \timingTiming is on.--不加CONCURRENTLY参数的刷新test=# REFRESH MATERIALIZED VIEW V_TEST01;REFRESH MATERIALIZED VIEWTime: 1888.860 mstest=# INSERT INTO TEST01 VALUES (1000002);INSERT 0 1test=# CREATE UNIQUE INDEX INX_ID ON V_TEST01(ID);CREATE INDEX--加CONCURRENTLY参数的刷新test=# refresh materialized view concurrently v_test01;REFRESH MATERIALIZED VIEWTime: 100476.707 ms可见，加了CONCRRENTLY参数后，刷新时间明显增加。但加了CONCRRENTLY参数的更新不会阻塞查询操作。

 

The End！

2017-08-21