SQL with IN Subquery - Tosska Technologies Limited

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

Here is an example SQL that retrieves data from EMPLOYEE table with employee’s ID code in EMP_SUBSIDIARY table.

select * from employee a
where a.emp_id
in (select b.emp_id
from emp_subsidiary b)

Here the following are the query plan of this SQL, it takes 10.03 seconds to finish. The query plan is very simple since the syntax of the SQL is not complicated. The query plan shows a full table scan of EMPLOYEE and then nested to the index of EMPSB_EMP_ID, it looks like a good query plan, but I wonder if it is too expensive to have a full table scan of EMPLOYEE table?

In order to ask Oracle to consider other query plans, I added a dummy function Coalesce(b.emp_id,b.emp_id) in the subquery’s select list that artificially adding cost to the driving path from EMPLOYEE to EMP_SUBSIDIARY due to the index EMPSB_EMP_ID is disabled by this dummy function.

SELECT  *
FROM      employee a
WHERE  a.emp_id   IN (SELECT Coalesce(b.emp_id,b.emp_id)
                     FROM     emp_subsidiary b)

The rewritten SQL generates an adaptive query plan and a “nested loops” from EMPSB_EMP_ID to EMPLOYEE by EMPLOYEE_PK index. You can remove the steps in “Id” column with marked ‘-‘ from the plan to verify the result plan. The new plan now takes 4.13 seconds to finish only.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Tag: SQL with IN Subquery

How to Tune SQL with “IN subquery” for openGauss ?

How to Tune SQL with IN Subquery in Certain Environment for Oracle?