Oleg Bartunov <obartunov@gmail.com> writes:
> On Sun, Nov 13, 2016 at 6:05 PM, Aaron Lewis <the.warl0ck.1989@gmail.com>
>> It takes 500ms with 10m rows, could it be faster?
> sure. Recheck with function call is pretty expensive, so I'd not recommend
> to create functional index, just create separate column of type tsvector
> (materialize to_tsvector) and create gin index on it. You should surprise.
I doubt it'll help that much --- more than half the time is going into the
bitmap indexscan, and with over 1m candidate matches, there's no way
that's going to be super cheap.
I wonder whether a gist index would be better here, since it would support
a plain indexscan which should require scanning much less of the index
given the small LIMIT.
(Materializing the tsvector would probably help for gist, too, by reducing
the cost of lossy-index rechecks.)
BTW, it still looks like the performance is being significantly hurt by
inadequate work_mem.
regards, tom lane