Re: [PERFORM] Slow TSearch2 performance for table with 1 million documents.

On Fri, 5 Oct 2007, Tom Lane wrote:

> Benjamin Arai <benjamin(at)araisoft(dot)com> writes:
>> # explain analyze select * FROM fulltext_article, to_tsquery
>> ('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, q) DESC;
>
>> QUERY PLAN
>> ------------------------------------------------------------------------
>> ------------------------------------------------------------------------
>> ------------
>> Sort (cost=6576.74..6579.07 rows=933 width=774) (actual
>> time=12969.237..12970.490 rows=5119 loops=1)
>> Sort Key: rank(fulltext_article.idxfti, q.q)
>> -> Nested Loop (cost=3069.79..6530.71 rows=933 width=774)
>> (actual time=209.513..12955.498 rows=5119 loops=1)
>> -> Function Scan on q (cost=0.00..0.01 rows=1 width=32)
>> (actual time=0.005..0.006 rows=1 loops=1)
>> -> Bitmap Heap Scan on fulltext_article
>> (cost=3069.79..6516.70 rows=933 width=742) (actual
>> time=209.322..234.390 rows=5119 loops=1)
>> Recheck Cond: (fulltext_article.idxfti @@ q.q)
>> -> Bitmap Index Scan on fulltext_article_idxfti_idx
>> (cost=0.00..3069.56 rows=933 width=0) (actual time=208.373..208.373
>> rows=5119 loops=1)
>> Index Cond: (fulltext_article.idxfti @@ q.q)
>> Total runtime: 12973.035 ms
>> (9 rows)
>
> The time seems all spent at the join step, which is odd because it
> really hasn't got much to do. AFAICS all it has to do is compute the
> rank() values that the sort step will use. Is it possible that
> rank() is really slow?

can you try rank_cd() instead ?

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83