Received: from iitis.gliwice.pl ([212.106.181.2]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBFIXY020999 for ; Fri, 15 Dec 2000 13:33:34 -0500 (EST) (envelope-from arataj@iitis.gliwice.pl) Received: from localhost (arataj@localhost) by iitis.gliwice.pl (8.11.1/8.11.1) with ESMTP id eBFIXuC14222 for ; Fri, 15 Dec 2000 19:33:56 +0100 (MET) Date: Fri, 15 Dec 2000 19:33:56 +0100 (MET) From: Artur Rataj X-Sender: arataj@atos To: pgsql-sql@postgresql.org Subject: Re: full text index In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200012/173 X-Sequence-Number: 507 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