在PostgreSQL中逐步刷新materalized视图
是否可以在PostgreSQL中逐步刷新物化视图,即仅针对新的或已更改的数据?
考虑一下这张表&物化观点: CREATE TABLE graph ( xaxis integer NOT NULL,value integer NOT NULL,); CREATE MATERIALIZED VIEW graph_avg AS SELECT xaxis,AVG(value) FROM graph GROUP BY xaxis 定期将新值添加到图形或更新现有值.我想每隔几个小时刷新一次视图graph_avg,仅用于已更新的值.但是在PostgreSQL 9.3中,整个表都刷新了.这非常耗时.下一个版本9.4允许CONCURRENT更新,但它仍然刷新整个视图.拥有数百万行,这需要几分钟. 什么是跟踪更新和更新的好方法新值,只部分刷新视图?
您始终可以实现自己的表作为“物化视图”.这是你在Postgres 9.3中以任何方式实现MATERIALIZED VIEW之前必须做的事情.
例如,您可以创建一个普通的VIEW: CREATE VIEW graph_avg_view AS SELECT xaxis,AVG(value) AS avg_val FROM graph GROUP BY xaxis; 并将结果作为一个整体或在您需要重新开始时实现: CREATE TABLE graph_avg AS SELECT * FROM graph_avg_view (或者直接使用SELECT语句,而不创建VIEW.) 具有数据修改CTE的基本DML语句,如下所示: 假设没有其他人试图同时写入graph_avg(读取没问题): WITH del AS ( DELETE FROM graph_avg t WHERE NOT EXISTS (SELECT 1 FROM graph_avg_view v WHERE v.xaxis = v.xaxis); ),upd AS ( UPDATE graph_avg t FROM graph_avg_view v WHERE t.xaxis = v.xaxis AND t.avg_val <> v.avg_val ) INSERT INTO graph_avg t SELECT * FROM graph_avg_view v LEFT JOIN graph_avg t USING (xaxis) WHERE t.xaxis IS NULL; 但这应该最有可能得到优化. 基本配方: >将默认now()的timestamp列添加到基表.我们称之为ts. >如果您有更新,请添加触发器以设置每次更改xaxis或值的更新的当前时间戳. >创建一个小表来记住最新快照的时间戳.我们称之为mv: CREATE TABLE mv ( tbl text PRIMARY KEY,ts timestamp NOT NULL DEFAULT '-infinity' ); -- possibly more details >创建此部分多列索引: CREATE INDEX graph_mv_latest ON graph (xaxis,value) WHERE ts >= '-infinity'; >使用上一个快照的时间戳作为查询中的谓词,以使用完美的索引用法刷新快照.>在事务结束时,删除索引并使用事务时间戳重新创建它,替换索引谓词中的时间戳(最初为“-infinity”),您也将其保存到表中.一次交易中的一切.>请注意,部分索引可以很好地覆盖INSERT和UPDATE操作,但不能覆盖DELETE.为了解决这个问题,您需要考虑整个表格.这完全取决于具体要求. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |