38.3. 物化视图 · PostgreSQL 中文文档 9.3

# 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`的例子，有计时，但是因为这是使用的在本地系统的缓存，外部数据封装器到远程系统的性能可能更大。 ``` 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 '/etc/dictionaries-common/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) ``` 计划是： ``` Aggregate (cost=4125.19..4125.20 rows=1 width=0) (actual time=26.013..26.014 rows=1 loops=1) -> Foreign Scan on words (cost=0.00..4124.70 rows=196 width=0) (actual time=26.011..26.011 rows=0 loops=1) Filter: (word = 'caterpiler'::text) Rows Removed by Filter: 99171 Foreign File: /etc/dictionaries-common/words Foreign File Size: 938848 Total runtime: 26.081 ms ``` 如果使用物化视图，查询更快速： ``` Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.074..0.074 rows=1 loops=1) -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.071..0.071 rows=0 loops=1) Index Cond: (word = 'caterpiler'::text) Heap Fetches: 0 Total runtime: 0.119 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=2195.70..2195.72 rows=10 width=32) (actual time=218.904..218.906 rows=10 loops=1) -> Sort (cost=2195.70..2237.61 rows=16765 width=32) (actual time=218.902..218.904 rows=10 loops=1) Sort Key: ((word <-> 'caterpiler'::text)) Sort Method: top-N heapsort Memory: 25kB -> Foreign Scan on words (cost=0.00..1833.41 rows=16765 width=32) (actual time=0.046..200.965 rows=99171 loops=1) Foreign File: /etc/dictionaries-common/words Foreign File Size: 938848 Total runtime: 218.966 ms ``` 使用物化视图： ``` Limit (cost=0.28..1.02 rows=10 width=9) (actual time=24.916..25.079 rows=10 loops=1) -> Index Scan using wrd_trgm on wrd (cost=0.28..7383.70 rows=99171 width=9) (actual time=24.914..25.076 rows=10 loops=1) Order By: (word <-> 'caterpiler'::text) Total runtime: 25.884 ms ``` 如果你能允许定期更新远程数据到本地数据库，会带来可观的性能优势。