From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Greg Navis <contact(at)gregnavis(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [pg_trgm] Making similarity(?, ?) < ? use an index |
Date: | 2016-06-03 12:05:39 |
Message-ID: | CAKFQuwaviM1dsMVp8wBJYAhs18+TgkBF-xkJiCv1O7VHHeADcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Friday, June 3, 2016, Greg Navis <contact(at)gregnavis(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2016-06-03 15:34:36 | Re: psql remote shell command |
Previous Message | Artur Zakirov | 2016-06-03 11:58:24 | Re: [pg_trgm] Making similarity(?, ?) < ? use an index |