Re: index on ILIKE/LIKE - PostgreSQL 9.2 - Mailing list pgsql-general

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;

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