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

full text search index scan query plan changed in 8.4.2?

From: xu fei <autofei(at)yahoo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: full text search index scan query plan changed in 8.4.2?
Date: 2010-02-28 20:42:09
Message-ID: 60218.64374.qm@web45406.mail.sp1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Hi, hackers:

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!

Xu Fei


      


Responses

pgsql-performance by date

Next:From: Kenneth MarshallDate: 2010-02-28 21:51:57
Subject: Re: partitioned tables query not using indexes
Previous:From: Josh BerkusDate: 2010-02-28 20:29:14
Subject: Re: partitioned tables query not using indexes

pgsql-hackers by date

Next:From: Greg SmithDate: 2010-02-28 20:48:51
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration
Previous:From: Greg SmithDate: 2010-02-28 20:21:23
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration

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