Hi,
How can we boost performance of queries containing pattern matching
characters? In my case, we're using a percent sign (%) that matches any
string of zero or more characters.
QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
EMAIL column is VARCHAR(256).
As it is clear from the above query, email is matched "partially and
case-insensitively", which my application requirement demands.
In case, if it were a full match, I could easily define a functional INDEX
on EMAIL column (lower(EMAIL)) and I could rewrite my DELETE where criteria
like lower(EMAIL) = 'someemail@domain.com'.
MYTABLE currently contains 2 million records and grows consistently.
Regards,
Gnanam