pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
full text index
3+ messages / 2 participants
[nested] [flat]

* full text index
@ 1999-08-14 05:28 Clayton Cottingham <drfrog@smartt.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Clayton Cottingham @ 1999-08-14 05:28 UTC (permalink / raw)
  To: pgsql-sql

hello,
ive been using this, i have aproximately 130k of rows to go over, this
seems to take a long time



has anyone else had experience with this?






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* full text index
@ 2000-12-15 18:08 Artur Rataj <arataj@iitis.gliwice.pl>
  2000-12-15 18:33 ` Re: full text index Artur Rataj <arataj@iitis.gliwice.pl>
  0 siblings, 1 reply; 3+ messages in thread

From: Artur Rataj @ 2000-12-15 18:08 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: full text index
  2000-12-15 18:08 full text index Artur Rataj <arataj@iitis.gliwice.pl>
@ 2000-12-15 18:33 ` Artur Rataj <arataj@iitis.gliwice.pl>
  0 siblings, 0 replies; 3+ messages in thread

From: Artur Rataj @ 2000-12-15 18:33 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-12-15 18:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-14 05:28 full text index Clayton Cottingham <drfrog@smartt.com>
2000-12-15 18:08 full text index Artur Rataj <arataj@iitis.gliwice.pl>
2000-12-15 18:33 ` Artur Rataj <arataj@iitis.gliwice.pl>

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