Re: [pg_trgm] Making similarity(?, ?) < ? use an index

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Friday, June 3, 2016, Greg Navis <contact@xxxxxxxxxxxxx> 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.