postgresql 的物化视图之一

系统管理员 2023-03-06 03:58 1阅读 0赞

os: centos 7.4.1708  
db: postgresql 10.11

oracle 数据里经常用到物化视图，想不到 postgresql 9.3 也加入了这么先进的功能。

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql10.x86_64                10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-contrib.x86_64        10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-debuginfo.x86_64      10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-devel.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-docs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-libs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-odbc.x86_64           12.00.0000-1PGDG.rhel7              @pgdg10  
    postgresql10-plperl.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-plpython.x86_64       10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-pltcl.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-server.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
    postgresql10-tcl.x86_64            2.4.0-1.rhel7                       @pgdg10  
    postgresql10-tcl-debuginfo.x86_64  2.3.1-1.rhel7                       @pgdg10  
    postgresql10-test.x86_64           10.11-2PGDG.rhel7                   @pgdg10 
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                  
    ----------------------------------------------------------------------------------------------------------
     PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# 初步使用 #

postgres=# create table tmp_t0(
    	id bigint primary key,
    	name varchar(100),
    	memo varchar(100)
    );
    
    postgres=# insert into tmp_t0
    select id,
           md5(id::varchar) as name,
           md5(md5(id::varchar) ) as memo
    from generate_series(1,1000000) as id; 
    
    postgres=# vacuum analyze tmp_t0;

postgres=# create table tmp_t1(
    	id bigint primary key,
    	name varchar(100),
    	memo varchar(100)
    );
    
    postgres=# insert into tmp_t1
    select id,
           md5(id::varchar) as name,
           md5(md5(id::varchar) ) as memo
    from generate_series(1,1000000) as id; 
    
    postgres=# vacuum analyze tmp_t1;

普通视图

postgres=# create or replace view v_tmp_t0_tmp_t1 as 
    select t0.id,t1.name,t1.memo
      from tmp_t0 t0,
           tmp_t1 t1
     where 1=1
       and t0.id=t1.id
       ;
    
    postgres=# explain analyze select count(1) from v_tmp_t0_tmp_t1;
                                                                                     QUERY PLAN                                    
                                                  
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Finalize Aggregate  (cost=55875.46..55875.47 rows=1 width=8) (actual time=896.620..896.621 rows=1 loops=1)
       ->  Gather  (cost=55875.24..55875.45 rows=2 width=8) (actual time=895.981..899.959 rows=3 loops=1)
             Workers Planned: 2
             Workers Launched: 2
             ->  Partial Aggregate  (cost=54875.24..54875.25 rows=1 width=8) (actual time=891.454..891.454 rows=1 loops=3)
                   ->  Merge Join  (cost=1.51..53833.58 rows=416667 width=0) (actual time=1.490..812.528 rows=333333 loops=3)
                         Merge Cond: (t1.id = t0.id)
                         ->  Parallel Index Only Scan using tmp_t1_pkey on tmp_t1 t1  (cost=0.42..20147.09 rows=416667 width=8) (actual time=0.042..100.212 rows=333333 loops=3)
                               Heap Fetches: 0
                         ->  Index Only Scan using tmp_t0_pkey on tmp_t0 t0  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.046..290.682 rows=999819 loops=3)
                               Heap Fetches: 0
     Planning time: 0.496 ms
     Execution time: 900.027 ms
    (13 rows)

物化视图

postgres=# CREATE MATERIALIZED VIEW mv_tmp_t0_tmp_t1 as 
    select t0.id,t1.name,t1.memo
      from tmp_t0 t0,
           tmp_t1 t1
     where 1=1
       and t0.id=t1.id
       ;
    
    postgres=# explain analyze select count(1) from mv_tmp_t0_tmp_t1;
                                                                         QUERY PLAN                                                
                          
    -----------------------------------------------------------------------------------------------------------------------------------------------------
     Finalize Aggregate  (cost=15514.83..15514.84 rows=1 width=8) (actual time=1284.117..1284.117 rows=1 loops=1)
       ->  Gather  (cost=15514.61..15514.82 rows=2 width=8) (actual time=1284.004..1288.327 rows=3 loops=1)
             Workers Planned: 2
             Workers Launched: 2
             ->  Partial Aggregate  (cost=14514.61..14514.62 rows=1 width=8) (actual time=1280.050..1280.051 rows=1 loops=3)
                   ->  Parallel Seq Scan on mv_tmp_t0_tmp_t1  (cost=0.00..14278.49 rows=94449 width=0) (actual time=0.049..1202.439
     rows=333333 loops=3)
     Planning time: 0.143 ms
     Execution time: 1288.403 ms
    (8 rows)

从执行计划已经可以看出不同了

查看定义是一样的

postgres=# delete from tmp_t0 where id between 500001 and 1000000;
    
    postgres=# select count(1) from v_tmp_t0_tmp_t1;
     count  
    --------
     500000
    (1 row)
    
    postgres=# select count(1) from mv_tmp_t0_tmp_t1;
      count  
    ---------
     1000000
    (1 row)

由上可以看出tmp\_t0数据发生变化后，普通视图可以看到变化，物化视图则不能，这是就需要刷新下物化视图。

postgres=# refresh materialized view concurrently mv_tmp_t0_tmp_t1;
    ERROR:  cannot refresh materialized view "public.mv_tmp_t0_tmp_t1" concurrently
    HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
    
    postgres=# create unique index idx_mv_tmp_t0_tmp_t1_id on mv_tmp_t0_tmp_t1 using btree(id);
    
    postgres=# refresh materialized view concurrently mv_tmp_t0_tmp_t1;
    
    postgres=# select count(1) from mv_tmp_t0_tmp_t1;
     count  
    --------
     500000
    (1 row)

目前还不能增量刷新

参考:  
http://groonga.org/  
https://github.com/Cyan4973/xxHash

https://github.com/pgroonga/pgroonga