Ka Ming Ng – 珠海图思科软件有限公司

05/06/2025

在 PostgreSQL 中优化含 ‘IN’ 子查询的 SQL 语句有哪些方法？

以下是我近期博客中的一条 SQL 语句。有读者询问在子查询的选择列表添加 +0（例如 b.emp_grade+0）对性能的影响。我很欣喜地得知，这个 +0 解决方案大约在 20 年前首次由我提出，用于解决特定子查询问题。不过该方法现已被 COALESCE(b.emp_grade, b.emp_grade) 替代——此举既能规避数据类型检测，又可预防意外错误。

今天，我想进一步探讨利用 IN 子查询优化 SQL 的其他解决方案。

select *
from grade
where grd_id in (select b.emp_grade
from employee b
where b.emp_id > 3000000)

以下截图展示了由 Tosska SQL Tuning Expert Pro 通过’仅重写’选项和 ‘SQL调优智能=2’ 生成的替代SQL方案。我将分享更多SQL重写方法，全面展示各种可能方案，期待以SQL调优技术的精妙艺术激发读者的探索热情。

以下虽不深入探讨具体SQL重写语句细节，但我很乐意分享其余查询重写方案及其对应的执行计划：

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

27/05/202527/05/2025

如何对 PostgreSQL 中的“IN 子查询”进行 SQL 调优 ?

以下是一个使用 IN 子查询的 SQL 语句示例。该查询从 grade 表中检索所有 grd_id 匹配 employee 表中 emp_id 大于 3,000,000 的记录的 emp_grade 的数据：

select *
from grade
where grd_id in (select b.emp_grade
from employee b
where b.emp_id > 3000000)

查询的执行计划显示，该语句耗时 12.6 秒。

该查询计划显示，优化器采用了嵌套循环（Nested Loop）连接方式：先对 grade 表进行全表顺序扫描（Sequential Scan），再对 employee 表进行索引扫描（Index Scan）。然而，这种执行逻辑会导致每个 grade 表的记录都会触发一次针对 employee 表中 emp_id > 3,000,000 条件的索引扫描。虽然 employee 表总记录约 400 万条，但满足 emp_id > 3,000,000 的记录不足 100 万条。

为解决此问题，我通过在子查询中增加 GROUP BY 子句重写了 SQL：

select *
from grade
where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000
                   group by b.emp_grade)

修改后的查询计划发生以下关键变化：

GROUP BY 子句强制子查询优先执行.
通过索引扫描快速筛选出 employee 表中 emp_id > 3,000,000 的记录.
对 emp_grade 列进行分组操作，消除重复值。
将 grade 表与已分组的 emp_grade 子集通过哈希表高效关联。

优化后的 SQL 执行时间降至 0.54 秒，性能显著提升。

优化后的 SQL 语句比原始版本性能提升超过 23 倍。此类优化也可以通过 Tosska SQL Tuning Expert Pro for PostgreSQL 工具实现。在下方的截图中，虽然展示了其他性能更优的 SQL 改写方案，但受限于篇幅无法在此详述——我们后续可以进一步探讨这一主题。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

14/05/2025

如何对PostgreSQL中使用CASE表达式的SQL语句进行调优？

以下是一个包含CASE表达式语法的简单SQL语句：

SELECT *
FROM   employee
WHERE
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
       END = :a

以下是该SQL语句的查询计划（当绑定变量:a等于’low’时，执行时间为1.01秒）。由于CASE表达式无法利用emp_salary索引，查询需要对EMPLOYEE表进行全表扫描。

我们可以通过使用多个OR条件将CASE表达式重构为以下语法：

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)

通过将多个OR条件与AND操作（例如’low’ = :a）结合，可以有效禁用对EMPLOYEE表的不必要数据搜索。

以下是重构后SQL的查询计划（执行速度提升至0.005秒），性能比原始语法快约200倍。新的查询计划显示使用了emp_salary索引的位图索引扫描（Bitmap Index Scan）。

这类SQL重写可以通过Tosska SQL Tuning Expert for PostgreSQL自动完成。实际上，还存在其他性能更优的改写方法，但由于篇幅限制无法在此详述，后续可能会在我的博客中进一步探讨。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

29/04/2025

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss 1.6.0 发行说明

单击以查看Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss 1.6.0 发行说明

10/04/2025

Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL 1.1.0 发行说明

单击以查看Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL 1.1.0 发行说明

10/04/2025

Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL® – 系统需求

在安装 Tosska SQL Tuning Expert Pro (TSEP Pro™) for PostgreSQL® 之前, 请确保您的系统满足以下最低硬件和软件要求:

CPU	1.8 GHz 处理器
内存	最小2 GB内存，建议4 GB内存
硬盘空间	用于安装的200 MB硬盘空间
操作系统	Microsoft Windows 7 Microsoft Windows 10 Microsoft Windows 11
.NET Framework	Microsoft .NET Framework 4.5.2
数据库服务器	PostgreSQL 数据库 9.0或更高版本

06/03/2025

如何为 openGauss 调优带有 CASE 表达式的 SQL 语句 ?

openGauss 是一个开源的关系型数据库管理系统（RDBMS），起源于 PostgreSQL。它专为高性能、高可用性和企业级功能而设计。openGauss 最初由华为开发，后来开源给社区。

以下是开发人员可能会遇到的一个常见问题：编写动态 SQL 语句时，在 CASE 表达式中硬编码了 a = ‘low’，而不是使用绑定变量 = :var，如下所示：

SELECT *
FROM   employee
WHERE
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
         ELSE ‘Normal’
       END = ‘low’

