Re: index on ILIKE/LIKE - PostgreSQL 9.2

On Thu, May 12, 2016 at 2:32 PM, Lucas Possamai <drum(dot)lucas(at)gmail(dot)com> wrote:

>> -> Bitmap Index Scan on "ix_jobs_trgm_gin"
>> (cost=0.00..335.64 rows=485 width=0) (actual time=3883.886..3883.886 rows=32
>> loops=1)
>> Index Cond: (("title")::"text" ~~* '%RYAN
>> WER%'::"text")
>> Buffers: shared hit=5945
>> Total runtime: 3945.554 ms

So it is not cold-cache or IO problems, but a CPU problem. Your query
only has 6 trigrams in it, and that is causing nearly 6000 buffer
hits. I'm guessing the " w" trigram is extremely common in your data
set. Anyway, you have some huge posting lists there, and they were
not dealt with very well in 9.2 or 9.3.

Cheers,

Jeff