Beware of upper()/lower() in PostgreSQL

Happy new year to all of you.

Today i’m going into performance impacts which can come from upper()/lower(). If you were already aware of these things, you won’t be impressed, but if you’re like me, optimizing your setup which is 5 years old, you will be amazed.

So i was upgrading my PowerDNS setup for my new customer interface, when i stumpled upon this:

This query is performed on a view which is mapping my ruby DataMapper Models into one that is by default usable by PowerDNS. After i’ve killed the upper() (god know’s why i’ve placed it there 5 years ago), the query looks like this:

Seem’s like i’ve just made a timewarp from 3.5ms to 0.3ms querytime.

What did we learn? Audit and Benchmark old stuff once in a while, or at least when you’ve learned a lot since you’ve last touched it.

Flattr me!

Tell your friends!