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

Started by Alex Nethabout 17 years ago2 messagesgeneral
Jump to latest
#1Alex Neth
alex@liivid.com

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.

=> explain select id from source_listings where
plainto_tsquery('view') @@ to_tsvector('english', full_listing);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on source_listings (cost=1454.88..7445.47
rows=1595 width=4)
Recheck Cond: (plainto_tsquery('view'::text) @@
to_tsvector('english'::regconfig, full_listing))
-> Bitmap Index Scan on kw2_index (cost=0.00..1454.48 rows=1595
width=0)
Index Cond: (plainto_tsquery('view'::text) @@
to_tsvector('english'::regconfig, full_listing))

=> explain analyze select id from source_listings where
plainto_tsquery('view') @@ to_tsvector('english', full_listing);
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
... it just keeps building a new index until I kill it
Cancel request sent

#2Bruce Momjian
bruce@momjian.us
In reply to: Alex Neth (#1)
Re: Full text index not being used, even though it is in the plan

Alex Neth <alex@liivid.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