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

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?

            regards, tom lane