以下是该 SQL 语句的查询计划，其执行时间为 2.20 秒。由于 CASE 表达式无法利用 emp_salary 索引，查询对 EMPLOYEE 表执行了顺序扫描（Seq Scan）。

我们可以使用以下带有多个 OR 条件的语法来重写 CASE 表达式。

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’);

如果 emp_salary 字段可为空（nullable），SQL 查询应按照以下方式编写：

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’)

以下是重写后的 SQL 查询计划，其执行时间为 0.002 秒，比原始语法快了 1100 倍。新的查询计划使用了针对 emp_salary 索引的位图索引扫描（Bitmap Index Scan）。
这种重写可以通过 Tosska SQL Tuning Expert Pro 工具为 openGauss 自动实现。还有一些其他重写方法可以提供更好的性能，但由于篇幅限制，本文不适合详细讨论。我可能会在未来的博客文章中进一步探讨这些方法。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited

22/01/2025

如何为 openGauss 调优带有“EXISTS 子查询”的 SQL ？

openGauss 是一种开源的关系型数据库管理系统（RDBMS），它起源于 PostgreSQL。openGauss 旨在提供高性能、高可用性和企业级功能。最初由华为开发，后来被开源给社区。

在大多数数据库的 SQL 优化器中，一个常见问题是对 EXISTS 子查询的低效处理。以下是一个带有 EXISTS 子查询的 SQL 语句示例。该查询从 emp_subsidiary 表中检索与 employee 表中的 salary、grade_id 和 department_id 匹配的记录。

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)

以下是查询计划；完成该查询需要 10.35 秒。

查询计划显示了一个 Hash Inner Join，其中包含对 emp_subsidiary 表的顺序扫描（sequential scan）和对 employee 表的顺序扫描。该查询计划的性能看起来是合理的，如果不引入新的索引，性能将无法进一步提升。

有人可能会建议将 SQL 重写为使用 IN 语句，以查看查询是否可以改进，如下所示：

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)

重写后的 SQL 并未生成新的查询计划，且未观察到性能提升。为了解决这个问题，让我进一步将原始 SQL 语句重写为带有 INTERSECT 语句的子查询。

显然，重写后的 SQL 在子查询中引入了一个额外的操作，即 employee 表与 emp_subsidiary 表进行交集运算。这意味着只有 employee 表和 emp_subsidiary 表之间基于 emp_salary、emp_grade 和 emp_dept 的交集记录会被返回。因此，子查询的结果集大幅减少，从而显著降低了高成本的 Hash Aggregate 操作。

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)

重写后的 SQL 需要 4 秒完成，其查询计划如下。与原始查询计划相比，新计划略显复杂。然而，Hashed Aggregate 操作的成本显著低于原始 SQL，实际执行时间也反映了这一改进。

这种语法重写方法只有在 INTERSECT 操作引入的开销被其显著减少的子查询结果集所抵消时才有用。

重写后的 SQL 比原始 SQL 快 2 倍以上。这种优化也可以通过使用 Tosska SQL Tuning Expert 在 openGauss 中实现。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – 珠海图思科软件有限公司

14/01/2025

如何为 openGauss 调优带有“IN 子查询”的 SQL ？

openGauss 是一种开源的关系型数据库管理系统（RDBMS），它起源于 PostgreSQL。openGauss 旨在提供高性能、高可用性和企业级功能。最初由华为开发，后来被开源给社区。openGauss 的 SQL 优化器基于 PostgreSQL，但经过了显著的增强和修改，以提升性能、可扩展性并支持企业级工作负载。虽然官方文档中没有明确说明 openGauss 是从哪个 PostgreSQL 版本继承的初始代码库，但普遍认为 openGauss 起源于 PostgreSQL 9.2.4。因此，当前版本的 openGauss 中可能仍然存在一些来自旧版 PostgreSQL 的遗留 SQL 优化器问题。

在不成熟的 SQL 优化器中，一个常见问题是对 IN 子查询的低效处理。以下是一个带有 IN 子查询的 SQL 语句示例。该查询从 employee 表中检索与 emp_subsidiary 表中 salary 匹配的记录，条件是两者的 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)

以下是查询计划；完成该查询需要 7.2 秒。

查询计划显示了对 employee 表的顺序扫描（sequence scan）和对 emp_subsidiary 表的索引扫描（index scan）。然而，这种查询不适合 employee 与 emp_subsidiary 比例为 30:1 的场景。如果 openGauss 拥有更强大的 SQL 优化器，它应该包含一个内部的 SQL 语法重写机制，将 IN 语句转换为 JOIN 或 EXISTS 语句，从而允许探索更高效的查询计划。>br>

下面，我将使用 EXISTS 语句重写 SQL，如下所示：

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)

重写后的 SQL 仅需 0.34 秒即可完成，并且在查询计划中使用了 Merge Semi Join——这是一种无法通过原始 IN 语法生成的计划。我们可以看到，重写后的 SQL 成本显著低于原始 SQL 语句。这表明，在 openGauss 对原始查询进行 SQL 优化时，Merge Semi Join 计划并未在其探索的计划空间内。

重写后的 SQL 比原始 SQL 快 20 倍以上。这种优化也可以通过使用 Tosska SQL Tuning Expert 在 openGauss 中实现。

Tosska SQL Tuning Expert Pro (TSEG Pro™) for openGauss® – 珠海图思科软件有限公司

19/12/202419/12/2024

Tosska DB Ace Enterprise (DBAO™) for Oracle 3.2.0 发行说明

单击以查看Tosska DB Ace Enterprise (DBAO™) for Oracle 3.2.0 发行说明