Skip site navigation (1) Skip section navigation (2)

Re: full text index

From: Artur Rataj <arataj(at)iitis(dot)gliwice(dot)pl>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: full text index
Date: 2000-12-15 18:33:56
Message-ID: Pine.GSO.4.05.10012151927530.9698-100000@atos (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Graham VickrageDate: 2000-12-15 18:36:39
Subject: Use of indexes in plpgsql functions
Previous:From: Brett W. McCoyDate: 2000-12-15 18:24:11
Subject: Re: readline ??

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group