物化视图相关操作

• 建立物化视图。

postgres=# create MATERIALIZED VIEW tbase_count as select count(1) as num from tbase;
SELECT 1
postgres=# select * from tbase_count;
  num  
-------
 10000
(1 row)

• \d查物化视图结构。

postgres=# \d tbase_count
               Materialized view"public.tbase_count"
 Column | Type  | Modifiers 
--------+--------+-----------
 num    |bigint | 

• \d+查看物化视图结构（包含注释），包含创建物化视图的sql语句。

postgres=# \d+ tbase_count
               Materialized view"public.tbase_count"
 Column | Type  | Modifiers | Storage |Stats target | Description 
--------+--------+-----------+---------+--------------+-------------
 num    |bigint |           | plain   |              | 
View definition:
 SELECT count(1) AS num
  FROM tbase;

• \dm查看视图列表。

postgres=# \dm
               List of relations
 Schema |   Name     |       Type        | Owner 
--------+-------------+-------------------+-------
 pgxc   |tbase_sum   | materialized view | tbase
 public | tbase_count | materialized view |tbase
(2 rows)

• \dm+查看物化视图列表详细信息（包含注释），占用空间大小。

postgres=# \dm+
               List of relations
 Schema |   Name     |       Type        | Owner |    Size   |  Description  
--------+-------------+-------------------+-------+------------+---------------
 pgxc   |tbase_sum | materialized view | tbase  |8192 bytes | 
 public | tbase_count | materialized view |tbase  | 8192 bytes | tbase总记录数
(2 rows)

• \dm+显示某个模式下的所有物化视图。

postgres=# \dm+ pgxc.*
               List of relations
 Schema |   Name     |       Type        | Owner |    Size   |  Description  
--------+-----------+-------------------+-------+------------+-------------
 pgxc   |tbase_sum | materialized view | tbase  |8192 bytes | 
(1 row)

• \dm+视图名显示某个物化视图的详细信息。

postgres=# \dm+ tbase_count
               List of relations
 Schema |   Name     |       Type        | Owner |    Size   |  Description  
--------+-------------+-------------------+-------+------------+---------------
 public | tbase_count | materialized view |tbase  | 8192 bytes | tbase总记录数
(1 row)

• \dm+通配符列出适配的物化视图。

postgres=# \dm t*
               List of relations
 Schema |   Name     |       Type        | Owner 
--------+-------------+-------------------+-------
 pgxc   |tbase_sum   | materialized view | tbase
 public | tbase_count | materialized view |tbase
(2 rows)

postgres=# \dm tbase_c*
               List of relations
 Schema |   Name     |       Type        | Owner 
--------+-------------+-------------------+-------
 public | tbase_count | materialized view |tbase
(1 row)