物化视图
PalopgSQL中的物化视图像视图一样使用了规则系统，但是以一种类表的形式保留了结果。在物化视图：
1CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;和视图：
1CREATE TABLE mymatview AS SELECT * FROM mytab;之间的主要区别是物化视图不能直接被更新，并且用于创建物化视图的查询的存储方式和视图查询的存储方式完全相同，因此要为物化视图生成新鲜的数据：
1REFRESH MATERIALIZED VIEW mymatview;有关一个PalopgSQL系统目录中的物化视图的信息和一个表或视图的信息完全相同。因此对于解析器，一个物化视图就是一个关系，就像一个表或一个视图。当一个物化视图被一个查询引用时，数据直接从物化视图中返回，如同表一样；规则只被用来填充物化视图。
虽然对物化视图中存储的数据的访问常常要快于直接访问底层表或通过一个视图访问，但是数据并不总是最新的；但是某些时候并不需要当前数据。考虑一个记录销售情况的表：
1CREATE TABLE invoice (
2 invoice_no integer PRIMARY KEY,
3 seller_no integer, -- 销售员的 ID
4 invoice_date date, -- 销售日期
5 invoice_amt numeric(13,2) -- 销售量
6);如果人们想快速绘制历史销售数据，他们可能希望汇总，并且他们可能并不关心当前日期的不完整数据：
1CREATE MATERIALIZED VIEW sales_summary AS
2 SELECT
3 seller_no,
4 invoice_date,
5 sum(invoice_amt)::numeric(13,2) as sales_amt
6 FROM invoice
7 WHERE invoice_date < CURRENT_DATE
8 GROUP BY
9 seller_no,
10 invoice_date
11 ORDER BY
12 seller_no,
13 invoice_date;
14
15CREATE UNIQUE INDEX sales_summary_seller
16 ON sales_summary (seller_no, invoice_date);这个物化视图可能对在为销售员创建的控制面板上显示一个图表非常有用。可以用一个计划任务在每晚使用这个 SQL 语句更新该统计信息：
1REFRESH MATERIALIZED VIEW sales_summary;物化视图的另一种使用是允许通过一个外部数据封装器对来自一个远程系统的数据进行更快的访问。下面有一个使用file_fdw的简单例子，但是由于本地系 统上可以使用高速缓存，因此比起访问一个远程系统的性能差异可能会比这里所展示的更大。注意鉴于 file_fdw不支持索引，我们也使用这种能力来在物化视图上放置索引。这种优势可能不适用于其他种类的外部数据访问。
建立：
1CREATE EXTENSION file_fdw;
2CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
3CREATE FOREIGN TABLE words (word text NOT NULL)
4 SERVER local_file
5 OPTIONS (filename '/usr/share/dict/words');
6CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
7CREATE UNIQUE INDEX wrd_word ON wrd (word);
8CREATE EXTENSION sd_trgm;
9CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
10VACUUM ANALYZE wrd;现在让我们对一个词进行拼写检查。直接使用file_fdw：
1SELECT count(*) FROM words WHERE word = 'caterpiler';
2
3 count
4-------
5 0
6(1 row)通过EXPLAIN ANALYZE，我们可以看到：
1 Aggregate (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
2 -> Foreign Scan on words (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
3 Filter: (word = 'caterpiler'::text)
4 Rows Removed by Filter: 479829
5 Foreign File: /usr/share/dict/words
6 Foreign File Size: 4953699
7 Planning time: 0.118 ms
8 Execution time: 188.273 ms如果使用物化视图，该查询会快很多：
1 Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
2 -> 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)
3 Index Cond: (word = 'caterpiler'::text)
4 Heap Fetches: 0
5 Planning time: 0.164 ms
6 Execution time: 0.117 ms不管哪种方式，单词都是被拼错的，因此让我们看看什么是我们可能想要的。再次使用file_fdw：
1SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
2
3 word
4---------------
5 cater
6 caterpillar
7 Caterpillar
8 caterpillars
9 caterpillar's
10 Caterpillar's
11 caterer
12 caterer's
13 caters
14 catered
15(10 rows)1 Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
2 -> Sort (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
3 Sort Key: ((word <-> 'caterpiler'::text))
4 Sort Method: top-N heapsort Memory: 25kB
5 -> Foreign Scan on words (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
6 Foreign File: /usr/share/dict/words
7 Foreign File Size: 4953699
8 Planning time: 0.128 ms
9 Execution time: 1431.679 ms使用物化视图：
1 Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
2 -> 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)
3 Order By: (word <-> 'caterpiler'::text)
4 Planning time: 0.196 ms
5 Execution time: 198.640 ms如果你能够忍受定期把远程数据更新到本地数据库，其性能收益可能是巨大的。
评价此篇文章