Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 1711D633030 for ; Sun, 28 Feb 2010 16:42:22 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 87614-02 for ; Sun, 28 Feb 2010 20:42:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n66.bullet.mail.sp1.yahoo.com (n66.bullet.mail.sp1.yahoo.com [98.136.44.50]) by mail.postgresql.org (Postfix) with SMTP id 731DF63287B for ; Sun, 28 Feb 2010 16:42:10 -0400 (AST) Received: from [216.252.122.217] by n66.bullet.mail.sp1.yahoo.com with NNFMP; 28 Feb 2010 20:42:09 -0000 Received: from [98.136.44.173] by t2.bullet.sp1.yahoo.com with NNFMP; 28 Feb 2010 20:42:09 -0000 Received: from [127.0.0.1] by omp614.mail.sp1.yahoo.com with NNFMP; 28 Feb 2010 20:42:09 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 193904.25613.bm@omp614.mail.sp1.yahoo.com Received: (qmail 65063 invoked by uid 60001); 28 Feb 2010 20:42:09 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1267389729; bh=CxD99jSaUOhbNPrneJO2DtJZJdB06RIBThL4nsSkJic=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=F6rg9xFWAsciikDHD1PwLV8uJvVrDME49kvzoOyaoQh2B5eo/kNrjjP2vbdXEOVu1zaIF34wa0KfFkbi+CSoat3h6jmpVcqL4w3rN5/6j7iJ6nnI+aiDNDzaKehUfy58wF23M2j+/7G4oWCPSpUQa1XAtE0dJzOuC7Sb5xx/PNM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=MvKoTV3HVSa13BlzbjFYAAyMeBCCQbIoEYVuNtO0/8N/t1mwZEOLxpfGDOmsj4aP4d7yb1JYQMqIBRkL4ww85kWEzFXsn/ob8Z6a9jo1Psc6urhjhurwaBPV4Rsr9Yd+eAEWADq6/Znelxw7ZE2Q4my6lTHYeM7Dp5W3HBttGag=; Message-ID: <60218.64374.qm@web45406.mail.sp1.yahoo.com> X-YMail-OSG: xJl3G38VM1m5nAPTT.IhcE49AaEc2fdHGBKUqPFHQ2m_8liTUw_omD.T4APnI_QBdKf163_.W_Ek8vT50l606WA7fEm5qjD6ZQjqPctEul5wHzf1RATAFtZCyU9IoipL6lCYBm28CCpO8ioH5KDaQnOFE8Pc6Z7j6Dl_H3NMtZF4Pn5zeRaaaN_C47uT1vKDUnyC72OQ3yO3MIJsPTG3rIwZDqZuLzP3qE7OzBoCJbV1LiGLBSfNKCXb1lGdC4QSzmNjTqz779S42O1sbObylpWIfDGJdGNsRZaaRH45vrfavIlY21mMsOWCXXG9hDEePOig5rcs Received: from [152.15.95.105] by web45406.mail.sp1.yahoo.com via HTTP; Sun, 28 Feb 2010 12:42:09 PST X-Mailer: YahooMailClassic/9.2.12 YahooMailWebService/0.8.100.260964 Date: Sun, 28 Feb 2010 12:42:09 -0800 (PST) From: xu fei Subject: full text search index scan query plan changed in 8.4.2? To: pgsql-hackers@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.091 tagged_above=-10 required=5 tests=AWL=0.093, BAYES_40=-0.185 X-Spam-Level: X-Archive-Number: 201002/2419 X-Sequence-Number: 158762 Hi, hackers:=0A=0AI am testing the index used by full text search recently.= =0A=0AI have install 8.3.9 and 8.4.2=A0separately. =0A=0AIn 8.3.9, the quer= y plan is like:=0A=0Apostgres=3D# explain SELECT s.name as source , t.name = as target FROM element as s, element as t WHERE to_tsvector('testcfg',s.nam= e) =A0@@ to_tsquery('testcfg',replace(t.name,':','|'));=A0=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 ----------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------=A0=0ANested Loop =A0(cost=3D0.01..259.9= 2 rows=3D491 width=3D18)=A0=A0 =0A -> =A0Seq Scan on element t =A0(cost=3D0= .00..13.01 rows=3D701 width=3D9)=A0=A0 =0A -> =A0Index Scan using element_f= tsidx_test on element s =A0(cost=3D0.01..0.33 rows=3D1 width=3D9)=A0=A0 =A0= =A0 =A0 =0A Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::t= ext) @@ to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text,= '|'::text)))=0A(4 rows)=0A=0AI have index:=A0"element_ftsidx_test" gin (to= _tsvector('testcfg'::regconfig, name::text))=0A=0AThe same index and query = in 8.4.2:=A0=0A=0Apostgres=3D# explain SELECT s.name as source , t.name as = target FROM element as s, element as t WHERE to_tsvector('testcfg',s.name) = =A0@@ to_tsquery('testcfg',replace(t.name,':','|')) ;=A0=A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 = =A0 =A0 =A0 =A0 =A0 =A0 ---------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------=A0=0ANested Loop =A0(cost=3D0.32..3123= .51 rows=3D2457 width=3D18)=A0=A0 =0A -> =A0Seq Scan on element t =A0(cost= =3D0.00..13.01 rows=3D701 width=3D9)=A0=A0 =0A -> =A0Bitmap Heap Scan on el= ement s =A0(cost=3D0.32..4.36 rows=3D4 width=3D9)=A0=A0 =A0 =A0 =A0 Re= check Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquer= y('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))=A0= =A0 =A0 =A0 =A0 =0A -> =A0Bitmap Index Scan on element_ftsidx_test = =A0(cost=3D0.00..0.32 rows=3D4 width=3D0)=0A =A0 =A0 =A0 =A0 =A0 =A0 Index = Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@ to_tsquery('tes= tcfg'::regconfig, replace((t.name)::text,=A0':'::text, '|'::text)))=0A(6 ro= ws)=0A=0AWhy the query plans are different and why? Thanks!=0A=0AXu Fei=0A= =0A=0A