Here the following is a simple SQL statement with a CASE expression syntax.
SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
END = :a
The following are the query plans for this SQL statement, which completes in 1.01 seconds with the bind variable ‘:a’ equal to ’low’. The query involves a Full Table Scan of the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.
We can refactor the CASE expression into the following syntax using multiple OR conditions
select *
from employee
where (emp_salary < 1000 and
‘low’ = :a or
((not (emp_salary < 1000)) or
emp_salary is null) and
emp_salary > 100000 and
‘high’ = :a)
Rewriting multiple OR conditions with an AND operation (e.g., ‘low’ = :a) can efficiently disable unnecessary data searches on the EMPLOYEE table.
Here is the query plan of the rewritten SQL and the speed is 0.005 seconds. It is around 200 times better than the original syntax. The new query plan shows an Bitmap Index Scan on emp_salary index.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for PostgreSQL automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.
Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited
