Re: full text index

On Fri, 15 Dec 2000, Artur Rataj wrote:

> Hello,
> 
> I have a question about a full text index.
> I have created such index over a text field. I have stored
> substrings of each word in the text field, so that for `example' they
> would be `example', `xample' and so on to `le'. The index has been
> physically ordered by string, indices were created on it, and `vacuum' was
> performed, and then I tried a statement like:
> 
> select fserial from fti where string ~ '^xample';
> 
> And it had a speed comparable to a sequential scan of the original table
> using the `~*' operator.
> 
> `Explain' for that statement shows:
> 
> Index Scan using fti_key on fti
> (cost=19249.90 rows=1 width=4)
> 
> The original table has about 4.000 rows, and the full text index
> table has about 1.000.000 rows.
> 
> Why the query that uses the full text index is so slow, in compare to
> a sequential scan?
> 
> Best regards
> 
> Artur Rataj
> 
> 

Now I have compared the statements more exactly -- the one
that uses fti is much slower:

using fti index, about 150 rows found: about 80s
sequential scan, about 130 rows found: about 5s