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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2010-02-28 20:48:51 | Re: Re: Hot Standby query cancellation and Streaming Replication integration |
Previous Message | Greg Smith | 2010-02-28 20:21:23 | Re: Re: Hot Standby query cancellation and Streaming Replication integration |
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Marshall | 2010-02-28 21:51:57 | Re: partitioned tables query not using indexes |
Previous Message | Josh Berkus | 2010-02-28 20:29:14 | Re: partitioned tables query not using indexes |