当前位置： 首页 > ORACLE > 正文

PG优化系列：Oracle迁移到PG中性能下降1000倍

huangtingzhong 发布于 2025-08-03 22:11ORACLE抢沙发

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

问题背景

这个问题来自于一个朋友咨询，他们一个小业务在测试Oracle迁移到PG系列环境中，在功能测试时发现一个性能问题，原来在Oracle环境中秒级响应的业务在PG中一直运行不出来，让帮忙看看是什么原因。结果一看，这是老经典的问题了，在15年前，Oracle同样存在这个问题。

问题SQL分析

大概SQL语句如下面这种类型：

select count(*) from booking a where a.account_id not in (select b.account_id from account b);

相信很多同行看到这条SQL语句无需看执行计划就大概知道慢在什么地方。Oracle快是因为Oracle在做子查询改写时，会考虑子查询返回NULL的特殊情况，如果子查询返回NULL则不改写子查询，如果不返回NULL，则会尝试改写，这个功能就是由参数_optimizer_null_aware_antijoin控制，默认在11G以后的版本都为true。

但是在PG数据库中，暂时还不能通过元数据和Where限制条件来判断子查询的NULL情况，还需要开发人员手动重写SQL语句来规避这种限制（其实Oracle改写一样是添加LNNVL函数来排除NULL值的）。

1. 现象模拟

1.1 两张表的DDL语句

booking和account两张表，account的account_id是主键列，booking的account_id为外键列，引用account的account_id列，所以这里可以明确知道booking的account不会存在NULL值的情况，表的DDL语句如下：

htz=# \d booking;
                       Table "postgres_air.booking"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 booking_id   | bigint                   |           | not null | 
 booking_ref  | text                     |           | not null | 
 booking_name | text                     |           |          | 
 account_id   | integer                  |           |          | 
 email        | text                     |           | not null | 
 phone        | text                     |           | not null | 
 update_ts    | timestamp with time zone |           |          | 
 price        | numeric(7,2)             |           |          | 
Indexes:
    "booking_pkey" PRIMARY KEY, btree (booking_id)
    "booking_booking_ref_key" UNIQUE CONSTRAINT, btree (booking_ref)
Foreign-key constraints:
    "booking_account_id_fk" FOREIGN KEY (account_id) REFERENCES account(account_id)
Referenced by:
    TABLE "booking_leg" CONSTRAINT "booking_id_fk" FOREIGN KEY (booking_id) REFERENCES booking(booking_id)
    TABLE "passenger" CONSTRAINT "pass_booking_id_fk" FOREIGN KEY (booking_id) REFERENCES booking(booking_id)

htz=# \d account
                                           Table "postgres_air.account"
      Column       |           Type           | Collation | Nullable |                   Default                   
-------------------+--------------------------+-----------+----------+---------------------------------------------
 account_id        | integer                  |           | not null | nextval('account_account_id_seq'::regclass)
 login             | text                     |           | not null | 
 first_name        | text                     |           | not null | 
 last_name         | text                     |           | not null | 
 frequent_flyer_id | integer                  |           |          | 
 update_ts         | timestamp with time zone |           |          | 
Indexes:
    "account_pkey" PRIMARY KEY, btree (account_id)
    "account_last_name" btree (last_name)
Foreign-key constraints:
    "frequent_flyer_id_fk" FOREIGN KEY (frequent_flyer_id) REFERENCES frequent_flyer(frequent_flyer_id)
Referenced by:
    TABLE "booking" CONSTRAINT "booking_account_id_fk" FOREIGN KEY (account_id) REFERENCES account(account_id)
    TABLE "passenger" CONSTRAINT "pass_account_id_fk" FOREIGN KEY (account_id) REFERENCES booking(booking_id)
    TABLE "passenger" CONSTRAINT "pass_frequent_flyer_id_fk" FOREIGN KEY (account_id) REFERENCES booking(booking_id)
    TABLE "phone" CONSTRAINT "phone_account_id_fk" FOREIGN KEY (account_id) REFERENCES booking(booking_id)

1.2 模拟SQL语句

