Re: FTI is really really slow; what am I doing wrong?

Meant to send this to the whole list...

I had vaccum'ed it, but not 'vacuum analyze' -ed it. That did the trick.
Cost down to 12.09 from 80628.92.

Thank you!

>From: "Mitch Vincent" <mvincent(at)cablespeed(dot)com>
>To: "Paul C." <ulive1x(at)hotmail(dot)com>, <pgsql-general(at)postgresql(dot)org>
>Subject: Re: [GENERAL] FTI is really really slow; what am I doing wrong?
>Date: Wed, 22 Aug 2001 11:28:04 -0400
>
>You've vacuum analyze 'd the database, haven't you?
>
>-Mitch
>
> > There is exactly one sentence (row) that has the strings 'Newton' and
> > 'Kepler' in it. That is my target. For a straight select on ST:
> > select * from st where body ~* 'newton' and body ~* 'kepler';
> > the cost is 1100.41
> > BUT for an query using the FTI indices:
> > select s.* from st s, st_fti f1, st_fti f2 where f1.string
> > ~ '^kepler' and f2.string ~ '^newton' and s.oid = f1.id
> > and s.oid = f2.id;
> > the cost becomes a staggering 80628.92!!! The plans are pasted at the
>end
> > of this message.
> > Now, I have all the indices created (on id of st_fti, on string of
>st_fti
> > and on oid of st). I cannot figure out why this is so much worse than
>the
> > straight query. Indeed, the cost to look up a single string in the
>st_fti
> > table is way high:
> > select * from st_fti where string ~ '^kepler';
> > costs 36703.40, AND its doing a Seq Scan on st_fti, even though an index
> > exists.
> > What am I doing wrong? Is it the sheer size of the st_fti table that is
> > causing problems? Any help would be greatly appreciated.
> > Thanks,
>
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp