Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id AFCB89FB95C; Fri, 5 Oct 2007 19:58:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 56659-09; Fri, 5 Oct 2007 19:57:58 -0300 (ADT) X-Greylist: delayed 15:07:30.381945 by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from spunkymail-a17.g.dreamhost.com (sd-green-bigip-207.dreamhost.com [208.97.132.207]) by postgresql.org (Postfix) with ESMTP id 4602B9FB955; Fri, 5 Oct 2007 19:57:58 -0300 (ADT) Received: from [192.168.0.159] (1032hostw83.starwoodbroadband.com [12.31.48.83]) by spunkymail-a17.g.dreamhost.com (Postfix) with ESMTP id BBEDD74CFC; Fri, 5 Oct 2007 15:57:55 -0700 (PDT) In-Reply-To: References: <16663.1191597165@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <6936504E-3560-4BE9-86BE-BEDAB3CD355A@araisoft.com> Cc: Tom Lane , PostgreSQL , pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: Benjamin Arai Subject: Re: [PERFORM] Slow TSearch2 performance for table with 1 million documents. Date: Fri, 5 Oct 2007 15:57:31 -0700 To: Oleg Bartunov X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/317 X-Sequence-Number: 121609 On Oct 5, 2007, at 8:32 AM, Oleg Bartunov wrote: > On Fri, 5 Oct 2007, Tom Lane wrote: > >> Benjamin Arai 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 ? > Using Rank: -# ('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=98083.081..98084.351 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=479.122..98067.594 rows=5119 loops=1) -> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1) -> Bitmap Heap Scan on fulltext_article (cost=3069.79..6516.70 rows=933 width=742) (actual time=341.739..37112.110 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=321.443..321.443 rows=5119 loops=1) Index Cond: (fulltext_article.idxfti @@ q.q) Total runtime: 98087.575 ms (9 rows) Using Rank_cd: # explain analyze select * FROM fulltext_article, to_tsquery ('simple','cat') AS q WHERE idxfti @@ q ORDER BY rank_cd(idxfti, q) DESC; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------- Sort (cost=6576.74..6579.07 rows=933 width=774) (actual time=199316.648..199324.631 rows=26054 loops=1) Sort Key: rank_cd(fulltext_article.idxfti, q.q) -> Nested Loop (cost=3069.79..6530.71 rows=933 width=774) (actual time=871.428..199244.330 rows=26054 loops=1) -> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1) -> Bitmap Heap Scan on fulltext_article (cost=3069.79..6516.70 rows=933 width=742) (actual time=850.674..50146.477 rows=26054 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=838.120..838.120 rows=26054 loops=1) Index Cond: (fulltext_article.idxfti @@ q.q) Total runtime: 199338.297 ms (9 rows) > >> >> 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 >