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@xxxxxxxxxxxxx> wrote:
Naoko Reeves <naokoreeves@xxxxxxxxx> writes:Probably it thinks the index isn't selective enough for that case. How
> 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....
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
