Re: index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 10:03 PM, Lucas Possamai <drum(dot)lucas(at)gmail(dot)com>
wrote:

>
>>>
>> 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;
>>
>
> hmm.. still slow =(
>
>
> and it's not hitting the index: (i had to change the clientid because the
> previous one was in cache)
>
> Limit (cost=93790.08..93790.09 rows=1 width=218) (actual
>> time=284.293..284.308 rows=5 loops=1)
>> Buffers: shared hit=42284
>> CTE ja_jobs
>> -> HashAggregate (cost=93774.31..93779.16 rows=485 width=20)
>> (actual time=207.235..228.141 rows=16320 loops=1)
>> Buffers: shared hit=42284
>> -> Bitmap Heap Scan on "ja_jobs" (cost=882.98..93697.86
>> rows=30578 width=20) (actual time=21.942..133.380 rows=48472 loops=1)
>> Recheck Cond: (("clientid" = 14635) AND ("time_job" >
>> 1436731799))
>> Buffers: shared hit=42284
>> -> Bitmap Index Scan on "ix_jobs_client_times"
>> (cost=0.00..875.34 rows=30578 width=0) (actual time=12.389..12.389
>> rows=48472 loops=1)
>> Index Cond: (("clientid" = 14635) AND ("time_job" >
>> 1436731799))
>> Buffers: shared hit=243
>> -> Sort (cost=10.92..10.93 rows=1 width=218) (actual
>> time=284.289..284.293 rows=5 loops=1)
>> Sort Key: "ja_jobs"."title"
>> Sort Method: quicksort Memory: 25kB
>> Buffers: shared hit=42284
>> -> CTE Scan on "ja_jobs" (cost=0.00..10.91 rows=1 width=218)
>> (actual time=236.248..284.263 rows=5 loops=1)
>> Filter: (("title")::"text" ~~* '%To Electrical%'::"text")
>> Rows Removed by Filter: 16315
>> Buffers: shared hit=42284
>> Total runtime: 287.633 ms
>
>
>

I think a GIN index can't be used for sorting.