Re: Q: text query search and

amihay gonen <agonenil(at)gmail(dot)com> wrote:

> explain ANALYZE select * from test_test
> where text_data@@plainto_tsquery('name');
>
> my questions are :
> 1. why the index is not used (I guess it is related to the way
> the data is generated)

Because the sequential scan has a lower cost based on available
statistics. If I run all the example statements without any
delays, I actually see it using the index; it is only if I run
VACUUM ANALYZE (or give autovacuum enough time to do so) that I see
a sequential scan. That's because it sees that it will need to
visit all the tuples in the heap anyway, so why bother also
visiting all the index tuples? If you search for a word that's not
in the table, you'll see it use the index, too.

> 2, how can I use pg_trgm with ts_vector to enable to answer query
> like 10.10 or nam ? the idea is to use the gin index , maybe
> there are other option without using pg_trgm?

The easy way to do that is just to add a trigram index and search
for similar strings, and forget about full text search. If you
want to use full text search for this you may need to use special
dictionaries or parser code. (I used both for a system to search
court document text, and it seemed to work well.) The facilities
for custom full text search parsers seem pretty bad; I found what I
needed using regular expressions and cast to the appopriate ts
types.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company