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: Re: full text index
Date: Fri, 15 Dec 2000 19:33:56 +0100 (MET)
Message-ID: <Pine.GSO.4.05.10012151927530.9698-100000@atos> (raw)
In-Reply-To: <Pine.GSO.4.05.10012141951080.9698-100000@atos>


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




view thread (3+ messages)

Message-ID: <Pine.GSO.4.05.10012151927530.9698-100000@atos>
Permalink:  ../Pine.GSO.4.05.10012151927530.9698-100000@atos/
Also on:    postgresql.org/message-id/Pine.GSO.4.05.10012151927530.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.10012151927530.9698-100000@atos>
  References: <Pine.GSO.4.05.10012141951080.9698-100000@atos> <Pine.GSO.4.05.10012151927530.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