Re: index on ILIKE/LIKE - PostgreSQL 9.2

>
>
>>
> 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