由于是外键关系，所以下面的SQL语句肯定返回0行，按理说SQL语句应该很快就能返回结果，其实通过DDL语句就可以直接给出结果的。

select count(*) from booking a where a.account_id not in (select b.account_id from account b);
 
select count(*) from booking  a where not exists (select 1 from account b where a.account_id=b.account_id);

1.3 执行故障SQL语句

在我自己的MacOS环境中，下面SQL语句执行了30多分钟都没有结果。

select count(*) from booking a where a.account_id not in (select b.account_id from account b);

会话信息如下：

htz=# \i we.sql
 datname | usename  | application_name | pid | state  | wait_event | tquery  |  txact  | tstate  |                                             query                                              
---------+----------+------------------+-----+--------+------------+---------+---------+---------+------------------------------------------------------------------------------------------------
 htz     | postgres | psql             |     | active |            | 32.67 m | 32.67 m | 32.67 m | select count(*) from booking a where a.account_id not in (select b.account_id from account b);
(1 row)

 application_name | acount 
------------------+--------
 psql             |      1
(1 row)

                                             query                                              | acount 
------------------------------------------------------------------------------------------------+--------
 select count(*) from booking a where a.account_id not in (select b.account_id from account b); |      1

2. SQL性能分析

2.1 执行计划分析

PG中的执行计划需要SQL完整执行完后才会显示，无法通过简单的命令来查看正在执行会话的SQL语句的执行计划，所以这里只能通过explain来分析。

htz=# explain select count(*) from booking a where a.account_id not in (select b.account_id from account b);
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=21776698624.78..21776698624.79 rows=1 width=8)
   ->  Seq Scan on booking a  (cost=0.42..21776691570.69 rows=2821638 width=0)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.42..7074.78 rows=257333 width=4)
                 ->  Index Only Scan using account_pkey on account b  (cost=0.42..4782.11 rows=257333 width=4)
(6 rows)

2.2 性能瓶颈分析

这里的Filter: (NOT (SubPlan 1))跟Oracle中的Filter差不多的意思，也就是booking中每返回一行数据，都会与SubPlan进行一次Filter，也就意味着需要执行一次子查询，当然这里子查询进行了Materialize，虽无需执行子查询，但是仍然需要遍历一遍子查询返回的结果集并进行Filter条件判断。

通过执行计划可以得到booking一共有2821638行，也就意味着SubPlan要被执行2821638次，每一次SubPlan的成本为7074，这就得到一个巨大的成本了，也就是为什么SQL语句运行很慢的原因了。

性能计算：

  • booking表行数：2,821,638行
  • 每次SubPlan成本：7,074
  • 总成本：2,821,638 × 7,074 = 19,966,267,332
  • 这个巨大的成本解释了为什么SQL运行如此缓慢

3. 实施优化

3.1 用NOT EXISTS替代NOT IN

通过两张表的定义，可以完全排除NULL的情况，所以可以直接用not exists重写SQL语句。

select count(*) from booking  a where not exists (select 1 from account b where a.account_id=b.account_id);

执行时间和执行计划如下：

htz=# explain (verbose,analyze)  select count(*) from booking  a where not exists (select 1 from account b where a.account_id=b.account_id);
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=218294.83..218294.84 rows=1 width=8) (actual time=1394.904..1394.905 rows=1 loops=1)
   Output: count(*)
   ->  Hash Anti Join  (cost=9004.78..218294.82 rows=1 width=0) (actual time=1394.902..1394.903 rows=0 loops=1)
         Hash Cond: (a.account_id = b.account_id)
         ->  Seq Scan on postgres_air.booking a  (cost=0.00..143031.76 rows=5643276 width=4) (actual time=0.012..540.512 rows=5643216 loops=1)
               Output: a.account_id
         ->  Hash  (cost=4782.11..4782.11 rows=257333 width=4) (actual time=30.457..30.457 rows=257333 loops=1)
               Output: b.account_id
               Buckets: 131072  Batches: 4  Memory Usage: 3290kB
               ->  Index Only Scan using account_pkey on postgres_air.account b  (cost=0.42..4782.11 rows=257333 width=4) (actual time=0.006..11.608 rows=257333 loops=1)
                     Output: b.account_id
                     Heap Fetches: 0
 Query Identifier: 432154786695496027
 Planning Time: 3.161 ms
 Execution Time: 1395.674 ms
