openGauss学习笔记-277 openGauss性能调优-实际调优案例06-改写SQL消除子查询（案例1）277.1 现象描述277.2 优化说明
openGauss学习笔记-277 openGauss性能调优-实际调优案例06-改写SQL消除子查询（案例1）
277.1 现象描述
select
1,
(select count(*) from normal_date n where n.id = a.id) as GZCS
from normal_date a;
此SQL性能较差，查看发现执行计划中存在SubPlan，具体如下：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on normal_date a (cost=0.00..888118.42 rows=5129 width=4) (actual time=2.394..22194.907 rows=10000 loops=1)
SubPlan 1
-> Aggregate (cost=173.12..173.12 rows=1 width=8) (actual time=22179.496..22179.942 rows=10000 loops=10000)
-> Seq Scan on normal_date n (cost=0.00..173.11 rows=1 width=0) (actual time=11279.349..22159.608 rows=10000 loops=10000)
Filter: (id = a.id)
Rows Removed by Filter: 99990000
Total runtime: 22196.415 ms
(7 rows)
277.2 优化说明
此优化的核心就是消除子查询。分析业务场景发现a.id不为null，那么从SQL语义出发，可以等价改写SQL为：
select
count(*)
from normal_date n, normal_date a
where n.id = a.id
group by a.id;
计划如下：
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=480.86..532.15 rows=5129 width=12) (actual time=21.539..24.356 rows=10000 loops=1)
Group By Key: a.id
-> Hash Join (cost=224.40..455.22 rows=5129 width=4) (actual time=6.402..13.484 rows=10000 loops=1)
Hash Cond: (n.id = a.id)
-> Seq Scan on normal_date n (cost=0.00..160.29 rows=5129 width=4) (actual time=0.087..1.459 rows=10000 loops=1)
-> Hash (cost=160.29..160.29 rows=5129 width=4) (actual time=6.065..6.065 rows=10000 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 352kB
-> Seq Scan on normal_date a (cost=0.00..160.29 rows=5129 width=4) (actual time=0.046..2.738 rows=10000 loops=1)
Total runtime: 26.844 ms
(9 rows)
image.png说明： 为了保证改写的等效性，在normal_date.id加了not null约束。
👍 点赞，你的认可是我创作的动力！
⭐️ 收藏，你的青睐是我努力的方向！
✏️ 评论，你的意见是我进步的财富！
image.png
