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,