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