PostgreSQL运维案例之递归查询死循环解决方案，
目录
- 一、问题背景
- 二、问题分析
- 三、解决方案
- 总结
一、问题背景
某日，开发同事上报一sql性能问题，一条查询好似一直跑不出结果，查询了n小时，还未返回结果。比较诡异的是同样的sql，相同的数据量，相同的表大小，且在服务器硬件配置相同的情况下，在另外一套环境查询非常快，毫秒级。
第一时间排查了异常环境的查询进程stack，并抓取了一分钟的strace。从结果得知进程是正常执行的，那么看起来就是查询慢的问题了。
最终发现是递归查询出现了死循环，以下内容均是在个人电脑进行的模拟复现
sql语句如下：
with s as (select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y')
select
s.empno as "staffNo",
s.emp_type as "empType",
s.emp_tel_info as "empNum",
a.cust_name as "Name",
a.cust_position as "Postion",
a.cust_addr as "Addr",
a.cust_tel_info as "Mobile",
(
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
select r.region_code as "FirstRegCode"
from r where r.region_type='5'
and r.region_status='Y'
),
(
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
select r.region_code as "SecondRegCode"
from r where r.region_type='4'
and r.region_status='Y'
),
(
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
select r.region_code as "ThirdRegCode"
from r where r.region_type='3'
and r.region_status='Y'
),
(
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
select r.region_code as "FurthRegCode"
from r where r.region_type='2'
and r.region_status='Y'
)
from s left join cust_info a on s.empno=a.cust_id;
二、问题分析
对比了两个坏境的执行计划，代价预估及扫描算子、连接算子看起来都是一样的。
执行计划如下：
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=8.58..1944.99 rows=1 width=866)
CTE s
-> Index Scan using emp_info_pkey on emp_info (cost=0.28..8.30 rows=1 width=57)
Index Cond: ((empno)::text = '200'::text)
Filter: (((emp_type)::text > '5'::text) AND ((emp_status)::text = 'Y'::text))
-> CTE Scan on s (cost=0.00..0.02 rows=1 width=256)
-> Index Scan using cust_info_pkey on cust_info a (cost=0.28..8.29 rows=1 width=200)
Index Cond: ((s.empno)::text = (cust_id)::text)
SubPlan 3
-> CTE Scan on r r_1 (cost=479.57..482.09 rows=1 width=118)
Filter: (((region_type)::text = '5'::text) AND ((region_status)::text = 'Y'::text))
CTE r
-> Recursive Union (cost=0.28..479.57 rows=101 width=19)
-> Index Scan using region_tbl_pkey on region_tbl f (cost=0.28..8.29 rows=1 width=19)
Index Cond: ((region_code)::text = (s.region_code)::text)
-> Hash Join (cost=0.33..46.93 rows=10 width=19)
Hash Cond: ((f_1.region_code)::text = (r.parent_region_code)::text)
-> Seq Scan on region_tbl f_1 (cost=0.00..39.00 rows=2000 width=19)
-> Hash (cost=0.20..0.20 rows=10 width=118)
-> WorkTable Scan on r (cost=0.00..0.20 rows=10 width=118)
SubPlan 5
-> CTE Scan on r r_3 (cost=479.57..482.09 rows=1 width=118)
Filter: (((region_type)::text = '4'::text) AND ((region_status)::text = 'Y'::text))
CTE r
-> Recursive Union (cost=0.28..479.57 rows=101 width=19)
-> Index Scan using region_tbl_pkey on region_tbl f_2 (cost=0.28..8.29 rows=1 width=19)
Index Cond: ((region_code)::text = (s.region_code)::text)
-> Hash Join (cost=0.33..46.93 rows=10 width=19)
Hash Cond: ((f_3.region_code)::text = (r_2.parent_region_code)::text)
-> Seq Scan on region_tbl f_3 (cost=0.00..39.00 rows=2000 width=19)
-> Hash (cost=0.20..0.20 rows=10 width=118)
-> WorkTable Scan on r r_2 (cost=0.00..0.20 rows=10 width=118)
SubPlan 7
-> CTE Scan on r r_5 (cost=479.57..482.09 rows=1 width=118)
Filter: (((region_type)::text = '3'::text) AND ((region_status)::text = 'Y'::text))
CTE r
-> Recursive Union (cost=0.28..479.57 rows=101 width=19)
-> Index Scan using region_tbl_pkey on region_tbl f_4 (cost=0.28..8.29 rows=1 width=19)
Index Cond: ((region_code)::text = (s.region_code)::text)
-> Hash Join (cost=0.33..46.93 rows=10 width=19)
Hash Cond: ((f_5.region_code)::text = (r_4.parent_region_code)::text)
-> Seq Scan on region_tbl f_5 (cost=0.00..39.00 rows=2000 width=19)
-> Hash (cost=0.20..0.20 rows=10 width=118)
-> WorkTable Scan on r r_4 (cost=0.00..0.20 rows=10 width=118)
SubPlan 9
-> CTE Scan on r r_7 (cost=479.57..482.09 rows=1 width=118)
Filter: (((region_type)::text = '2'::text) AND ((region_status)::text = 'Y'::text))
CTE r
-> Recursive Union (cost=0.28..479.57 rows=101 width=19)
-> Index Scan using region_tbl_pkey on region_tbl f_6 (cost=0.28..8.29 rows=1 width=19)
Index Cond: ((region_code)::text = (s.region_code)::text)
-> Hash Join (cost=0.33..46.93 rows=10 width=19)
Hash Cond: ((f_7.region_code)::text = (r_6.parent_region_code)::text)
-> Seq Scan on region_tbl f_7 (cost=0.00..39.00 rows=2000 width=19)
-> Hash (cost=0.20..0.20 rows=10 width=118)
-> WorkTable Scan on r r_6 (cost=0.00..0.20 rows=10 width=118)
(56 rows)
postgres=#
从执行计划来看，代价预估中没有发现非常耗时的步骤。对正常的环境中explain analyze查看实际消耗，实际执行300ms，最终返回了一条数据，和代价预估基本一致。逐步排查，最终将重心放在了递归查询这部分。
递归部分sql：
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code, f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
分析sql逻辑，递归条件为f.region_code=r.parent_region_code，并且递归开始的f.region_code字段值为s.region_code=‘1200’，这里的1200是通过对s表进行查询得到的，如下：
postgres=# select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y'; region_code | emp_type | emp_tel_info | emp_name | emp_status | empno -------------+----------+--------------+----------------------------------+------------+------- 1200 | 6 | 85192900896 | d7bcf68fc9d88d8b3f5ed6fa2713abcf | Y | 200 (1 row)
改写递归部分的sql，查看实际执行，并打印了元组的ctid，如下是limit 10的结果：
postgres=# with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 10; ctid | region_code | parent_region_code | region_type | region_status ---------+-------------+--------------------+-------------+--------------- (18,75) | 1200 | 1020 | 5 | Y (18,76) | 1020 | 1002 | 4 | Y (9,108) | 1002 | 120 | 3 | Y (18,79) | 120 | 12 | 2 | N (18,81) | 12 | 1 | 1 | N (0,110) | 1 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (10 rows)
再看limit 15的结果：
postgres=# with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 15; ctid | region_code | parent_region_code | region_type | region_status ---------+-------------+--------------------+-------------+--------------- (18,75) | 1200 | 1020 | 5 | Y (18,76) | 1020 | 1002 | 4 | Y (9,108) | 1002 | 120 | 3 | Y (18,79) | 120 | 12 | 2 | N (18,81) | 12 | 1 | 1 | N (0,110) | 1 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (0,109) | 4 | 3 | 3 | N (0,108) | 3 | 4 | 6 | N (15 rows)
之后还打印了limit 1000，limit 10000的结果。发现一个现象，ctid为(0,108) (0,109)这两条数据一直在交替迭代，所以sql执行慢是一直在交替扫描这两条数据，这条sql在这个环境中是永远都跑不出结果的。
这两条数据很有特点，目前的递归条件为f.region_code=r.parent_region_code，而这两条数据的值刚好形成了一个闭环，导致递归陷入了死循环。
postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
ctid | region_code | parent_region_code
---------+-------------+--------------------
(0,108) | 3 | 4
(0,109) | 4 | 3
(2 rows)
另外一个正常的环境中这两条数据并没有形成闭环，如下：
postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
ctid | region_code | parent_region_code
---------+-------------+--------------------
(0,245) | 3 | 0
(0,246) | 4 | 0
(2 rows)
三、解决方案
1.已知是这两条数据导致的问题，那么可以参照正常环境修改数据值，或者从查询条件中剔除这两条数据。实际执行340ms返回一条数据，如下：
staffNo | empType | empNum | Name | Postion | Addr | Mobile | FirstRegCode | SecondRegCode | Thir
dRegCode | FurthRegCode
---------+---------+-------------+--------------+--------------------------------+--------------------+-------------+--------------+---------------+-----
---------+--------------
200 | 6 | 85192900896 | 运维yuanyuan | Database administrator | 陕西省西安市高新区 | 13512345678 | 1200 | 1020 | 1002
|
(1 row)
Time: 339.986 ms
2.为什么要用到递归？递归条件是否可以修改？
sql中一些字段的查询是通过递归完成的，例如SecondRegCode字段值为1020是递归输出的第二条结果，即通过1200递归查询出1020。如果不使用递归，那么只能查询到1200（FirstRegCode字段值），这个字段的值是查不到的。也就是说查询某些字段是依赖递归的。
以当前的sql逻辑，递归条件是无法修改的。
建议：
1）如果sql中继续使用递归，那么对于region_code和parent_region_code字段关系一定要做明确的规则处理，比如建立检查约束，明确region_code大于parent_region_code，这样存入的数据就不会出现闭环。
2）sql作为结构化查询语言对比应用语言，对复杂逻辑的处理存在很多局限性，不如应用代码灵活。因此是否可以考虑将递归处理从sql中拉出来，放到应用代码中处理
总结
到此这篇关于PostgreSQL运维案例之递归查询死循环的文章就介绍到这了,更多相关PostgreSQL递归查询死循环内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！
您可能感兴趣的文章:- PostgreSQL树形结构的递归查询示例
- 在PostgreSQL中实现递归查询的教程
- PostgreSQL图(graph）的递归查询实例
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。