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

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,