在PostgreSQL中逐步刷新materalized视图

create table graph (
   xaxis Integer NOT NulL,value integer NOT NulL,);

CREATE MATERIAliZED VIEW graph_avg AS 
SELECT xaxis,AVG(value)
FROR_178_11845@ graph
GROUP BY xaxis

您始终可以实现自己的表作为“物化视图”.这是你在POSTGRes 9.3中以任何方式实现MATERIAliZED VIEW之前必须做的事情. @H_675_1@例如,您可以创建一个普通的VIEW：

create view graph_avg_vIEw AS 
SELECT xaxis,AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

@H_675_1@并将结果作为一个整体或在您需要重新开始时实现：

create table graph_avg AS
SELECT * FROM graph_avg_vIEw

@H_675_1@(或者直接使用SELECT语句,而不创建VIEW.)
然后,根据您的用例的未公开详细信息,您可以手动删除/更新/插入更改.

@H_675_1@具有数据修改CTE的基本DML语句,如下所示：

@H_675_1@假设没有其他人试图同时写入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;

@H_675_1@但这应该最有可能得到优化.

@H_675_1@基本配方：

@H_675_1@>将默认Now()的timestamp列添加到基表.我们称之为ts.

@H_675_1@>如果您有更新,请添加触发器以设置每次更改xaxis或值的更新的当前时间戳.

@H_675_1@>创建一个小表来记住最新快照的时间戳.我们称之为mv：

create table mv (
   tbl text PRIMary KEY,ts timestamp NOT NulL DEFAulT '-infinity'
); -- possibly more details

@H_675_1@>创建此部分多列索引：

CREATE INDEX graph_mv_latest ON graph (xaxis,value)
WHERE  ts >= '-infinity';

@H_675_1@>使用上一个快照的时间戳作为查询中的谓词,以使用完美的索引用法刷新快照.>在事务结束时,删除索引并使用事务时间戳重新创建它,替换索引谓词中的时间戳(最初为“-infinity”),您也将其保存到表中.一次交易中的一切.>请注意,部分索引可以很好地覆盖INSERT和update操作,但不能覆盖deletE.为了解决这个问题,您需要考虑整个表格.这完全取决于具体要求.