Kevin Grittner <kgrittn@ymail.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
