SQL 优化之 OR 子句改写

SQL优化案例

前言

在常见的 SQL 优化技巧中，将 OR 语句改写为 UNION 是一种常见的手段。当 OR 连接的两个条件互斥时，可以改写为 UNION ALL，从而避免去重操作，进一步提升查询性能，主要原因在于优化器在处理 OR 时，很难充分利用已有的索引，导致查询计划可能退化为全表扫描或较低效的执行方式。

案例演示

数据准备

CREATE TABLE t1(id int, num int, dsc text, log_date text);
CREATE TABLE t2(id int, cnt int, change text, op_date text);

INSERT INTO t1 SELECT i, i%100, 'test'||1, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;
INSERT INTO t2 SELECT i, i%1000, 'now'||i, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;

CREATE INDEX ON t1(id);
CREATE INDEX ON t1(num);
CREATE INDEX ON t2(id);
CREATE INDEX ON t2(cnt);

ANALYZE t1;
ANALYZE t2;

单表 OR 查询

目前，PostgreSQL优化器对SQL中的OR子句过滤条件的优化能力较为有限。如果OR子句中的过滤条件仅涉及一张表，且所有过滤条件上均具备适当的索引，则优化器会为此类场景生成一个BitmapOr的Index Path。

explain analyze select * from t1 where t1.num = 1 or id =1;

Bitmap Heap Scan on t1  (cost=9.38..88.74 rows=101 width=25) (actual time=0.139..0.328 rows=100 loops=1)
  Recheck Cond: ((num = 1) OR (id = 1))
  Heap Blocks: exact=73
  ->  BitmapOr  (cost=9.38..9.38 rows=101 width=0) (actual time=0.119..0.120 rows=0 loops=1)
        ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.110..0.110 rows=100 loops=1)
              Index Cond: (num = 1)
        ->  Bitmap Index Scan on t1_id_idx  (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
              Index Cond: (id = 1)
Planning Time: 0.104 ms
Execution Time: 0.360 ms

多表 OR 查询

如果OR子句涉及多张表，优化器只能将该OR子句视为连接后的过滤条件，这可能导致SQL执行效率降低。例如：

explain analyze select * from t1 join t2 on t1.id = t2.id where (t1.num = 1 or t2.cnt = 2);

Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=2.476..6.215 rows=110 loops=1)
  Hash Cond: (t1.id = t2.id)
  Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
  Rows Removed by Join Filter: 9890
  ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.015..0.935 rows=10000 loops=1)
  ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=2.433..2.434 rows=10000 loops=1)
        Buckets: 16384  Batches: 1  Memory Usage: 704kB
        ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.009..0.885 rows=10000 loops=1)
Planning Time: 0.314 ms
Execution Time: 6.250 ms

可以看到，先是 t2 表全表扫描，基于 t2 表的扫描结果构建哈希表，然后全表扫描 t1 表，使用哈希条件t1.id = t2.id，在哈希表中匹配每一行，然后再过滤掉(t1.num = 1) OR (t2.cnt = 2)的条件，可以看到有9890 不符合条件。

上述OR子句被当作一个整体，优化器无法使用t1.num或者t2.cnt上的索引，导致t1与t2进行全表扫描。实际上，OR子句在逻辑上可以转化为UNION ALL，包含两个或多个分支的查询形式。

SQL 改写