(15 rows)

3.2 优化效果对比

这里看到整个SQL语句走了Hash Anti Join的方式，并且整个时间为1395MS，执行效率是非常快的。

性能对比：

  • NOT IN方式：30+分钟（未完成）
  • NOT EXISTS方式：1.4秒
  • 性能提升：约1000倍以上

3.3 手动添加NULL排除方式无效

在PG中，暂时还不支持通过手动条件NOT NULL方式，如下所示。

htz=# explain (verbose) select count(*) from booking a where a.account_id not in (select b.account_id from account b where b.account_id is not null);
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=23127233328.49..23127233328.50 rows=1 width=8)
   Output: count(*)
   ->  Seq Scan on postgres_air.booking a  (cost=0.00..23127226274.40 rows=2821638 width=0)
         Output: a.booking_id, a.booking_ref, a.booking_name, a.account_id, a.email, a.phone, a.update_ts, a.price
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..7552.99 rows=257333 width=4)
                 Output: b.account_id
                 ->  Seq Scan on postgres_air.account b  (cost=0.00..5260.33 rows=257333 width=4)
                       Output: b.account_id
                       Filter: (b.account_id IS NOT NULL)
 Query Identifier: -7816702025292081538
(12 rows)

4. 技术原理深入分析

4.1 NOT IN vs NOT EXISTS 的底层差异

NOT IN的执行机制：

  • 对于每一行booking记录，都需要检查account_id是否在子查询结果中
  • 如果子查询可能返回NULL，则整个NOT IN表达式可能返回NULL
  • PG优化器无法自动优化这种情况，导致使用Filter + SubPlan的执行方式

NOT EXISTS的执行机制：

  • 使用半连接（Semi Join）的反向操作
  • 优化器可以将其转换为Hash Anti Join或Nested Loop Anti Join
  • 避免了逐行检查的问题，大幅提升性能

4.2 Oracle vs PostgreSQL 优化器差异

Oracle的优化：

  • 参数_optimizer_null_aware_antijoin控制NULL感知的反连接优化
  • 自动检测子查询是否可能返回NULL
  • 如果无NULL风险，自动转换为ANTI JOIN

PostgreSQL的限制：

  • 目前缺乏NULL感知的自动优化
  • 需要手动重写SQL或添加约束条件
  • 优化器相对保守，避免错误的结果

5. 最佳实践建议

5.1 SQL开发规范

  1. 优先使用NOT EXISTS：在可能的情况下，优先使用NOT EXISTS而不是NOT IN
  2. 明确NULL约束：在表设计时明确字段的NULL约束
  3. 添加适当的索引：确保连接字段有合适的索引
  4. 定期更新统计信息：确保优化器有准确的统计信息

5.2 迁移注意事项

  1. 性能测试：从Oracle迁移到PG时，必须进行全面的性能测试
  2. SQL重写：识别并重写可能存在性能问题的SQL
  3. 监控和调优：建立完善的监控体系，及时发现性能问题

6. 总结

目前PG优化器的功能相对来说还比较弱，优化层面还是需要回到最传统的SQL开发规范上多入手，根据优化器本身的特点（优点和缺点）来制定SQL的开发规范，这样才可以尽可能的避免业务上线后遇到的性能问题。

同时如果业务从Oracle等数据库迁移到PG系列中，也不要想象地以为在Oracle运行好好的，在PG中就没有问题，在迁移过程中一定多做好功能和性能的测试。

关键要点：

  • NOT IN在PG中性能较差，特别是在大数据量场景下
  • NOT EXISTS通常能提供更好的性能
  • 数据库迁移需要全面的性能测试和SQL优化
  • 理解不同数据库优化器的特点有助于写出高效的SQL

——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)

赏

您可能还会对这些文章感兴趣！

PG优化系列：Oracle迁移到PG中性能下降1000倍：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter