Naoko Reeves <naokoreeves@gmail.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