Quick PostgreSQL optimization story
11 March 2006
There are several ways to do case insensitive string matching in SQL. Here are two ways that I've tried and analyzed on a table that doesn't have any indices.
( LOWER(u.first_name) = LOWER('Lazy') OR LOWER(u.last_name) = LOWER('Lazy') OR LOWER(u.first_name || u.last_name) = LOWER('Lazy') )
( u.first_name ILIKE 'Lazy' OR u.last_name ILIKE 'Lazy' OR u.first_name || u.last_name ILIKE 'Lazy' )
A potentially third option is to make sure that the parameters sent to the SQL code is cooked, in this case we make the parameter into lower case before sent to the SQL code
( LOWER(u.first_name) = 'lazy' OR LOWER(u.last_name) = 'lazy' OR LOWER(u.first_name || u.last_name) = 'lazy' )
Which one do you think is fastest?
The results are:
Option 1: 2.0ms - 2.5ms (average 2.25ms) Option 1b: 2.0ms - 2.1ms (average 2.05ms) Option 2: 1.7ms - 2.0ms (average 1.85ms)
ILIKE operator method is the fastest. Not only is it faster, it also supports regular expressions.
I've always thought that the
ILIKE were sinfully slow (yet useful when time isn't an issue). I should perhaps redo these tests with an index on the