标签： SQL tuning for PostgreSQL

在我的最新博客文章中，我将探讨针对使用 “NOT IN” 子查询的 SQL 语句的额外优化方案。
以下是一个带有 NOT IN 子查询的 SQL 示例。该语句从成绩表（grade）中查询记录，要求当员工表（employee）中 emp_id 大于 3000 时，grd_id 不匹配任何 emp_grade 值：

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

在我上一篇文章中，通过将原始查询改写为使用NOT EXISTS语句，在我的数据库环境中实现了最佳性能表现。然而需要注意的是，这种方法并非对所有数据库结构设计都普遍适用：

select *
  from grade g1
 where not (exists (select ‘x’
                      from employee b
                     where g1.grd_id = b.emp_grade and
                           b.emp_id > 3000))

现在我将列出其他高性能解决方案：

方案1——添加GROUP BY子句
通过添加GROUP BY子句，可促使优化器预先对员工表进行排序和哈希聚合处理：

方案2——添加COALESCE函数
通过添加COALESCE(b.emp_grade, b.emp_grade)，会阻止员工表(employee)使用潜在索引，导致该表被迫进行顺序扫描(Seq Scan)，从而改变原查询执行路径中的连接顺序：

方案3——替换字面值为子查询
通过将字面值3000替换为子查询(SELECT (3000)),会隐藏常量特性，从而阻止优化器在b.emp_id字段上使用索引：

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

以下是一个使用 NOT IN 子查询的 SQL 语句示例。该查询从 Grade 表中检索记录，条件是当 emp_id 大于 3,000 时，grd_id 不与 employee 表中的任何 grd_id 匹配：

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

当前执行计划耗时异常漫长，需要 50 分 10 秒 才能完成。

该查询计划显示，系统先在Employee表上对Emp_id > 3000进行顺序扫描，生成物化临时存储，随后用Grade表的Grd_id进行过滤。这种查询计划效率极低——因为当Grade表的5000行记录需要逐行处理时，需反复扫描Employee表约300万条记录。

为解决此问题，我将SQL语句重写为NOT (EXISTS …)形式：

select *
  from grade g1
 where not (exists (select ‘x’
                      from employee b
                     where g1.grd_id = b.emp_grade and
                           b.emp_id > 3000))

优化后的查询计划显示：现在通过嵌套循环连接（Nest Loop），以Grade表为驱动，配合Employee表上的索引扫描（emps_grade_inx） 进行操作。

重写后的SQL语句执行时间降至0.07秒，性能得到显著提升。

重写后的SQL比原始版本提速超过43,000倍。这类优化同样可通过Tosska SQL Tuning Expert Pro for PostgreSQL工具实现。下图展示了几种性能更优的SQL替代方案，但因篇幅所限不便在本文详述，也许我们可以稍后再讨论这个话题。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited