Re: index on ILIKE/LIKE - PostgreSQL 9.2

On Thu, May 12, 2016 at 2:32 PM, Lucas Possamai <drum.lucas@gmail.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