Re: index on ILIKE/LIKE - PostgreSQL 9.2

På torsdag 12. mai 2016 kl. 10:05:01, skrev Andreas Joseph Krogh <
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>>:
[snp] 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)

 
Forgot to say, this is in PG-9.6 (master), but should work on previous
versions.
 
-- 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>