Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 48C60633EBB for ; Sun, 28 Feb 2010 18:42:21 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 17409-01-10 for ; Sun, 28 Feb 2010 22:42:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com [69.36.227.170]) by mail.postgresql.org (Postfix) with ESMTP id 06CC9633E8F for ; Sun, 28 Feb 2010 18:41:55 -0400 (AST) Received: from adsl-63-195-55-98.dsl.snfc21.pacbell.net ([63.195.55.98] helo=Sidney-Stratton.local) by glacier.frostconsultingllc.com with esmtpsa (TLSv1:CAMELLIA256-SHA:256) (Exim 4.69) (envelope-from ) id 1Nlrpf-0004RM-Hy; Sun, 28 Feb 2010 14:41:53 -0800 Message-ID: <4B8AF12A.4000002@agliodbs.com> Date: Sun, 28 Feb 2010 14:41:46 -0800 From: Josh Berkus User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 MIME-Version: 1.0 To: xu fei CC: postgres performance list Subject: Re: [HACKERS] full text search index scan query plan changed in 8.4.2? References: <60218.64374.qm@web45406.mail.sp1.yahoo.com> In-Reply-To: <60218.64374.qm@web45406.mail.sp1.yahoo.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.181 tagged_above=-10 required=5 tests=AWL=0.418, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/402 X-Sequence-Number: 37823 Xufei, List changed to psql-performance, which is where this discussion belongs. > I am testing the index used by full text search recently. > > I have install 8.3.9 and 8.4.2 separately. > > In 8.3.9, the query plan is like: > > postgres=# explain SELECT s.name as source , t.name as target FROM element as s, element as t WHERE to_tsvector('testcfg',s.name) @@ to_tsquery('testcfg',replace(t.name,':','|')); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=0.01..259.92 rows=491 width=18) > -> Seq Scan on element t (cost=0.00..13.01 rows=701 width=9) > -> Index Scan using element_ftsidx_test on element s (cost=0.01..0.33 rows=1 width=9) > Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text))) > (4 rows) > > I have index: "element_ftsidx_test" gin (to_tsvector('testcfg'::regconfig, name::text)) > > The same index and query in 8.4.2: > > postgres=# explain SELECT s.name as source , t.name as target FROM element as s, element as t WHERE to_tsvector('testcfg',s.name) @@ to_tsquery('testcfg',replace(t.name,':','|')) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=0.32..3123.51 rows=2457 width=18) > -> Seq Scan on element t (cost=0.00..13.01 rows=701 width=9) > -> Bitmap Heap Scan on element s (cost=0.32..4.36 rows=4 width=9) Recheck Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text))) > -> Bitmap Index Scan on element_ftsidx_test (cost=0.00..0.32 rows=4 width=0) > Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text))) > (6 rows) > > Why the query plans are different and why? Thanks! Because the row estimates changed, since 8.4 improved row estimation for TSearch. The 2nd query is probably actually faster, no? If not, you may need to increase your stats collection. Or at least show us a VACUUM ANALYZE. --Josh Berkus