Re: materialized views and FDWs

Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> test=# explain analyze select word from words order by word <-> 'caterpiler' limit 10;
> Foreign Scan on words
>  Total runtime: 218.966 ms

> test=# explain analyze select word from wrd order by word <-> 'caterpiler' limit 10;
> Index Scan using wrd_trgm on wrd
>  Total runtime: 25.884 ms

I forgot to put the initial check for a valid word, where the
difference is much larger:

test=# explain analyze select count(*) from words where word = 'caterpiler';
                                                   QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------------
 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
(7 rows)

test=# explain analyze select count(*) from wrd where word = 'caterpiler';
                                                       QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------------
 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
(5 rows)

The question remains the same, though ... document this usage?

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