Обсуждение: Regex Query Index question
[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....
co-worker suggested me to use chr(40) instead so I tried this:
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\' || chr(40) || '123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}' || '7890')
No success...
Also { and period doesn't seems to use index either.... but } ) [ ] $ # works.
Could you guide me to right direction for me please?
Thank you very much for your time in advance.
Naoko Reeves
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
Naoko Reeves <naokoreeves@gmail.com> 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
Hello,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....
Particularly with RegEx you want to tell people what you are trying to do and not just give the expressions themselves.co-worker suggested me to use chr(40) instead so I tried this:
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\' || chr(40) || '123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}' || '7890')
No success...
Also { and period doesn't seems to use index either.... but } ) [ ] $ # works.Could you guide me to right direction for me please?
Naoko Reeves <naokoreeves@gmail.com> writes: > 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), Oh --- there's your problem. In 8.4 and earlier, we don't trust \( to be a literal character in a regex pattern, because it's not a literal if you have regex_flavor set to 'basic'. 9.0 and up removed that option, so it works as-expected in newer versions. regards, tom lane
Now that I read more closely the alternation is actually concatenation. My point still stands but your issue is that youhave not created a functional index on the decryption result of the encrypted phone number. PostgreSQL does not knowthat the decrypted phone number is equivalent to the unencrypted field. It only can look at expressions to determinewhether an index is usable - not values. A table can have more than one index. David J. On Aug 11, 2011, at 19:58, David Johnston <polobo@yahoo.com> wrote: > >> >> Not testing here but... and ignore whitespace >> >> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \d{3} [ -\s ] \d{4} )$' >> > Some tweaks needed but seriously consider dropping RegEx and going the functional index route. > >> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \s* \d{3} \s* [ -\s ] \s* \d{4} )$' > > Added some extra white-space checking but again not tested. > > You can probably find better/more flexible expressions online. > > David J.
> > Not testing here but... and ignore whitespace > > '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \d{3} [ -\s ] \d{4} )$' > Some tweaks needed but seriously consider dropping RegEx and going the functional index route. > '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \s* \d{3} \s* [ -\s ] \s* \d{4} )$' Added some extra white-space checking but again not tested. You can probably find better/more flexible expressions online. David J.