SQL优化案例分享 | PawSQL 近日推出 Lateral Join 重写优化算法

在上文Lateral 查询详解：概念、适用场景与普通 JOIN 的区别中介绍到，Lateral 查询是SQL中的一种连接方式，它允许FROM子句中的子查询引用同一FROM子句中前面的表的列。虽然这种特性提供了强大的表达能力，但在某些场景下可能导致性能问题。PawSQL优化器近日实现了一种针对特定类型Lateral Join的重写优化方案，以提升查询性能。

二、PawSQL Lateral Join 优化的原理

PawSQL优化器中的LateralQueryRewrite
类负责这一优化过程，其核心原理是：

1. 解关联转换：将Lateral子查询转换为非相关子查询，避免针对外部表的每一行重复执行内部查询。

2. 聚合下推：保留内部查询的聚合操作，但添加关联列到GROUP BY子句中，使其能独立执行。

3. 关联条件重定位：将原本在LATERAL子查询内部的关联条件移至外层查询的WHERE或JOIN条件中。

Lateral 查询重写优化必须满足以下条件：

1. Lateral子查询必须包含聚合函数（如SUM、AVG、COUNT等）(注1)

2. Lateral子查询不能包含LIMIT子句

3. Lateral子查询返回的行数应少于外部查询

4. 子查询和外部查询之间的关联必须是通过等值谓词（=）建立的

5. 关联谓词不能包含否定条件

6. 外部表引用必须来自单个表引用

注1：聚合并不是解关联的必要条件，非聚合Lateral查询的解关联在查询折叠中被优化。

二、案例分析

让我们分析一个实际案例，看看PawSQL优化器如何应用Lateral Join 重写优化的。

2.1 原始SQL

select * 
from customer, lateral (
    select SUM(o_totalprice)
    from orders
    where o_custkey= c_custkey
      and o_orderdate='1998-03-03'
)as total

在这个查询中：

• 外部查询从customer表获取所有行

• 内部LATERAL查询计算每个客户在日期1998-03-03 的订单总金额

• 关联条件是o_custkey = c_custkey
  （等值谓词）

2.2 重写后的SQL

select/*QB_1*/*from customer,(
    select/*QB_2*/SUM(o_totalprice), o_custkey
    from orders
    where o_orderdate='1998-03-03'
    group by o_custkey    
)as total 
where total.o_custkey= c_custkey

2.3 重写优化分析

1. 解关联操作：

• 内部查询不再引用外部customer
  表的列，变成了独立子查询

• 移除了LATERAL关键字

2. 列添加与GROUP BY：

• 在内部查询的SELECT列表中添加了o_custkey
  

• 在内部查询中添加了GROUP BY o_custkey
  子句

3. 关联条件重定位：

• 原本在LATERAL子查询内的关联条件o_custkey = c_custkey
  被移到了外层的WHERE子句

4. 保留非关联条件：

• 非关联的过滤条件o_orderdate = '1998-03-03'
  保留在内部查询中

三、性能提升机制

根据执行计划比较，这一重写带来了显著的性能提升（约2172.57%）。

优化前

Nested Loop  (cost=8.44..127635.00 rows=15000 width=223) (actual time=0.054..36.043 rows=15000 loops=1)
->  Seq Scan on customer  (cost=0.00..510.00 rows=15000 width=191) (actual time=0.012..1.640 rows=15000 loops=1)
->  Aggregate  (cost=8.44..8.45 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=15000)
      ->  Index Scan using ord_idx3 on orders  (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=15000)
            Index Cond: ((o_custkey = customer.c_custkey) AND (o_orderdate = '1998-03-03'::date))
Planning Time: 2.984 ms
Execution Time: 36.597 ms

• 对customer表进行全表扫描（Seq Scan）

• 对每个customer行执行一次聚合操作（共15000次循环）

• 每次聚合操作都使用索引扫描orders表（共15000次）

优化后

Nested Loop  (cost=0.71..441.97 rows=62 width=227) (actual time=0.367..1.586 rows=59 loops=1)
  ->  GroupAggregate  (cost=0.42..6.59 rows=62 width=36) (actual time=0.068..0.136 rows=59 loops=1)
        Group Key: orders.o_custkey
        ->  Index Only Scan using pawsql_idx1255915527 on orders  (cost=0.42..5.50 rows=62 width=12) (actual time=0.061..0.074 rows=59 loops=1)
              Index Cond: (o_orderdate = '1998-03-03'::date)
              Heap Fetches: 0
  ->  Index Scan using customer_pkey on customer  (cost=0.29..7.01 rows=1 width=191) (actual time=0.024..0.024 rows=1 loops=59)
        Index Cond: (c_custkey = orders.o_custkey)
Planning Time: 0.656 ms
Execution Time: 1.623 ms

• 首先对orders表进行一次性聚合，按o_custkey分组，避免了重复聚合
  

• 聚合子查询充分利用新推荐索引PAWSQL_IDX1255915527，且结果集只返回59行结果
  

• 然后通过嵌套循环join与customer表关联（小表 join 大表）

• 关联customer表时使用其主键，大大减少了表扫描次数

四、结论

PawSQL优化器的Lateral Join 重写优化通过将相关Lateral查询转换为非相关子查询，有效地改变了查询的执行策略，大幅提高了性能。这一优化特别适用于包含聚合操作的Lateral 查询，通过重写可以减少冗余计算，更好地利用索引，并允许数据库引擎选择更优的执行计划。

在设计复杂查询时，了解这种优化机制可以帮助开发人员编写更加高效的SQL语句。同时，对于已有的使用Lateral 的查询，可以考虑使用类似PawSQL的优化工具来提升性能。

往期文章精选

1. SQL优化案例分享 | PawSQL 优化器谓词下推算法实现分析

2. SQL优化案例分享 | 从321秒到0.2秒的性能飞跃 — TPCH查询优化实战

3. 下载破万！国产软件PawSQL Advisor凭何成为开发者心中的 SQL 优化利器？

4. PawSQL for SQLServer：SQL Server 的SQL优化、审核、性能巡检

点击关注 👇👇👇