Quick PostgreSQL optimization story

11 March 2006   1 comment   Work

Mind That Age!

This blog post is 12 years old! Most likely, its content is outdated. Especially if it's technical.

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.

Option 1:

 LOWER(u.first_name) = LOWER('Lazy') OR 
 LOWER(u.last_name) = LOWER('Lazy') OR
 LOWER(u.first_name || u.last_name) = LOWER('Lazy')

Option 2:

 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

Option 1b:

 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)

Conclusion: the ILIKE operator method is the fastest. Not only is it faster, it also supports regular expressions.

I've always thought that the LIKE and ILIKE were sinfully slow (yet useful when time isn't an issue). I should perhaps redo these tests with an index on the first_name and last_name columns.



CREATE INDEX u_first_name_index ON u (lower(first_name));

or a variation thereof.

(Ah, I just noticed you wrote "without any indices". You probably already know this, then. I'll post it anyway - for the search engines.)

You could also use a shadow column maintained by a trigger, but that's an evil solution, only to be used in almost never-met circumstances.

Your email will never ever be published

Related posts

Squeezebox + Pandora 08 March 2006
Carbon XEmacs installed 14 March 2006
Related by Keyword:
When Docker is too slow, use your host 11 January 2018
Fastest way to match a filename's extension in Python 31 August 2017
Don't forget your sets in Python! 10 March 2017
Optimization of QuerySet.get() with or without select_related 03 November 2016
How to no-mincss links with django-pipeline 03 February 2016
Related by Text:
Be very careful with your add_header in Nginx! You might make your site insecure 11 February 2018
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
I'm Prolog 01 May 2007