Postgres Pro
Downloads
Home   >   mailing lists

Re: Question about trigram GIST index - Mailing list pgsql-performance

From Robert DiFalco
Subject Re: Question about trigram GIST index
Date
Msg-id CAAXGW-yWdFTFSLquOH2qNY4BpL95VGKPAObK3ECdnT2i-zMfzw@mail.gmail.com
Whole thread Raw
In response to Re: Question about trigram GIST index  (Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>)
List pgsql-performance
Tree view
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.broccolo@2ndquadrant.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.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

pgsql-performance by date:

Previous
From: Giuseppe Broccolo
Date:
Subject: Re: Question about trigram GIST index
Next
From: Jeff Janes
Date:
Subject: Re: Question about trigram GIST index