Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] full text search index scan query plan changed in 8.4.2?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: xu fei <autofei(at)yahoo(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [HACKERS] full text search index scan query plan changed in 8.4.2?
Date: 2010-02-28 22:41:46
Message-ID: 4B8AF12A.4000002@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2010-02-28 22:46:52
Subject: Re: partitioned tables query not using indexes
Previous:From: Kenneth MarshallDate: 2010-02-28 21:51:57
Subject: Re: partitioned tables query not using indexes

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2010-02-28 22:58:42
Subject: Re: remove contrib/xml2
Previous:From: Josh BerkusDate: 2010-02-28 22:38:28
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group