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@cablespeed.com>
>To: "Paul C." <ulive1x@hotmail.com>, <pgsql-general@postgresql.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