Re: index on ILIKE/LIKE - PostgreSQL 9.2 - Mailing list pgsql-general

The main problem is WHERE title ILIKE '%RYAN WER%'

When you put a % on the left of the text, there is no way to optimize that, so yes, it will be slow.

If you can eliminate the leading percent and just have trailing, it will be much faster.

Hmm.. yep.. I suppose I can do that.

But, taking the left % off, the query is still slow:

Limit  (cost=418.57..418.58 rows=1 width=20) (actual time=4439.367..4439.381 rows=1 loops=1)
  Buffers: shared hit=6847
  ->  Unique  (cost=418.57..418.58 rows=1 width=20) (actual time=4439.363..4439.374 rows=1 loops=1)
        Buffers: shared hit=6847
        ->  Sort  (cost=418.57..418.58 rows=1 width=20) (actual time=4439.360..4439.365 rows=4 loops=1)
              Sort Key: "title"
              Sort Method: quicksort  Memory: 25kB
              Buffers: shared hit=6847
              ->  Bitmap Heap Scan on "ja_jobs"  (cost=414.55..418.56 rows=1 width=20) (actual time=4439.312..4439.329 rows=4 loops=1)
                    Recheck Cond: (("clientid" = 31239) AND ("time_job" > 1457826264) AND (("title")::"text" ~~* 'RYAN SHOWER%'::"text"))
                    Buffers: shared hit=6847
                    ->  BitmapAnd  (cost=414.55..414.55 rows=1 width=0) (actual time=4439.280..4439.280 rows=0 loops=1)
                          Buffers: shared hit=6843
                          ->  Bitmap Index Scan on "ix_jobs_client_times"  (cost=0.00..50.67 rows=1711 width=0) (actual time=0.142..0.142 rows=795 loops=1)
                                Index Cond: (("clientid" = 31239) AND ("time_job" > 1457826264))
                                Buffers: shared hit=8
                          ->  Bitmap Index Scan on "ix_ja_jobs_trgm_gin"  (cost=0.00..363.62 rows=483 width=0) (actual time=4439.014..4439.014 rows=32 loops=1)
                                Index Cond: (("title")::"text" ~~* 'RYAN SHOWER%'::"text")
                                Buffers: shared hit=6835
Total runtime: 4439.427 ms

Here [1] it appears to be working even with two %.. But it's not for me.... 

[1] https://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

Any ideia? lol