[ SQL performance Killers ] — Implicit Conversions
An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using Nazar.io) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.
This is the third post of the [SQL performance killers series], the first two posts were [Applying functions to column data] and [IN vs EXISTS].
This time I want to show you how leaving the responsibility of data type conversions to the database — implicit conversion — can impact your database/application performance.
When your parameter has a different type from the column you are comparing to, the database tries to make an implicit conversion so that the command won't return an error. For example, when comparing a number with a string, it will try to convert the string to a number implicitly.
Although it makes things convenient for the developers, it can bring important performance impact. One of this performance issues happened with one of our customers who had just partitioned a huge table. The table was a central part of his database and accessed by most of the "slow queries". After partitioning the table it was expected to have a big performance improvement but it did't happen.
After analyzing the queries and its execution plan, we noticed that there was no partition elimination and the queries were accessing every table partitions. The problem was that the column used for the partition key was a "timestamp with time zone" and the parameter received by the query was a string.
In this scenario PostgreSQL was converting the string to a “timestamp without time zone” what was preventing partition elimination.
After applying explicit conversion to the queries, it started doing partition elimination and the expected performance gains finally came!
“80% of the performance issues are caused by improperly coded database applications.” — Craig S. Mullins
