PG 物化视图

一、创建物化视图

create materialized view view_name as query_sql with [no] data;

with no data: 创建物化视图后，视图不可查询(因为无数据)
with data: 创建物化视图后，视图可查询数据

二、刷新物化视图的数据

(1)、即将数据加载到物化视图中。

refresh materialized view view_name;

(2)、刷新物化视图

刷新物化视图 的数据时，或锁定整张表(在大数据量的情况下比较久，会导致其它查询语句无法执行)。为了避免此种情
况，可使用 concurrently 选项。

refresh materialized view concurrently view_name;

注：使用 concurrently 选项，PostgreSQL将创建物化视图的临时更新版本，比较更新版本和原视图两个版本，并仅对差异部分
执行 insert 和 update 。
这样就可以在更新时对物化视图进行查询。

使用 concurrently 选项的一个要求是：物化视图必须具有 unique 索引。
注意 :CONCURRENTLY 选项只能在 PostgreSQL 9.4 及以上版本中使用。

create unique index index_name on view_name (列名1,列名2...);

三、删除物化视图

drop materialized view [if exists] view_name;

四、案例：

创建物化视图：

select * from payment;
select * from rental;
select * from inventory;
select * from film;
select * from film_category;
select * from category; 

一、创建物化视图：

create materialized view rental_by_category as 
    select 
        cy.name,
        sum(pt.amount) as sum_amount
    from payment pt 
        inner join rental rl on pt.rental_id=rl.rental_id
        inner join inventory iy on rl.inventory_id=iy.inventory_id
        inner join film fm on iy.film_id=fm.film_id
        inner join film_category fy on fm.film_id=fy.film_id
        inner join category cy on fy.category_id=cy.category_id
    group by cy.name
    order by sum_amount
with no data;

select * from rental_by_category;

结果：因为创建物化视图使用了with no data因此查询时提示报错

错误: 物化视图 "rental_by_category"未被初始化

二、刷新物化视图数据：

refresh materialized view rental_by_category;

select * from rental_by_category;

三、使用concurrently选项刷新物化视图：

前提必须先为物化视图创建唯一索引

create unique index inx_rental_by_category on rental_by_category (name);

refresh materialized view concurrently rental_by_category;

select * from rental_by_category;