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" <[EMAIL PROTECTED]> >To: "Paul C." <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> >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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
