Re: index on ILIKE/LIKE - PostgreSQL 9.2

On Wed, May 11, 2016 at 5:30 PM, Lucas Possamai <drum(dot)lucas(at)gmail(dot)com> wrote:
> Hi there!
>
> I've got a simple but slow query:
>
>> SELECT DISTINCT title
>> FROM ja_jobs WHERE title ILIKE '%RYAN WER%'
>> and clientid = 31239 AND time_job > 1457826264
>> order BY title
>> limit 10

>
> CREATE INDEX ix_ja_jobs_trgm_gin ON public.ja_jobs USING gin (title
> gin_trgm_ops);
>
> Explain analyze after the index: (Yes, I ran the analyze)
>
>> Limit (cost=389.91..389.91 rows=1 width=20) (actual
>> time=3720.511..3720.511 rows=0 loops=1)
>> -> Unique (cost=389.91..389.91 rows=1 width=20) (actual
>> time=3720.507..3720.507 rows=0 loops=1)
>> -> Sort (cost=389.91..389.91 rows=1 width=20) (actual
>> time=3720.505..3720.505 rows=0 loops=1)
>> Sort Key: "title"
>> Sort Method: quicksort Memory: 25kB
>> -> Bitmap Heap Scan on "ja_jobs" (cost=385.88..389.90
>> rows=1 width=20) (actual time=3720.497..3720.497 rows=0 loops=1)
>> Recheck Cond: (("clientid" = 31239) AND ("time_job" >
>> 1457826264) AND (("title")::"text" ~~ '%RYAN WER%'::"text"))
>> Rows Removed by Index Recheck: 4
>> -> BitmapAnd (cost=385.88..385.88 rows=1 width=0)
>> (actual time=3720.469..3720.469 rows=0 loops=1)
>> -> Bitmap Index Scan on "ix_jobs_client_times"
>> (cost=0.00..50.00 rows=1644 width=0) (actual time=0.142..0.142 rows=795
>> loops=1)
>> Index Cond: (("clientid" = 31239) AND
>> ("time_job" > 1457826264))
>> -> Bitmap Index Scan on "ix_ja_jobs_trgm_gin"
>> (cost=0.00..335.63 rows=484 width=0) (actual time=3720.213..3720.213 rows=32
>> loops=1)
>> Index Cond: (("title")::"text" ~~ '%RYAN
>> WER%'::"text")
>> Total runtime: 3720.653 ms

How big is the table? The gin index? shared_buffers? RAM? What
kind of IO system do you have, and how many other things were going on
with it?

It would be interesting to see the output of explain (analyze,
buffers) with track_io_timing turned on.

There have been improvements in this area since 9.2, you should
consider upgrading to at least 9.4.

Cheers,

Jeff