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

On Fri, 5 Oct 2007, Tom Lane wrote:

> Benjamin Arai <benjamin@araisoft.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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83