Re: Regex Query Index question

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