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

Поиск
Список
Период
Сортировка
От Greg Navis
Тема [pg_trgm] Making similarity(?, ?) < ? use an index
Дата
Msg-id CAA6WWt834QH-f20okyv6repnHm5oqe-16z0GAdiH4RzPBf9t+A@mail.gmail.com
обсуждение исходный текст
Ответы Re: [pg_trgm] Making similarity(?, ?) < ? use an index  (Artur Zakirov <a.zakirov@postgrespro.ru>)
Re: [pg_trgm] Making similarity(?, ?) < ? use an index  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
Дерево обсуждения
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?

Best regards
--
Greg Navis
I help tech companies to scale Heroku-hosted Rails apps.

В списке pgsql-general по дате отправления:

Предыдущее
От: Nick Cleaton
Дата:
Сообщение: Re: Slave claims requested WAL segment already removed - but it wasn't
Следующее
От: Greg Navis
Дата:
Сообщение: [pg_trgm] Per-query set_limit()