Re: FTI is really really slow; what am I doing wrong?
От | Mitch Vincent |
---|---|
Тема | Re: FTI is really really slow; what am I doing wrong? |
Дата | |
Msg-id | 001601c12b1f$097e0980$1251000a@mitch обсуждение исходный текст |
Ответ на | FTI is really really slow; what am I doing wrong? ("Paul C." <ulive1x@hotmail.com>) |
Список | pgsql-general |
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,
В списке pgsql-general по дате отправления: