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

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

news 2025/8/29 2:32:36

问题背景

这个问题来自于一个朋友咨询，他们一个小业务在测试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 accountTable "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.sqldatname | 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_idBuckets: 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_idHeap Fetches: 0Query Identifier: 432154786695496027Planning Time: 3.161 msExecution 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.priceFilter: (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_idFilter: (b.account_id IS NOT NULL)Query Identifier: -7816702025292081538
(12 rows)