openGauss is an open-source relational database management system (RDBMS) that originated from PostgreSQL. It is designed for high performance, high availability, and enterprise-grade features. Initially developed by Huawei, openGauss was later open sourced to the community.
Here is a common problem that developers may encounter: writing a dynamic SQL statement with a CASE expression that hardcodes a = ‘low’ instead of using a bind variable like = :var, as shown below:
SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’
Below is the query plan for this SQL statement, which takes 2.20 seconds to complete. The query performs a sequential scan (Seq Scan) on the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.
We can rewrite the CASE expression using the following syntax with multiple OR conditions.
select *
from employee
where (emp_salary < 1000 and
‘low’ = ‘low’ or
(not (emp_salary < 1000)) and
emp_salary > 100000 and
‘high’ = ‘low’ or
(not (emp_salary < 1000 or
emp_salary > 100000)) and
‘Normal’ = ‘low’);
If the emp_salary field is nullable, the SQL query should be written like this:
select *
from employee
where (emp_salary < 1000 and
‘low’ = ‘low’ or
((not (emp_salary < 1000)) or
emp_salary is null) and
emp_salary > 100000 and
‘high’ = ‘low’ or
((not (emp_salary < 1000 or
emp_salary > 100000)) or
emp_salary is null) and
‘Normal’ = ‘low’)
Here is the query plan for the rewritten SQL, which executes in 0.002 seconds. This is 1100 times faster than the original syntax. The new query plan utilizes a Bitmap Index Scan on the emp_salary index.
This type of rewrite can be achieved automatically using Tosska SQL Tuning Expert Pro for openGauss. There are other rewrites that deliver even better performance, but they are not suitable to discuss in this short article. I may explore them in more detail in a future blog post.
Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited
