On Fri, 15 Dec 2000, Artur Rataj wrote:
> 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
In response to
pgsql-sql by date
|Next:||From: Graham Vickrage||Date: 2000-12-15 18:36:39|
|Subject: Use of indexes in plpgsql functions|
|Previous:||From: Brett W. McCoy||Date: 2000-12-15 18:24:11|
|Subject: Re: readline ??|