Re: Full text index not being used, even though it is in the plan

Alex Neth <alex(at)liivid(dot)com> writes:

> I am trying to use a full text index, but it seems to be reindexing on every
> query.
>
> The query plan looks fine, but the queries take extremely long (hours even).
> I think it is reindexing because it is notifying me that certain long "words"
> won't be indexed as you can see below, which is what it does when I create the
> index.

I don't think it's reindexing, it's just calling to_tsvector() which it has to
do when it rechecks rows that the index says might match.

Is it possible that nearly all the full_listing values contain "view"? How
does it perform with much more selective searches?

If your full_listing values are quite large then recalculating the tsvector
might be a lot more expensive than doing a full table scan and LIKE match for
cases when nearly the whole table is going to be scanned anyways.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning