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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
