物化视图

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 章 38. 规则系统 | 下一页

# 38.3. 物化视图

PostgreSQL里的物化视图像视图那样使用规则系统， 但是用类表的形式保存结果。

    CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

和：

    CREATE TABLE mymatview AS SELECT * FROM mytab;

之间最主要的区别是物化视图不能随后直接被更新，并且创建物化视图的查询就像视图的查询存储那样存储， 所以新数据可以用下面命令产生：

    REFRESH MATERIALIZED VIEW mymatview;

PostgreSQL系统目录中有关物化视图的信息和表或视图的信息一样。 所以对于解析器，物化视图是一个关系，就像一个表或一个视图。当在查询中引用一个物化视图时， 数据直接从物化视图返回，就像从一个表返回；规则只是用来填充物化视图。

当访问存储在物化视图中的数据时，通常比直接访问底层表或通过一个视图更快， 数据并不总是当前的；然而有时不需要当前数据。考虑一个记录销售的表：

    CREATE TABLE invoice (
        invoice_no    integer        PRIMARY KEY,
        seller_no     integer,       -- 销售人员的ID
        invoice_date  date,          -- 销售日期
        invoice_amt   numeric(13,2)  -- 销售数量
    );

如果人们希望能够快速的图形化历史销售数据，他们可能想要汇总， 可能不关心当前未完成的数据：

    CREATE MATERIALIZED VIEW sales_summary AS
      SELECT
          seller_no,
          invoice_date,
          sum(invoice_amt)::numeric(13,2) as sales_amt
        FROM invoice
        WHERE invoice_date < CURRENT_DATE
        GROUP BY
          seller_no,
          invoice_date
        ORDER BY
          seller_no,
          invoice_date;

    CREATE UNIQUE INDEX sales_summary_seller
      ON sales_summary (seller_no, invoice_date);

物化视图可以用来在为销售人员创建的控制面板上显示图形。 可以使用下面的SQL语句在每天晚上更新统计数据：

    REFRESH MATERIALIZED VIEW sales_summary;

物化视图的另一个用处是允许对远程系统中的数据快速访问，通过一个外部数据封装器。 下面是一个简单的使用`file_fdw`的例子，有计时， 但是因为这是使用的在本地系统的缓存，访问远程系统通常比这里显示的性能差异更大。 请注意，我们也利用了把索引放在物化视图上的能力，在这里`file_fdw` 不支持索引；这个优势不会应用于其他类型的外部数据访问。

Setup:

    CREATE EXTENSION file_fdw;
    CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
    CREATE FOREIGN TABLE words (word text NOT NULL)
      SERVER local_file
      OPTIONS (filename '/usr/share/dict/words');
    CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
    CREATE UNIQUE INDEX wrd_word ON wrd (word);
    CREATE EXTENSION pg_trgm;
    CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
    VACUUM ANALYZE wrd;

现在让我们拼写检查一个单词。直接使用`file_fdw`：

    SELECT count(*) FROM words WHERE word = 'caterpiler';

     count
    -------
         0
    (1 row)

带有`EXPLAIN ANALYZE`，我们看到：

     Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
       ->  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
             Filter: (word = 'caterpiler'::text)
             Rows Removed by Filter: 479829
             Foreign File: /usr/share/dict/words
             Foreign File Size: 4953699

     Planning time: 0.118 ms
     Execution time: 188.273 ms

如果使用物化视图，查询更快速：

     Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
       ->  Index Only Scan using wrd_word on wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
             Index Cond: (word = 'caterpiler'::text)
             Heap Fetches: 0

     Planning time: 0.164 ms
     Execution time: 0.117 ms

无论哪种方式，这个词的拼写是错误的，所以我们看看我们想要的。还是使用`file_fdw`：

    SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

         word
    ---------------
     cater
     caterpillar
     Caterpillar
     caterpillars
     caterpillar's
     Caterpillar's
     caterer
     caterer's
     caters
     catered
    (10 rows)

     Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
       ->  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
             Sort Key: ((word <-> 'caterpiler'::text))
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
                   Foreign File: /usr/share/dict/words
                   Foreign File Size: 4953699

     Planning time: 0.128 ms
     Execution time: 1431.679 ms

使用物化视图：

     Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
       ->  Index Scan using wrd_trgm on wrd  (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
             Order By: (word <-> 'caterpiler'::text)

     Planning time: 0.196 ms
     Execution time: 198.640 ms

如果你能允许定期更新远程数据到本地数据库，会带来可观的性能优势。

上一页 | 起始页 | 下一页
视图和规则系统 | 上一级 | 在 `INSERT`, `UPDATE`, 和 `DELETE`上的规则