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

full text index

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

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


pgsql-sql by date

Next:From: Brett W. McCoyDate: 2000-12-15 18:24:11
Subject: Re: readline ??
Previous:From: Stephan SzaboDate: 2000-12-15 17:38:26
Subject: Re: [Re: postgres]

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