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

PawSQL

发布于 2025-04-21 11:16:55

18400

代码可运行

文章被收录于专栏：PawSQL，在线的自动化SQL优化工具！PawSQL，在线的自动化SQL优化工具！

运行总次数：0

代码可运行

一、Lateral 查询语法介绍

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

二、PawSQL Lateral Join 优化的原理

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

解关联转换：将Lateral子查询转换为非相关子查询，避免针对外部表的每一行重复执行内部查询。
聚合下推：保留内部查询的聚合操作，但添加关联列到GROUP BY子句中，使其能独立执行。
关联条件重定位：将原本在LATERAL子查询内部的关联条件移至外层查询的WHERE或JOIN条件中。

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

Lateral子查询必须包含聚合函数（如SUM、AVG、COUNT等）(注1)
Lateral子查询不能包含LIMIT子句
Lateral子查询返回的行数应少于外部查询
子查询和外部查询之间的关联必须是通过等值谓词（=）建立的
关联谓词不能包含否定条件
外部表引用必须来自单个表引用

注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 重写优化分析

解关联操作：
- 内部查询不再引用外部customer表的列，变成了独立子查询
- 移除了LATERAL关键字
列添加与GROUP BY：
- 在内部查询的SELECT列表中添加了o_custkey
- 在内部查询中添加了GROUP BY o_custkey子句
关联条件重定位：
- 原本在LATERAL子查询内的关联条件o_custkey = c_custkey被移到了外层的WHERE子句
保留非关联条件：
- 非关联的过滤条件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