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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2010-02-28 22:58:42 | Re: remove contrib/xml2 |
Previous Message | Josh Berkus | 2010-02-28 22:38:28 | Re: Re: Hot Standby query cancellation and Streaming Replication integration |
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2010-02-28 22:46:52 | Re: partitioned tables query not using indexes |
Previous Message | Kenneth Marshall | 2010-02-28 21:51:57 | Re: partitioned tables query not using indexes |