pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Artur Rataj <arataj@iitis.gliwice.pl>
To: pgsql-sql@postgresql.org
Subject: full text index
Date: Fri, 15 Dec 2000 19:08:33 +0100 (MET)
Message-ID: <Pine.GSO.4.05.10012141951080.9698-100000@atos> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <Pine.GSO.4.05.10012141951080.9698-100000@atos>
Permalink:  ../Pine.GSO.4.05.10012141951080.9698-100000@atos/
Also on:    postgresql.org/message-id/Pine.GSO.4.05.10012141951080.9698-100000@atos

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: arataj@iitis.gliwice.pl
  Subject: Re: full text index
  In-Reply-To: <Pine.GSO.4.05.10012141951080.9698-100000@atos>
  References: <Pine.GSO.4.05.10012141951080.9698-100000@atos>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox