From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | Giuseppe Broccolo <giuseppe(dot)broccolo(at)2ndquadrant(dot)it> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Question about trigram GIST index |
Date: | 2014-12-18 18:08:38 |
Message-ID: | CAAXGW-yWdFTFSLquOH2qNY4BpL95VGKPAObK3ECdnT2i-zMfzw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I'm pretty sure '%John%' uses the index.
explain analyze verbose SELECT name FROM wai_users WHERE lower(name) LIKE
'%john%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.wai_users (cost=53.45..1345.46 rows=900
width=14) (actual time=18.474..32.093 rows=1596 loops=1)
Output: name
Recheck Cond: (lower((wai_users.name)::text) ~~ '%john%'::text)
-> Bitmap Index Scan on idx_user_name (cost=0.00..53.41 rows=900
width=0) (actual time=18.227..18.227 rows=1596 loops=1)
Index Cond: (lower((wai_users.name)::text) ~~ '%john%'::text)
Total runtime: 33.662 ms
(6 rows)
On Thu, Dec 18, 2014 at 10:00 AM, Giuseppe Broccolo <
giuseppe(dot)broccolo(at)2ndquadrant(dot)it> wrote:
>
> I'm not sure about the '%' operator, but I'm sure that the GIST index will
> never be used in the
>
> SELECT * FROM users WHERE lower(name) LIKE '%john%';
>
> query; it is used for left or right anchored search, such as 'john%' or
> '%john'.
>
> Giuseppe.
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-12-18 18:33:33 | Re: Question about trigram GIST index |
Previous Message | Giuseppe Broccolo | 2014-12-18 18:00:00 | Re: Question about trigram GIST index |