Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 81DEE9FB9C7; Fri, 5 Oct 2007 12:13:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08710-09; Fri, 5 Oct 2007 12:12:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5309A9FB9D2; Fri, 5 Oct 2007 12:12:53 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l95FCjWN016664; Fri, 5 Oct 2007 11:12:46 -0400 (EDT) To: Benjamin Arai cc: PostgreSQL , pgsql-performance@postgresql.org Subject: Re: Slow TSearch2 performance for table with 1 million documents. In-reply-to: References: Comments: In-reply-to Benjamin Arai message dated "Fri, 05 Oct 2007 00:50:17 -0700" Date: Fri, 05 Oct 2007 11:12:45 -0400 Message-ID: <16663.1191597165@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/298 X-Sequence-Number: 121590 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? regards, tom lane