PostgreSQL从9.3版本开始支持物化视图(materialized view)。虽然有些功能上不如Oracle来的强大，多数情况下也足够使用了。
物化视图功能比较
postgres的物化视图和Oracle的比较如下表，
| 创建视图 |
create materialized view ... |
create materialized view ... |
| 删除视图 |
drop materialized view ... |
drop materialized view ... |
| 定时刷新 |
通过插件pgagent |
on demand start with ... next 参数 |
| 增量刷新 |
不支持 |
refresh fast 参数 |
| 读时刷新 |
预期9.4版本支持 |
支持 |
| commit刷新 |
通过trigger实现 |
on commit 参数 |
| 手动刷新 |
refresh materialized view ... |
EXEC DBMS_MVIEW.REFRESH( ... ) |
| 自动刷新 |
不支持 |
force 参数 |
| 添加索引 |
create index ... |
create index ... |
| 视图直接查询 |
支持 |
支持 |
| 计划自动替换 |
不支持 |
支持 |
postgres物化视图功能演示
本文使用的测试数据是使用pgbench生成的1600000条数据，详见我的另一篇文章pgbench压力测试工具简介。在pgbench_accounts表中有1600000条数据，其中bid列有16个取值，每个取值分别有100000条数据。测试用的数据库是PostgreSQL 9.3.4版本
使用create materialized view创建物化视图
1 2
| pgbench=# create materialized view mv_pgbench_accounts as select * from pgbench_accounts where bid < 3; SELECT 200000
|
默认情况下，创建物化视图的时候会全量刷新视图，即启用with data选项。
查询物化视图
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| pgbench=# select count(*) from mv_pgbench_accounts; count -------- 200000 (1 row) pgbench=# explain select count(*) from mv_pgbench_accounts; QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=5779.00..5779.01 rows=1 width=0) -> Seq Scan on mv_pgbench_accounts (cost=0.00..5279.00 rows=200000 width=0) (2 rows) pgbench=#
|
从查询计划中可以发现，物化视图已经起作用了。但是，使用等价的直接查询，不能自动转化为物化视图的查询，也就是没有执行计划自动替换功能，如下，
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| pgbench=# explain select count(*) from pgbench_accounts where bid < 3; QUERY PLAN ------------------------------------------------------------------------------- Aggregate (cost=47134.25..47134.26 rows=1 width=0) -> Seq Scan on pgbench_accounts (cost=0.00..46635.38 rows=199548 width=0) Filter: (bid < 3) (3 rows) pgbench=# select count(*) from pgbench_accounts where bid < 3; count -------- 200000 (1 row) pgbench=#
|
修改源表数据，刷新物化视图
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| pgbench=# insert into pgbench_accounts select aid+1600000,bid,abalance,filler from pgbench_accounts where bid = 1; INSERT 0 100000 pgbench=# select count(*) from pgbench_accounts where bid < 3; count 300000 (1 row) pgbench=# select count(*) from mv_pgbench_accounts; count 200000 (1 row) pgbench=#
|
再次插入bid=1的数据100000条到pgbench_accounts中(会话设置为自动commit)，postgres的视图没有自动刷新，需要手动刷新，如下，
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| pgbench=# refresh materialized view mv_pgbench_accounts with no data; REFRESH MATERIALIZED VIEW pgbench=# select count(*) from mv_pgbench_accounts; ERROR: materialized view "mv_pgbench_accounts" has not been populated HINT: Use the REFRESH MATERIALIZED VIEW command. pgbench=# refresh materialized view mv_pgbench_accounts with data; REFRESH MATERIALIZED VIEW pgbench=# select count(*) from mv_pgbench_accounts; count 300000 (1 row) pgbench=#
|
刷新的时候，如果使用了参数with no data，那么会将物化视图的数据重置为未发布的状态；使用with data参数，才会将数据写入。再次查询，就可以得到合理的结果。如果想做到插入数据后自动刷新，可以将刷新动作写在trigger里面。
物化视图上建立索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| pgbench=# create index mv_pgbench_accounts_bid_idx on mv_pgbench_accounts(bid); CREATE INDEX pgbench=# explain select count(*) from mv_pgbench_accounts where bid = 2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=7370.77..7370.78 rows=1 width=0) -> Index Only Scan using mv_pgbench_accounts_bid_idx on mv_pgbench_accounts (cost=0.42..6995.52 rows=150100 width=0) Index Cond: (bid = 2) (3 rows) pgbench=# select count(*) from mv_pgbench_accounts where bid = 2; count -------- 100000 (1 row) pgbench=#
|
物化视图上建立索引的方法和表上建立索引的方法几乎一样，使用方法也一样。从执行计划中可以看到，在物化视图上建立的索引可以被查询所用。
以上是对PostgreSQL的物化视图功能做的简单功能对比和使用方法演示。
本文参考了以下文章，, , , , ，和PostgreSQL9.3的官方文档, , 。