DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=gWfwHt4yNbOG5OX4NDzvNRdeA9JH1FhCnWeO162S9cKHP3KSHu8Hy5hwewG1h7S1Xa
	6mT05g51GLOZ5Z5qQLb4zSwaJ+Ly0phl4F0++Jtcah1gWZvue1uDrs2i1m/4yMThIPT4
	mN0o0vk+qG35r6zQ8uf0FDghvDPwz/9ZluUL0=
Message-ID: <4B8B2890.40000@gmail.com>
Date: Mon, 01 Mar 2010 13:38:08 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
CC: xu fei <autofei@yahoo.com>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] full text search index scan query plan changed in
 8.4.2?
References: <60218.64374.qm@web45406.mail.sp1.yahoo.com>
	<4B8AF12A.4000002@agliodbs.com>
In-Reply-To: <4B8AF12A.4000002@agliodbs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
> 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.

I'm sure you mean explain analyze :)

-- 
Postgresql & php tutorials
http://www.designmagick.com/