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:
user1@xxxxxxxxxx user2@xxxxxxxxxxxxxx user3@xxxxxxxxxxxxx user4@xxxxxxxxxxxxxxxxxxxx user5@xxxxxxxxxxxxxxxxxxxxxxx user3@xxxxxxxxxxxxxxxxx user4@xxxxxxxxxxxxxxxxxxxxxxxx user5@xxxxxxxxxxxxxxxxxxxxxxxxxxx user6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Is that really what you are after? Or, did you just want to match: user1@xxxxxxxxxx user2@xxxxxxxxxxxxxx -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