大致改写逻辑主要关注后方的AND (t1.num != 1 OR (t1.num is NULL);，改写需要考虑 NULL 的三值逻辑。在 SQL 中，当字段值为 NULL 时，用等号 (=) 或不等号 (!=) 比较会返回 unknown。

如果直接使用t1.num != 1来排除已经被第一部分查询覆盖的行，那么当t1.num为 NULL 时，表达式t1.num != 1的结果不是 true，而是 unknown，因此这一行就会在第二个子查询中被过滤掉。

然而，在原始条件(t1.num = 1 OR t2.cnt = 2)中，如果t1.num为 NULL，但t2.cnt = 2为 true，该行实际上是应该返回的。

为了解决以上问题，在第二个子查询的 WHERE 条件中增加(t1.num != 1 OR (t1.num IS NULL)。

当t1.num为 NULL 时，(t1.num = 1)会返回 unknown，而t1.num IS NULL则会返回 true，从而整个 OR 表达式的结果为 true。

这样，就确保了那些t2.cnt = 2且t1.num为 NULL 的行不会因为 NULL 比较而被错误地过滤掉，而能够和原始查询保持一致。

EXPLAIN ANALYZE
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.num = 1
UNION ALL
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.cnt = 2 AND (t1.num != 1 OR t1.num IS NULL);

Append  (cost=85.48..411.14 rows=110 width=51) (actual time=0.204..2.106 rows=110 loops=1)
  ->  Hash Join  (cost=85.48..297.98 rows=100 width=51) (actual time=0.203..1.981 rows=100 loops=1)
        Hash Cond: (t2.id = t1.id)
        ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.015..0.758 rows=10000 loops=1)
        ->  Hash  (cost=84.23..84.23 rows=100 width=25) (actual time=0.176..0.178 rows=100 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 14kB
              ->  Bitmap Heap Scan on t1  (cost=5.06..84.23 rows=100 width=25) (actual time=0.033..0.155 rows=100 loops=1)
                    Recheck Cond: (num = 1)
                    Heap Blocks: exact=73
                    ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.016..0.017 rows=100 loops=1)
                          Index Cond: (num = 1)
  ->  Nested Loop  (cost=4.65..112.61 rows=10 width=51) (actual time=0.033..0.098 rows=10 loops=1)
        ->  Bitmap Heap Scan on t2 t2_1  (cost=4.36..33.46 rows=10 width=26) (actual time=0.021..0.034 rows=10 loops=1)
              Recheck Cond: (cnt = 2)
              Heap Blocks: exact=10
              ->  Bitmap Index Scan on t2_cnt_idx  (cost=0.00..4.36 rows=10 width=0) (actual time=0.011..0.012 rows=10 loops=1)
                    Index Cond: (cnt = 2)
        ->  Index Scan using t1_id_idx on t1 t1_1  (cost=0.29..7.91 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=10)
              Index Cond: (id = t2_1.id)
              Filter: ((num <> 1) OR (num IS NULL))
Planning Time: 0.605 ms
Execution Time: 2.158 ms

通过这样改写之后，可以看到每个过滤条件都独立地走到了索引扫描中，最后再 Append，查询时间也提升了接近 3 倍。

还能不能更快呢？可以看到一共执行了两次表连接查询，如果是一张表的情况下是不是会更快，如何让两张表成为一张表呢。聪明的你肯定想到了 with，那我们来试试看。

explain analyze 
WITH joined_tables AS (
    SELECT t1.*, t2.* 
    FROM t1 JOIN t2 ON t1.id = t2.id where num <> 1 or cnt <> 2
)
SELECT * FROM joined_tables WHERE num = 1
UNION 
SELECT * FROM joined_tables WHERE cnt = 2 AND (num != 1 OR num IS NULL);

Unique  (cost=1139.78..1142.25 rows=110 width=144) (actual time=14.703..14.743 rows=110 loops=1)
  CTE joined_tables
    ->  Hash Join  (cost=299.00..660.50 rows=10000 width=51) (actual time=2.440..7.826 rows=10000 loops=1)
          Hash Cond: (t1.id = t2.id)
          Join Filter: ((t1.num <> 1) OR (t2.cnt <> 2))
          ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.016..0.951 rows=10000 loops=1)
          ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=2.393..2.394 rows=10000 loops=1)
                Buckets: 16384  Batches: 1  Memory Usage: 704kB
                ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.008..0.867 rows=10000 loops=1)
  ->  Sort  (cost=479.28..479.55 rows=110 width=144) (actual time=14.702..14.709 rows=110 loops=1)
        Sort Key: joined_tables.id, joined_tables.num, joined_tables.dsc, joined_tables.log_date, joined_tables.id_1, joined_tables.cnt, joined_tables.change, joined_tables.op_date
        Sort Method: quicksort  Memory: 32kB
        ->  Append  (cost=0.00..475.55 rows=110 width=144) (actual time=2.446..14.651 rows=110 loops=1)
              ->  CTE Scan on joined_tables  (cost=0.00..225.00 rows=100 width=144) (actual time=2.445..13.545 rows=100 loops=1)
                    Filter: (num = 1)
                    Rows Removed by Filter: 9900
              ->  CTE Scan on joined_tables joined_tables_1  (cost=0.00..250.00 rows=10 width=144) (actual time=0.003..1.085 rows=10 loops=1)
                    Filter: (((num <> 1) OR (num IS NULL)) AND (cnt = 2))
                    Rows Removed by Filter: 9990
