物化视图相关操作
最近更新时间: 2024-06-12 15:06:00
建立物化视图。
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)