Click to view Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss 1.8.1 Release Notes
Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – System Requirements
Before installing Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss®, please make sure your system meets the following minimum hardware and software requirements:
| CPU | 1.8 GHz Processor |
| Memory | 2 GB of RAM minimum, 4 GB of RAM recommended |
| Hard Disk Space | 200 MB of disk space for installation |
| Operating System | Microsoft Windows® 7 Microsoft Windows® 10 Microsoft Windows® 11 |
| .NET Framework | Microsoft .NET Framework 4.6.2 |
| Database Server | openGauss 5.0 or higher |
Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss 1.6.0 Release Notes
How to Tune SQL Statement with CASE Expression for openGauss ?
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
How to Tune SQL with “Exists subquery” for openGauss ?
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.
One common problem that often arises in most database SQL optimizers is the inefficient handling of EXISTS subqueries. Below is an example of a SQL statement with an EXISTS subquery. This query retrieves records from the emp_subsidiary table where the salary, grade_id, and department_id match those in the employee table.
select *
from emp_subsidiary a
where exists (select ‘x’
from employee b
where a.emp_salary = b.emp_salary and
b.emp_grade = a.emp_grade and
a.emp_dept = b.emp_dept)
Here is the query plan; it takes 10.35 seconds to complete.
The query plan shows a Hash Inner Join with a sequential scan of the emp_subsidiary table and a sequential scan of the employee table. The performance of this query plan seems reasonable and cannot be improved without introducing new indexes.
Someone might suggest rewriting the SQL to use an IN statement to see if the query can be improved, as shown below:
select *
from emp_subsidiary a
where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
b.emp_grade,
b.emp_dept
from employee b)
This rewritten SQL does not generate a new query plan, and no performance improvement is observed. To address the issue, let me further rewrite the original SQL statement into a subquery with an INTERSECT statement. Clearly, this rewritten SQL introduces an additional operation in the subquery, where the employee table intersects with the emp_subsidiary table. This means only the intersected set of records (based on emp_salary, emp_grade, and emp_dept) between the employee and emp_subsidiary tables will be returned. As a result, the result set of the subquery is drastically reduced, significantly lowering the high-cost Hash Aggregate operation.
select *
from emp_subsidiary a
where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
b.emp_grade,
b.emp_dept
from employee b
intersect
select a.emp_salary,
a.emp_grade,
a.emp_dept
from emp_subsidiary a)
The rewritten SQL takes 4 seconds to complete, with the following query plan. The new plan appears slightly more complex compared to the original query plan. However, the cost of the Hashed Aggregate operation is significantly lower than in the original SQL, and the actual execution time reflects this improvement.
This syntax rewrite method is only useful if the overhead introduced by the INTERSECT operation is offset by the significant reduction in the result set of the subquery achieved through the INTERSECT operation.
The rewritten SQL is more than 2 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for openGauss.
Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited
How to Tune SQL with “IN subquery” for openGauss ?
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. The SQL optimizer in openGauss is based on PostgreSQL but has been significantly enhanced and modified to improve performance, scalability, and support for enterprise workloads. While the exact version of PostgreSQL from which openGauss inherited its initial codebase is not explicitly stated in the official documentation, it is widely believed that openGauss originated from PostgreSQL 9.2.4. As a result, some legacy SQL optimizer issues from older PostgreSQL versions may still persist in the current version of openGauss.
One common problem that often arises in less mature SQL optimizers involves the inefficient handling of IN subqueries. Below is an example of a SQL statement with an IN subquery. This query retrieves records from the employee table where the salary matches the salary in the emp_subsidiary table for rows with the same emp_id.
select *
from employee a
where a.emp_salary in (select b.emp_salary
from emp_subsidiary b
where a.emp_id = b.emp_id)
Here is the query plan; it takes 7.2 seconds to complete.
The query plan shows a sequence scan of the employee table and an index scan of the emp_subsidiary table. However, this query is not suitable for a scenario where the ratio of employee to emp_subsidiary is 30:1. If openGauss had a more robust SQL optimizer, it should include an internal SQL syntax rewrite mechanism to transform the IN statement into a JOIN or EXISTS statement, allowing for the exploration of more efficient query plans.
Let me rewrite the SQL using an EXISTS statement, as shown below:
select *
from employee a
where exists (select ‘x’
from emp_subsidiary b
where a.emp_salary = b.emp_salary and
a.emp_id = b.emp_id)
This rewritten SQL takes 0.34 seconds to complete and uses a Merge Semi Join in the query plan—a plan that cannot be generated with the original IN syntax. We can see that the cost of the rewritten SQL is significantly lower than that of the original SQL statement. This indicates that the Merge Semi Join plan is not within the plan space explored by openGauss during the SQL optimization stage for the original query
The rewritten SQL is more than 20 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for openGauss.
Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited