Re: Why is this query not using GIN index?

Oleg Bartunov <obartunov(at)gmail(dot)com> writes:
> On Sun, Nov 13, 2016 at 6:05 PM, Aaron Lewis <the(dot)warl0ck(dot)1989(at)gmail(dot)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