Planning Time: 0.393 ms
Execution Time: 15.012 ms

哈哈哈哈哈，原本思想是减少行数之后再进行查询。看来行不通。那就换个思路，将两个表过滤条件之后再进行查询试试看。

explain analyze 
WITH temp_t1 AS (
    SELECT * FROM t1 WHERE num = 1
),
temp_t2 AS (
    SELECT * FROM t2 WHERE cnt = 2
)
SELECT *
FROM (
    SELECT t1.*, t2.*
    FROM temp_t1 t1 JOIN t2 ON t1.id = t2.id
    UNION
    SELECT t1.*, t2.*
    FROM t1 JOIN temp_t2 t2 ON t1.id = t2.id
    WHERE t1.num <> 1 OR t1.num IS NULL
) ;

Unique  (cost=414.87..417.34 rows=110 width=144) (actual time=1.958..1.997 rows=110 loops=1)
  ->  Sort  (cost=414.87..415.14 rows=110 width=144) (actual time=1.957..1.966 rows=110 loops=1)
        Sort Key: t1.id, t1.num, t1.dsc, t1.log_date, t2.id, t2.cnt, t2.change, t2.op_date
        Sort Method: quicksort  Memory: 32kB
        ->  Append  (cost=85.48..411.14 rows=110 width=144) (actual time=0.122..1.914 rows=110 loops=1)
              ->  Hash Join  (cost=85.48..297.98 rows=100 width=51) (actual time=0.122..1.839 rows=100 loops=1)
                    Hash Cond: (t2.id = t1.id)
                    ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.011..0.743 rows=10000 loops=1)
                    ->  Hash  (cost=84.23..84.23 rows=100 width=25) (actual time=0.103..0.104 rows=100 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 14kB
                          ->  Bitmap Heap Scan on t1  (cost=5.06..84.23 rows=100 width=25) (actual time=0.026..0.087 rows=100 loops=1)
                                Recheck Cond: (num = 1)
                                Heap Blocks: exact=73
                                ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.012..0.012 rows=100 loops=1)
                                      Index Cond: (num = 1)
              ->  Nested Loop  (cost=4.65..112.61 rows=10 width=51) (actual time=0.029..0.063 rows=10 loops=1)
                    ->  Bitmap Heap Scan on t2 t2_1  (cost=4.36..33.46 rows=10 width=26) (actual time=0.018..0.027 rows=10 loops=1)
                          Recheck Cond: (cnt = 2)
                          Heap Blocks: exact=10
                          ->  Bitmap Index Scan on t2_cnt_idx  (cost=0.00..4.36 rows=10 width=0) (actual time=0.009..0.009 rows=10 loops=1)
                                Index Cond: (cnt = 2)
                    ->  Index Scan using t1_id_idx on t1 t1_1  (cost=0.29..7.91 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=10)
                          Index Cond: (id = t2_1.id)
                          Filter: ((num <> 1) OR (num IS NULL))
Planning Time: 0.561 ms
Execution Time: 2.048 ms

改写效果不太明显，看来这查询速度已经是最快了。

小结

新技能 +1，SQL 优化有很多套路可循，比如此例中的 OR 改写、标量子查询改写等等。后续慢慢实验一下。

参考文档：

https://mp.weixin.qq.com/s/huTvFwvVOczIpgI3A-dmSQ

如果觉得文章对你有用，请随意赞赏

SQL 优化之 OR 子句改写

https://www.zhangyan1997.xyz/archives/sql-you-hua-zhi-or-zi-ju-gai-xie

作者

张颜

发布于

2025-06-22

更新于

2025-06-22

许可协议

CC BY 4.0

SQL 优化之 OR 子句改写

前言

案例演示

数据准备

单表 OR 查询

多表 OR 查询

SQL 改写

小结

作者

发布于

更新于

许可协议

评论