【PostgreSQL】物化视图

2. 创建物化视图示例

--创建测试表
test=# CREATE TABLE TEST01 (ID INT);
CREATE TABLE
test=# INSERT INTO TEST01 SELECT GENERATE_SERIES(1,1000000);
INSERT 0 1000000

--创建物化视图
test=# CREATE MATERIALIZED VIEW V_TEST01 AS SELECT * FROM TEST01;
SELECT 1000000
test=# 
test=# INSERT INTO TEST01 VALUES (1000001);
INSERT 0 1
test=# \timing
Timing is on.

--不加CONCURRENTLY参数的刷新
test=# REFRESH MATERIALIZED VIEW V_TEST01;
REFRESH MATERIALIZED VIEW
Time: 1888.860 ms
test=# INSERT INTO TEST01 VALUES (1000002);
INSERT 0 1
test=# CREATE UNIQUE INDEX INX_ID ON V_TEST01(ID);
CREATE INDEX

--加CONCURRENTLY参数的刷新
test=# refresh materialized view concurrently v_test01;
REFRESH MATERIALIZED VIEW
Time: 100476.707 ms

可见，加了CONCRRENTLY参数后，刷新时间明显增加。但加了CONCRRENTLY参数的更新不会阻塞查询操作。

The End！

2017-08-21

【PostgreSQL】物化视图

标签：int   new   ref   rate   tables   ora   操作   postgre   font