Re: index on ILIKE/LIKE - PostgreSQL 9.2

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