PG中的物化视图
Tags： GreenPlumPGPostgreSQL物化视图
简介
物化视图（也叫实体化视图）是PostgreSQL9.3版本才支持的一个功能，物化视图的使用规则和视图是一样的，和视图不同的是，物化视图将结果集持久化在表中；
一个物化视图对应一个SQL语句，查询时去对应的结果集表查询（跟视图查询方式有些区别）；
创建物化视图语句：
1 | CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab; |
物化视图所支持功能
物化视图无法对表进行插入更新删除操作;
物化视图有系统表字段信息；
支持创建空间索引和属性字段索引，也可以在物化视图上创建索引（GIST、BRIN、SP-GiST、以及字段索引等）；
刷新物化视图
物化视图更新1）：全量刷新，PostgreSQL9.3版本只有该刷新规则;
1 | REFRESH MATERIALIZED VIEW mymatview |
物化视图更新2）：增量刷新，PostgreSQL9.4版本增加的一个刷新规则;
1 | REFRESH MATERIALIZED VIEW CONCURRENTLY [ MATERIALIZED view table name] |
全量更新： 直接去基础表里面查询数据，刷新过程中会对该物化视图的所有的select操作阻塞，但刷新效率快。
增量刷新： 刷新要具备有唯一字段索引，将基础表查询出来的数据和现在物化视图表进行对比，填充差量数据，但刷新过程中不会对该物化视图的select操作进行阻塞，但刷新比全量更新慢。
物化视图的另外一种用法就是允许通过外部数据包裹器快速的访问来自远程系统的数据。下面是一个使用 file_fdw 的简单示例，其中包含时间，但由于这是在本地系统上使用缓存，因此与访问远程系统相比，性能差异通常会比此处显示的要大。 但是外部数据表file_fdw是不支持索引的，这个优势也不太适用于其他类型的外部表数据访问。所以可以基于外部数据表建立物化视图创建索引提高查询能力。
查询
1 2 3 | SELECT * FROM pg_matviews; \dm[S+] [PATTERN] list materialized views |
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | CREATE TABLE sales ( order_id SERIAL PRIMARY KEY, order_date DATE, product_id INT, quantity INT, price DECIMAL(10, 2) ); INSERT INTO sales (order_date, product_id, quantity, price) VALUES ('2023-05-01', 1, 10, 15.99), ('2023-05-02', 2, 5, 10.99), ('2023-05-03', 1, 8, 14.99), ('2023-05-04', 3, 12, 19.99), ('2023-05-05', 2, 6, 11.99); CREATE MATERIALIZED VIEW sales_summary_mv AS SELECT date_trunc('month', order_date) AS month, product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_sales FROM sales GROUP BY month, product_id; REFRESH MATERIALIZED VIEW sales_summary_mv; REFRESH MATERIALIZED VIEW sales_summary_mv with data; SELECT * FROM sales_summary_mv; -- 创建函数 CREATE OR REPLACE FUNCTION "public"."refresh_sales_summary_mv"() RETURNS "pg_catalog"."varchar" AS $BODY$BEGIN -- 刷新月度医院规则视图 refresh materialized view sales_summary_mv with data; RETURN 'success'; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ; -- 可以执行函数直接刷新 select refresh_sales_summary_mv(); |
在这个示例中，我们创建了一个名为 sales 的表，包含了以下列：
order_id：序列号，作为主键。order_date：订单日期，使用DATE数据类型。product_id：产品ID，使用INT数据类型。quantity：销售数量，使用INT数据类型。price：销售价格，使用DECIMAL(10, 2)数据类型，表示最多10位数，其中包括2位小数。
请注意，上述示例仅提供了一个简单的表结构，根据您的实际需求，您可能需要根据业务需求定义更多的列或添加其他约束和索引。
您可以使用上述创建语句在Greenplum中创建一个名为 sales 的表，并在其中存储销售数据。
然后，我们创建了一个名为 sales_summary_mv 的物化视图。它基于 sales 表，根据订单日期（order_date）按月进行分组，并计算每个月每个产品的总销量（total_quantity）和总销售额（total_sales）。
通过定期刷新物化视图，我们可以在查询物化视图时直接从预先计算的结果中获取数据，从而提高查询性能。
定时刷新物化视图
如果您希望自动定时刷新物化视图，您可以借助外部调度工具（如Cron）或编写一个定时任务来执行刷新操作。
下面是一个使用Cron调度刷新物化视图的示例：
- 首先，创建一个脚本文件（例如
refresh_mv.sh），其中包含刷新物化视图的SQL语句：
1 2 | #!/bin/bash psql -d your_database_name -c "REFRESH MATERIALIZED VIEW sales_summary_mv;" |
在脚本中，将 your_database_name 替换为您的Greenplum数据库的名称，sales_summary_mv 替换为要刷新的物化视图的名称。
使用文本编辑器（如Vi或Nano）编辑Cron任务：
1 | crontab -e |
在Cron任务中添加一个定时任务，例如，每天凌晨3点刷新物化视图：
1 | 0 3 * * * /path/to/refresh_mv.sh |
将 /path/to/refresh_mv.sh 替换为实际的脚本文件路径。
保存并关闭文本编辑器，Cron将在每天凌晨3点运行脚本文件，从而自动刷新物化视图。
请注意，上述示例仅提供了一个基本的定时刷新方案。根据您的需求和环境，您可能需要进一步调整和配置定时任务以满足您的要求。
视图和物化视图区别
视图
结果未持久化在服务器
支持触发器和rule创建
不支持索引
通过定义触发器或规则可实现视图更新
每次的数据查询都是最新的
物化视图
结果集持久化在服务器
不支持触发器和rule创建
支持索引
对外部表创建物化视图，提高查询速度
数据更新只能采用refresh操作
总结
1、GreenPlum和PG一样，支持物化视图，相关语法大概一致。
参考
https://blog.csdn.net/weixin_45706122/article/details/125596690
https://www.bookstack.cn/read/Greenplum-6.23-en/0acdcc4689d3f91d.md
