On Wed, May 11, 2016 at 10:03 PM, Lucas Possamai <drum.lucas@xxxxxxxxx> 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.
