Re: index on ILIKE/LIKE - PostgreSQL 9.2

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Wed, May 11, 2016 at 9:36 PM, Lucas Possamai <drum.lucas@xxxxxxxxx> wrote:

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 

Trying redoing the query with CTE as below:

WITH ja_jobs as
  (SELECT DISTINCT title
     FROM ja_jobs
    WHERE clientid = 31239  AND time_job > 1457826264
  )
SELECT title 
  FROM ja_jobs
 WHERE title ILIKE 'RYAN WER%'
 ORDER BY title
 LIMIT 10;


--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.