Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id AD0E0633092
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Tue,  9 Feb 2010 05:30:20 -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 14341-03
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Tue,  9 Feb 2010 09:30:05 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pz0-f174.google.com (mail-pz0-f174.google.com
	[209.85.222.174])
	by mail.postgresql.org (Postfix) with ESMTP id CDD7F632FFF
	for <pgsql-general@postgresql.org>;
	Tue,  9 Feb 2010 05:30:09 -0400 (AST)
Received: by pzk4 with SMTP id 4so65092pzk.1
	for <pgsql-general@postgresql.org>;
	Tue, 09 Feb 2010 01:30:08 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:message-id:subject
	:from:to:content-type;
	bh=DGbH8KlbtoaoBlMmTCaD+gUdE9oyXD3RGxSWFPfboTs=;
	b=pFoDCqGf5dkEqeSTG7/4oQ4L6Y9YJig3afoJO23hcSaYPeC7Wf6nDNq2ZgGF7CMiYw
	dzKBu8OYEWk7GRafavCBz6gkl5juLUMGsQcnMfPrgDMCnvIu9uFgcLmYiujCHvvk+WyJ
	wwnYWk6jaeRxifv6qCcxPzw4xoXl94YJmZMpc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=vfNVZYXmwcNI7QrK6EfEcyfKhpAFTPZPqddSM1+f9/EglP2YE+6EVWp9N4xN1+dxku
	Un7lgVOAj3y+AyFHuADvX2e9EZPsNOavGsEpowhlbRYwG/0YEPHWW9DRjbJ0OlF2oMiA
	gttFIl6x6lfpSC9JuhnSs6mezufbNAYTQii78=
MIME-Version: 1.0
Received: by 10.143.21.18 with SMTP id y18mr5117558wfi.58.1265701436237; Mon,
	08 Feb 2010 23:43:56 -0800 (PST)
Date: Tue, 9 Feb 2010 13:43:56 +0600
Message-ID: <2a7905441002082343g1dd0c8ebg5a27f7d44ec9fdb1@mail.gmail.com>
Subject: index is not using
From: AI Rumman <rummandba@gmail.com>
To: pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary=00504502cc2f7fa758047f261495
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.426 tagged_above=-10 required=5 tests=AWL=-0.428,
	BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201002/325
X-Sequence-Number: 159101

--00504502cc2f7fa758047f261495
Content-Type: text/plain; charset=ISO-8859-1

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.

--00504502cc2f7fa758047f261495
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>I have created a index</div>
<div>create index leadaddress_phone_idx on=A0 leadaddress(regexp_replace((p=
hone)::text, &#39;[^0-9]*&#39;::text, &#39;&#39;::text, &#39;g&#39;::text))=
;</div>
<div>=A0</div>
<div>But the index is not using.</div>
<div>=A0</div>
<div>explain select * from leadaddress where regexp_replace(phone,&#39;[^0-=
9]*&#39;,&#39;&#39;,&#39;g&#39;) like &#39;%2159438606&#39;;<br>=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=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=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=A0=A0=A0=A0=A0=A0=A0=A0 <br>
---------------------------------------------------------------------------=
-----------------------------<br>=A0Seq Scan on leadaddress=A0 (cost=3D1000=
00000.00..100009699.81 rows=3D1 width=3D97)<br>=A0=A0 Filter: (regexp_repla=
ce((phone)::text, &#39;[^0-9]*&#39;::text, &#39;&#39;::text, &#39;g&#39;::t=
ext) ~~ &#39;%2159438606&#39;::text)</div>

<div>=A0</div>
<div>Could anyone please tell me why? I analyzed the table after index crea=
tion.</div>
<div><br>=A0</div>

--00504502cc2f7fa758047f261495--