Re: index on ILIKE/LIKE - PostgreSQL 9.2

På torsdag 12. mai 2016 kl. 09:57:58, skrev Andreas Joseph Krogh <
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>>:
På torsdag 12. mai 2016 kl. 02:30:33, skrev Lucas Possamai <
drum(dot)lucas(at)gmail(dot)com <mailto:drum(dot)lucas(at)gmail(dot)com>>:
Hi there!  
I've got a simple but slow query:
 
 SELECT DISTINCT title  
FROM ja_jobs WHERE title ILIKE '%RYAN WER%'
and clientid = 31239  AND time_job > 1457826264
order BY title
limit 10
 
Explain analyze: 
 
Limit  (cost=5946.40..5946.41 rows=1 width=19) (actual time=2746.759..2746.772
rows=1 loops=1)
  ->  Unique  (cost=5946.40..5946.41 rows=1 width=19) (actual
time=2746.753..2746.763 rows=1 loops=1)
        ->  Sort  (cost=5946.40..5946.41 rows=1 width=19) (actual
time=2746.750..2746.754 rows=4 loops=1)
              Sort Key: "title"
              Sort Method: quicksort  Memory: 25kB
              ->  Bitmap Heap Scan on "ja_jobs"  (cost=49.02..5946.39 rows=1
width=19) (actual time=576.275..2746.609 rows=4 loops=1)
                    Recheck Cond: (("clientid" = 31239) AND ("time_job" >
1457826264))
                    Filter: (("title")::"text" ~~* '%RYAN WER%'::"text")
                    Rows Removed by Filter: 791
                    ->  Bitmap Index Scan on "ix_jobs_client_times"
 (cost=0.00..49.02 rows=1546 width=0) (actual time=100.870..100.870 rows=795
loops=1)
                          Index Cond: (("clientid" = 31239) AND ("time_job" >
1457826264))
Total runtime: 2746.879 ms  
Then, I created a trgm index:
 
CREATE INDEX ix_ja_jobs_trgm_gin ON public.ja_jobs USING gin (title
gin_trgm_ops);
 
Explain analyze after the index: (Yes, I ran the analyze)
 
Limit  (cost=389.91..389.91 rows=1 width=20) (actual time=3720.511..3720.511
rows=0 loops=1)
  ->  Unique  (cost=389.91..389.91 rows=1 width=20) (actual
time=3720.507..3720.507 rows=0 loops=1)
        ->  Sort  (cost=389.91..389.91 rows=1 width=20) (actual
time=3720.505..3720.505 rows=0 loops=1)
              Sort Key: "title"
              Sort Method: quicksort  Memory: 25kB
              ->  Bitmap Heap Scan on "ja_jobs"  (cost=385.88..389.90 rows=1
width=20) (actual time=3720.497..3720.497 rows=0 loops=1)
                    Recheck Cond: (("clientid" = 31239) AND ("time_job" >
1457826264) AND (("title")::"text" ~~ '%RYAN WER%'::"text"))
                    Rows Removed by Index Recheck: 4
                    ->  BitmapAnd  (cost=385.88..385.88 rows=1 width=0)
(actual time=3720.469..3720.469 rows=0 loops=1)
                          ->  Bitmap Index Scan on "ix_jobs_client_times"
 (cost=0.00..50.00 rows=1644 width=0) (actual time=0.142..0.142 rows=795
loops=1)
                                Index Cond: (("clientid" = 31239) AND
("time_job" > 1457826264))
                          ->  Bitmap Index Scan on "ix_ja_jobs_trgm_gin"
 (cost=0.00..335.63 rows=484 width=0) (actual time=3720.213..3720.213 rows=32
loops=1)
                                Index Cond: (("title")::"text" ~~ '%RYAN WER
%'::"text")
Total runtime: 3720.653 ms   
 
so.. the query is still slow.. 
Do you guys  know what can be done ? related to the ILIKE?

 
cheers
Lucas

 
It uses available indexes, but that isn't good enough.
 
Try including clientid in the index, using the btree_gin extension:
 
CREATE INDEX ix_ja_jobs_trgm_clientid_gin ON public.ja_jobs USING gin (title
gin_trgm_ops, clientid);
 
Note that if clientid is a bigint you have to cast the value to bigint for
btree_gin to use it (note that this isn't necessary if you use a prepared
statement):
 
SELECT DISTINCT title  
        FROM ja_jobs WHERE title ILIKE '%RYAN WER%'
        and clientid = 31239::bigint  AND time_job > 1457826264
        order BY title
        limit 10

 
Also note that the index cannot ant won't be used for sorting. A bitmap-AND is
also inevitable because GIN-indexes cannot be used for the '>' operator, so PG
uses the ix_jobs_client_times btree-index and bigmap-ANDs the result.
 
Can you post you complete schema?
 
I created this test:
 
create table ja_jobs(id bigserial primary key, title varchar not null,
clientid bigint not null, time_job bigint not null);
CREATE INDEX ix_ja_jobs_trgm_clientid_gin ON public.ja_jobs USING gin (title
gin_trgm_ops, clientid);

--- insert some test-data
 
As you see, this uses the index (when casting clientid to bigint):

 
andreak=# explain analyze SELECT DISTINCT title  
FROM ja_jobs WHERE title ILIKE '%ras du%'
and clientid = 12::bigint AND time_job > 257826264
order BY title
limit 10;
                                                                      QUERY
PLAN                                                                       

-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=8.43..8.44 rows=1 width=32) (actual time=0.033..0.034 rows=1
loops=1)
   ->  Unique  (cost=8.43..8.44 rows=1 width=32) (actual time=0.032..0.032
rows=1 loops=1)
         ->  Sort  (cost=8.43..8.43 rows=1 width=32) (actual
time=0.032..0.032 rows=1 loops=1)
               Sort Key: title
               Sort Method: quicksort  Memory: 25kB
               ->  Bitmap Heap Scan on ja_jobs  (cost=7.20..8.42 rows=1
width=32) (actual time=0.025..0.025 rows=1 loops=1)
                     Recheck Cond: (((title)::text ~~* '%ras du%'::text) AND
(clientid = '12'::bigint))
                     Filter: (time_job > 257826264)
                     Heap Blocks: exact=1
                     ->  Bitmap Index Scan on ix_ja_jobs_trgm_clientid_gin 
(cost=0.00..7.20 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1)
                           Index Cond: (((title)::text ~~* '%ras du%'::text)
AND (clientid = '12'::bigint))
 Planning time: 0.169 ms
 Execution time: 0.061 ms
(13 rows)
 

 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>