From: | Greg Navis <contact(at)gregnavis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | [pg_trgm] Making similarity(?, ?) < ? use an index |
Date: | 2016-06-03 10:35:38 |
Message-ID: | CAA6WWt834QH-f20okyv6repnHm5oqe-16z0GAdiH4RzPBf9t+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
Free, biweekly scalability newsletter for SaaS CEOs
<http://www.gregnavis.com/newsletter/>
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Navis | 2016-06-03 10:43:02 | [pg_trgm] Per-query set_limit() |
Previous Message | Nick Cleaton | 2016-06-03 07:36:16 | Re: Slave claims requested WAL segment already removed - but it wasn't |