materialized views and FDWs - Mailing list pgsql-hackers

In final testing and documentation today, it occurred to me to test
a materialized view with foreign data wrapper.  I picked the
file_fdw for convenience, but I think this should work as well with
any other FDW.  The idea is to create an MV which mirrors an FDW so
that it can be indexed and quickly accessed.  Timings below are all
fully cached to minimize caching effects.

test=# create extension file_fdw;
CREATE EXTENSION
test=# create server local_file foreign data wrapper file_fdw ;
CREATE SERVER
test=# create foreign table words (word text not null) server local_file options (filename
'/etc/dictionaries-common/words');
CREATE FOREIGN TABLE
test=# create materialized view wrd as select * from words;
SELECT 99171
test=# create unique index wrd_word on wrd (word);
CREATE INDEX
test=# create extension pg_trgm ;
CREATE EXTENSION
test=# create index wrd_trgm on wrd using gist (word gist_trgm_ops);
CREATE INDEX
test=# vacuum analyze wrd;
VACUUM
test=# select word from wrd order by word <-> 'caterpiler' limit 10;
     word     
---------------
 cater
 caterpillar
 Caterpillar
 caterpillars
 caterpillar's
 Caterpillar's
 caterer
 caterer's
 caters
 catered
(10 rows)

test=# explain analyze select word from words order by word <-> 'caterpiler' limit 10;
                                                         QUERY
PLAN                                                         

-----------------------------------------------------------------------------------------------------------------------------
 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
(8 rows)

test=# set enable_indexscan = off;
test=# explain analyze select word from wrd order by word <-> 'caterpiler' limit 10;
                                                      QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3883.69..3883.71 rows=10 width=9) (actual time=203.819..203.821 rows=10 loops=1)
   ->  Sort  (cost=3883.69..4131.61 rows=99171 width=9) (actual time=203.818..203.818 rows=10 loops=1)
         Sort Key: ((word <-> 'caterpiler'::text))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on wrd  (cost=0.00..1740.64 rows=99171 width=9) (actual time=0.029..186.749 rows=99171 loops=1)
 Total runtime: 203.851 ms
(6 rows)

test=# reset enable_indexscan;
test=# explain analyze select word from wrd order by word <-> 'caterpiler' limit 10;
                                                          QUERY
PLAN                                                         

------------------------------------------------------------------------------------------------------------------------------
 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
(4 rows)

Does this deserve specific treatment in the docs?  Where?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company