Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 12804632E12; Tue, 9 Feb 2010 05:34:47 -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 14897-10; Tue, 9 Feb 2010 09:34:31 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74]) by mail.postgresql.org (Postfix) with ESMTP id 47B9962F51F; Tue, 9 Feb 2010 05:34:06 -0400 (AST) Received: from [192.168.10.3] (ASte-Genev-Bois-153-1-63-158.w81-249.abo.wanadoo.fr [81.249.213.158]) by sd-17937.dedibox.fr (Postfix) with ESMTPA id D81853B8F5; Tue, 9 Feb 2010 10:34:04 +0100 (CET) Message-ID: <4B712C0C.6040906@lelarge.info> Date: Tue, 09 Feb 2010 10:34:04 +0100 From: Guillaume Lelarge User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1 MIME-Version: 1.0 To: AI Rumman CC: pgsql-performance@postgresql.org, pgsql-general@postgresql.org Subject: Re: index is not using References: <2a7905441002082343g1dd0c8ebg5a27f7d44ec9fdb1@mail.gmail.com> In-Reply-To: <2a7905441002082343g1dd0c8ebg5a27f7d44ec9fdb1@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.396 tagged_above=-10 required=5 tests=AWL=-0.397, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201002/326 X-Sequence-Number: 159102 Le 09/02/2010 08:43, AI Rumman a écrit : > I have created a index > create index leadaddress_phone_idx on > leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text, > 'g'::text)); > > But the index is not using. > > explain select * from leadaddress where > regexp_replace(phone,'[^0-9]*','','g') like '%2159438606'; > QUERY > PLAN > -------------------------------------------------------------------------------------------------------- > Seq Scan on leadaddress (cost=100000000.00..100009699.81 rows=1 width=97) > Filter: (regexp_replace((phone)::text, '[^0-9]*'::text, ''::text, > 'g'::text) ~~ '%2159438606'::text) > > Could anyone please tell me why? I analyzed the table after index creation. > The index cannot be used if the filter is '%something' or '%somethingelse%'. I can only be used for 'this%'. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com