PostgreSQL 9.6.0 锟街诧拷
锟斤拷一页锟斤拷一锟斤拷锟斤拷 39. 锟斤拷锟斤拷系统锟斤拷一页

39.3. 锟斤化锟斤拷图

PostgreSQL锟叫碉拷锟斤化锟斤拷图锟斤拷锟斤拷图一锟斤拷使锟斤拷锟剿癸拷锟斤拷系统锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟绞斤拷锟斤拷锟斤拷私锟斤拷锟斤拷锟斤拷锟斤化锟斤拷图锟斤拷 

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

锟斤拷锟斤拷图锟斤拷 

CREATE TABLE mymatview AS SELECT * FROM mytab;

之锟斤拷锟斤拷锟揭拷锟斤拷锟斤拷锟斤拷锘拷锟酵硷拷锟斤拷锟街憋拷颖锟斤拷锟斤拷拢锟斤拷锟斤拷锟斤拷锟斤拷诖锟斤拷锟斤拷锘拷锟酵硷拷牟锟窖拷拇娲拷锟绞斤拷锟斤拷锟酵硷拷锟窖拷拇娲拷锟绞斤拷锟饺拷锟酵拷锟斤拷锟斤拷要为锟斤化锟斤拷图锟斤拷锟斤拷锟斤拷锟绞碉拷锟斤拷锟捷ｏ拷 

REFRESH MATERIALIZED VIEW mymatview;

The information about a materialized view in the 锟叫癸拷一锟斤拷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锟斤拷支锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷也使锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷锟斤化锟斤拷图锟较凤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡匡拷锟杰诧拷锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷獠匡拷锟斤拷莘锟斤拷省锟�

锟斤拷锟斤拷锟斤拷 

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锟较的癸拷锟斤拷