UPPER vs. ILIKE
19 April 2010
I have a Zope web app that uses hand coded SQL (PostgreSQL) statements. Similar to the old PHP. My only excuse for not using an ORM was that this project started in 2005 and at the time SQLAlchemy seemed like a nerdy mess with more undocumented quirks than it was worth losing hair over.
Anyway, several statements use
ILIKE to get around the problem of making things case insensitive. Something like the Django ORM uses
UPPER to get around it. So I wonder how much the
ILIKE statement slows down compared to
UPPER and the indexed equal operator. Obviously, neither
UPPER can use an index.
Long story short, here are the numbers for selecting on about 10,000 index records:
# ONE EXPLAIN ANALYZE SELECT ... FROM ... WHERE name = 'Abbaye'; Average: 0.14 milliseconds # TWO EXPLAIN ANALYZE SELECT ... FROM ... WHERE UPPER(name::text) = UPPER('Abbaye'); Average: 18.89 milliseconds # THREE EXPLAIN ANALYZE SELECT ... FROM ... WHERE name ILIKE 'Abbaye'; Average: 24.63 milliseconds
First of all, the conclusion is to use
UPPER instead of
ILIKE if you don't need to do regular expressions. Secondly, if at all possible try to use the equal operator first and foremost and only reside on the case insensitive one if you really need to.
Lastly, in PostgreSQL is there a quick and easy to use drop in alternative to make an equality operatorish thing for varchars that are case insensitive?
There is an answer to the question just above: citext