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