Re: index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 9:36 PM, Lucas Possamai <drum(dot)lucas(at)gmail(dot)com>
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.