From: Chris <dmagick@gmail.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: xu fei <autofei@yahoo.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] full text search index scan query plan changed in 8.4.2?
Date: Mon, 01 Mar 2010 13:38:08 +1100
Message-ID: <4B8B2890.40000@gmail.com> (raw)
In-Reply-To: <4B8AF12A.4000002@agliodbs.com>
References: <60218.64374.qm@web45406.mail.sp1.yahoo.com>
<4B8AF12A.4000002@agliodbs.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmagick@gmail.com, josh@agliodbs.com, autofei@yahoo.com
Subject: Re: [HACKERS] full text search index scan query plan changed in 8.4.2?
In-Reply-To: <4B8B2890.40000@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox