Re: Regex Query Index question

Tom,
Thank you for your quick reply. Data start with "(123" only returns 28
records where as phone number start with"[123" returns 1.
Changed the data so that both will return 1 row.

One with "(999" query takes about 30 seconds (30983ms) without index.
One with "[999" take about 28 ms with index.

Yes, standard_conforming_strings is ON.
Also forgot to mentioned the version:
select version() >> "PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
32-bit"

Thank you very much for your time.

Naoko Reeves

On Thu, Aug 11, 2011 at 3:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Naoko Reeves <naokoreeves(at)gmail(dot)com> writes:
> > I have query phone number in database as follows:
> > [123) 456-7890
> > (123) 456-7890
>
> > When I query like this:
>
> > SELECT * FROM phone
>
> > WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' ||
> '[-\s\)]{0,2}'
> > || '7890')
>
> > it use Index but if I query like this (notice first character is
> > open parenthesis instead of open square blacket ) :
>
> > SELECT phn_fk_key FROM phn WHERE
>
> > phn_fk_table = 14
>
> > AND llx_decrypt(phn_phone_enc) ~ ('^\(123' || '[-\s\)]{0,2}' || '456' ||
> > '[-\s\)]{0,2}' || '7890')
>
> > It doesn't use Index....
>
> Probably it thinks the index isn't selective enough for that case. How
> many entries are there starting with "(123"?
>
> (BTW, I assume you've got standard_conforming_strings turned on, else
> there are some other issues with these backslashes ...)
>
> regards, tom lane
>

--
Naoko Reeves
http://www.anypossibility.com/