Do you train Kung Fu?
Or know someone who does?
Then check out KungFuPeople.com
Mobile version of this pageWho was logged in during a Django exception
Next:
Word Whomp solvers love Crosstips
Related blogs
PostgreSQL, MySQL or SQLiteAnti-spamming email harvesting
Creating a user for postgresql
Adding a year in PostgreSQL
Migrating with South on a field that uses auto_now_add=True
How I made my MongoDB based web app 10 times faster
Fastest "boolean SQL queries" possible with Django
Regular Expressions in Javascript cheat sheet
\b in Python regular expressions
\B in Python regular expressions
Python regular expression tester
Are you a web developer? Then VisiBone is for you
Quick PostgreSQL optimization story
Date formatting in python or in PostgreSQL
Integer division in programming languages
Running simple SQL commands on the command line
pg_class to check if table exists
List of casts in PostgreSQL
Just Oracle and IBM?
ALTER TABLE patch
Date formatting in Python or in PostgreSQL (part II)
Sorting transform function in PostgreSQL
Why bother with MySQL...
To sub-select or not sub-select in PostgreSQL
Speed test between django_mongokit and postgresql_psycopg2
Optimization of getting random rows out of a PostgreSQL in Django
Connecting with psycopg2 without a username and password
Related by category
Web development
UPPER vs. ILIKE
19th of 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 ILIKE or 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?
FYI UPPER can use an index if you create the index to be uppercase, i.e.
CREATE INDEX foo on bar (upper(col));
after which a "select * from bar where upper(col) = "PETER"; should be alot faster.
Really?! I sort of not surprised. PostgreSQL rocks! I'll throw that in if and when I replace my ILIKE statements (that don't use wildcards) with UPPER ones.