On 14/02/11 06:59, Gnanakumar wrote:
How can we boost performance of queries containing pattern matching characters?
QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
As it is clear from the above query, email is matched "partially and case-insensitively", which my application requirement demands.
Well, for that exact pattern you're not going to find an index that's much help. Do you really need something so wide-ranging though? The above will match all of the following:
us...@domain.com us...@sub.domain.com us...@domain.com.au us...@unrelated-domain.com us...@unrelated-domain.com.au us...@sub.domain.com.au us...@sub.unrelated-domain.com us...@sub.unrelated-domain.com.au us...@sub.unrelated-domain.completely-wrong.com Is that really what you are after? Or, did you just want to match: us...@domain.com us...@sub.domain.com -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
