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 ``` 如果你能允許定期更新遠程數據到本地數據庫，會帶來可觀的性能優勢。