Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6A2809FB2E3; Fri, 5 Oct 2007 09:01:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 80674-03; Fri, 5 Oct 2007 09:01:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mx1.magproductions.nl (mx1.magproductions.nl [87.249.97.171]) by postgresql.org (Postfix) with ESMTP id 57EC69FB2E0; Fri, 5 Oct 2007 09:01:22 -0300 (ADT) Received: from gw.office.mag.hq ([172.27.1.1] helo=localhost) by mx1.magproductions.nl with esmtps (TLS-1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1Idlrh-0003dB-FC; Fri, 05 Oct 2007 14:01:18 +0200 Received: from magenta.office.mag.hq ([172.27.1.6]) by localhost with esmtp (Exim 4.63) (envelope-from ) id 1Idlra-0005Dq-FG; Fri, 05 Oct 2007 14:01:02 +0200 Message-ID: <4706277B.6010000@magproductions.nl> Date: Fri, 05 Oct 2007 14:00:59 +0200 From: Alban Hertroys User-Agent: Thunderbird 1.5.0.12 (X11/20070604) MIME-Version: 1.0 To: Benjamin Arai CC: PostgreSQL , pgsql-performance@postgresql.org Subject: Re: Slow TSearch2 performance for table with 1 million documents. References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-MAG-Spam-Score: -2.6 X-MAG-Spam-Level: -- X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/285 X-Sequence-Number: 121577 Benjamin Arai wrote: > Hi, > > I have very slow performance for a TSearch2 table. I have pasted the > EXPLAIN ANALYZE queries below. 12 seconds is slow for almost any > purpose. Is there any way to speed this up? > > # explain analyze select * FROM fulltext_article, > to_tsquery('simple','dog') AS q WHERE idxfti @@ q ORDER BY rank(idxfti, > q) DESC; Admittedly I'm kind of new to tsearch, but wouldn't SELECT * FROM fulltext_article WHERE idxfti @@ to_tsquery('simple','dog') ORDER BY rank(idxfti, to_tsquery('simple', 'dog')) DESC; be faster? Quick testing shows a similar query in our database to not use a nested loop and a function scan. For comparison, here are our plans: Your approach: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=4.86..4.87 rows=1 width=164) (actual time=0.151..0.161 rows=5 loops=1) Sort Key: rank(fulltext_article.idxfti, q.q) -> Nested Loop (cost=0.00..4.85 rows=1 width=164) (actual time=0.067..0.119 rows=5 loops=1) -> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual time=0.010..0.012 rows=1 loops=1) -> Index Scan using fulltext_article_idxfti_idx on fulltext_article (cost=0.00..4.82 rows=1 width=132) (actual time=0.033..0.056 rows=5 loops=1) Index Cond: (fulltext_article.idxfti @@ "outer".q) Filter: (fulltext_article.idxfti @@ "outer".q) Total runtime: 0.242 ms (8 rows) My suggested approach: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=4.84..4.84 rows=1 width=132) (actual time=0.085..0.095 rows=5 loops=1) Sort Key: rank(idxfti, '''dog'''::tsquery) -> Index Scan using fulltext_article_idxfti_idx on fulltext_article (cost=0.00..4.83 rows=1 width=132) (actual time=0.025..0.052 rows=5 loops=1) Index Cond: (idxfti @@ '''dog'''::tsquery) Filter: (idxfti @@ '''dog'''::tsquery) Total runtime: 0.163 ms (6 rows) I hope this helps. -- Alban Hertroys a.hertroys@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //