PostgreSQL運維案例之遞歸查詢死循環(huán)解決方案
更新時間：2024年02月03日 08:52:33 作者：Nickxyoung
PostgreSQL提供的遞歸語法是很棒的,例如可用來解決樹形查詢的問題,解決Oracle用戶connect by的語法兼容性,下面這篇文章主要給大家介紹了關于PostgreSQL運維案例之遞歸查詢死循環(huán)解決方案的相關資料,需要的朋友可以參考下
一、問題背景
某日，開發(fā)同事上報一sql性能問題，一條查詢好似一直跑不出結(jié)果，查詢了n小時，還未返回結(jié)果。比較詭異的是同樣的sql，相同的數(shù)據(jù)量，相同的表大小，且在服務器硬件配置相同的情況下，在另外一套環(huán)境查詢非?？?，毫秒級。
第一時間排查了異常環(huán)境的查詢進程stack，并抓取了一分鐘的strace。從結(jié)果得知進程是正常執(zhí)行的，那么看起來就是查詢慢的問題了。
最終發(fā)現(xiàn)是遞歸查詢出現(xiàn)了死循環(huán)，以下內(nèi)容均是在個人電腦進行的模擬復現(xiàn)
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;
二、問題分析
對比了兩個壞境的執(zhí)行計劃，代價預估及掃描算子、連接算子看起來都是一樣的。
執(zhí)行計劃如下：
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=#
從執(zhí)行計劃來看，代價預估中沒有發(fā)現(xiàn)非常耗時的步驟。對正常的環(huán)境中explain analyze查看實際消耗，實際執(zhí)行300ms，最終返回了一條數(shù)據(jù)，和代價預估基本一致。逐步排查，最終將重心放在了遞歸查詢這部分。
遞歸部分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，查看實際執(zhí)行，并打印了元組的ctid，如下是limit 10的結(jié)果：
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的結(jié)果：
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的結(jié)果。發(fā)現(xiàn)一個現(xiàn)象，ctid為(0,108) (0,109)這兩條數(shù)據(jù)一直在交替迭代，所以sql執(zhí)行慢是一直在交替掃描這兩條數(shù)據(jù)，這條sql在這個環(huán)境中是永遠都跑不出結(jié)果的。
這兩條數(shù)據(jù)很有特點，目前的遞歸條件為f.region_code=r.parent_region_code，而這兩條數(shù)據(jù)的值剛好形成了一個閉環(huán)，導致遞歸陷入了死循環(huán)。
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)另外一個正常的環(huán)境中這兩條數(shù)據(jù)并沒有形成閉環(huán)，如下：
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.已知是這兩條數(shù)據(jù)導致的問題，那么可以參照正常環(huán)境修改數(shù)據(jù)值，或者從查詢條件中剔除這兩條數(shù)據(jù)。實際執(zhí)行340ms返回一條數(shù)據(jù)，如下：
staffNo | empType | empNum | Name | Postion | Addr | Mobile | FirstRegCode | SecondRegCode | Thir
dRegCode | FurthRegCode
---------+---------+-------------+--------------+--------------------------------+--------------------+-------------+--------------+---------------+-----
---------+--------------
200 | 6 | 85192900896 | 運維yuanyuan | Database administrator | 陜西省西安市高新區(qū) | 13512345678 | 1200 | 1020 | 1002
|
(1 row)
Time: 339.986 ms
2.為什么要用到遞歸？遞歸條件是否可以修改？
sql中一些字段的查詢是通過遞歸完成的，例如SecondRegCode字段值為1020是遞歸輸出的第二條結(jié)果，即通過1200遞歸查詢出1020。如果不使用遞歸，那么只能查詢到1200（FirstRegCode字段值），這個字段的值是查不到的。也就是說查詢某些字段是依賴遞歸的。
以當前的sql邏輯，遞歸條件是無法修改的。
建議：
1）如果sql中繼續(xù)使用遞歸，那么對于region_code和parent_region_code字段關系一定要做明確的規(guī)則處理，比如建立檢查約束，明確region_code大于parent_region_code，這樣存入的數(shù)據(jù)就不會出現(xiàn)閉環(huán)。
2）sql作為結(jié)構(gòu)化查詢語言對比應用語言，對復雜邏輯的處理存在很多局限性，不如應用代碼靈活。因此是否可以考慮將遞歸處理從sql中拉出來，放到應用代碼中處理
總結(jié)
到此這篇關于PostgreSQL運維案例之遞歸查詢死循環(huán)的文章就介紹到這了,更多相關PostgreSQL遞歸查詢死循環(huán)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了postgresql 12版本搭建及主備部署操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL是一個功能強大的開源數(shù)據(jù)庫系統(tǒng)，具有可靠性、穩(wěn)定性、數(shù)據(jù)一致性等特點，且可以運行在所有主流操作系統(tǒng)上，包括Linux、Unix、Windows等，這篇文章主要介紹了SpringBoot3集成PostgreSQL,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了PostgreSQL 字符串處理與日期處理操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
PostgreSQL有效地處理數(shù)據(jù)的加密和解密的常見方法
在信息化建設和等保建設中,都要求實現(xiàn)對用戶數(shù)據(jù)的隱私保護,也就是我們常說的脫敏,那么在?PostgreSQL?數(shù)據(jù)庫中有沒有這樣的方法或者策略可以實現(xiàn)呢,本文小編將給大家介紹一下PostgreSQL有效地處理數(shù)據(jù)的加密和解密的常見方法,需要的朋友可以參考下2025-03-03
這篇文章主要介紹了PostgreSQL備份工具 pgBackRest使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
navicat連接postgresql、人大金倉等數(shù)據(jù)庫報錯解決辦法
在使用Navicat操作數(shù)據(jù)庫時,遇到數(shù)據(jù)報錯是一個常見的問題,這類問題可能涉及多個方面,下面這篇文章主要給大家介紹了關于navicat連接postgresql、人大金倉等數(shù)據(jù)庫報錯的解決辦法,需要的朋友可以參考下2024-08-08
PostgreSQL 分頁查詢時間的2種比較方法小結(jié)
這篇文章主要介紹了PostgreSQL 分頁查詢時間的2種比較方法小結(jié)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL的upsert實例操作(insert on conflict do)
這篇文章主要介紹了PostgreSQL的upsert實例操作(insert on conflict do)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
使用PostgreSQL數(shù)據(jù)庫進行中文全文搜索的實現(xiàn)方法
目前在PostgreSQL中常見的兩個中文分詞插件是zhparser和pg_jieba,這里我們使用zhparser,插件的編譯和安裝請查看官方文檔 ,安裝還是比較復雜的,建議找個現(xiàn)成docker鏡像,本文給大家介紹了在PostgreSQL數(shù)據(jù)庫使用中文全文搜索,需要的朋友可以參考下2023-09-09
PGSQL實現(xiàn)判斷一個空值字段,并將NULL值修改為其它值
這篇文章主要介紹了PGSQL實現(xiàn)判斷一個空值字段,并將NULL值修改為其它值，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01