On Friday, June 3, 2016, Greg Navis <cont...@gregnavis.com> wrote: > Hey! > > I'm playing with pg_trgm. It seems that `lhs % rhs` is _almost_ equivalent > to `similarity(lhs, rhs) < show_limit()`. The difference that I noticed is > that `%` uses a GIN index while `similarity` does not. > > ``` > grn=# \d restaurants > Table "public.restaurants" > Column | Type | Modifiers > --------+------------------------+----------- > city | character varying(255) | not null > Indexes: > "restaurants_city_trgm_idx" gin (city gin_trgm_ops) > > grn=# SELECT COUNT(*) FROM restaurants; > count > -------- > 515475 > (1 row) > > Time: 45.964 ms > grn=# EXPLAIN ANALYZE SELECT * FROM restaurants WHERE similarity(city, > 'warsw') > show_limit(); > QUERY PLAN > > > -------------------------------------------------------------------------------------------------------------------- > Seq Scan on restaurants (cost=0.00..11692.81 rows=171825 width=10) > (actual time=16.436..665.062 rows=360 loops=1) > Filter: (similarity((city)::text, 'warsw'::text) > show_limit()) > Rows Removed by Filter: 515115 > Planning time: 0.139 ms > Execution time: 665.105 ms > (5 rows) > > Time: 665.758 ms > ``` > > My question is: is it possible to make `similarity` use the index? If not, > is there a way to speed up the query above? > > No. Indexing is tied to operators.
I don't know which search terms would work best but I gave this same answer less than a week ago. List searching before asking is appreciated. David J.
