kingbase SQL優化案例 ( union遞迴 改 cte遞迴 )
2023-12-07 18:00:36
本案例比較複雜，讀者不必完全搞懂本文中所有細節，只需要大致理解筆者邏輯思路即可。
同事上午找我看條SQL，原SQL查詢語句很簡單，內部檢視巢狀很複雜（檢視巢狀了3層左右）。
SQL整體執行時間10多秒，執行計劃幾千行，這裡不提供原SQL的執行計劃。
原SQL（返回11行、執行時間 15s）：
SELECT DISTINCT org_dept.pk_org AS orgid, org2.name AS orgname FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org INNER JOIN v_source ON v_source.orgid = v_ededede.pk_org INNER JOIN org_dept ON org_dept.pk_dept = v_ededede.pk_org LEFT OUTER JOIN v_orddddd org2 ON org2.pk_org = org_dept.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%' AND v_orddddd.isbusinessunit = 'N' AND org2.name LIKE '%公司%' UNION ALL SELECT DISTINCT orgid1 AS orgid, orgname1 AS orgname FROM v_source WHERE orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND v_source.orgname1 IS NOT NULL AND orgname1 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid3, orgname3 FROM v_source WHERE orgid3 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname3 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid4, orgname4 FROM v_source WHERE orgid4 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname4 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid5, orgname5 FROM v_source WHERE orgid5 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname5 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid6, orgname6 FROM v_source WHERE orgid6 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname6 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid7, orgname7 FROM v_source WHERE orgid7 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname7 LIKE '%公司%'; (11 rows) Time: 15053.564 ms (00:15.054)
單獨拿一段SQL出來執行、執行計劃（執行時間 1.7s）：
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=1421074087163.15..1421074095614.61 rows=40000 width=64) (actual time=1691.957..1692.864 rows=0 loops=1) -> Sort (cost=1421074087163.15..1421074089980.30 rows=1126861 width=64) (actual time=1691.957..1692.863 rows=0 loops=1) Sort Key: v_source.orgid7, v_source.orgname7 Sort Method: quicksort Memory: 25kB -> Merge Semi Join (cost=1402027009503.77..1421073931524.27 rows=1126861 width=64) (actual time=1691.952..1692.858 rows=0 loops=1) Merge Cond: ((v_source.orgid)::text = (v_orddddd.pk_org)::text) -> Subquery Scan on v_source (cost=1402027008883.37..1421073768022.37 rows=60644881 width=96) (actual time=1691.951..1692.854 rows=0 loops=1) Filter: ((v_source.orgid7 IS NOT NULL) AND (v_source.orgname7 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 -> Unique (cost=1402027008883.37..1418692923130.00 rows=190467591390 width=3314) (actual time=1685.227..1690.858 rows=7370 loops=1) -> Sort (cost=1402027008883.37..1402503177861.85 rows=190467591390 width=3314) (actual time=1685.225..1686.743 rows=7370 loops=1) Sort Key: "*SELECT* 1".orgid, "*SELECT* 1".oldorgid, "*SELECT* 1".codeid, "*SELECT* 1".orgallname, "*SELECT* 1".orgname, (0), "*SELECT* 1".parentorgid, "*SELECT* 1".parentorgname, "*SELECT* 1".isenable, " *SELECT* 1".orgtype, "*SELECT* 1".orgname0, (NULL::varchar), (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text), "*SELECT* 1".orgid0, ((NULL::bpchar)::varchar), (NULL::text), (NULL::text), (NULL::text), (N ULL::text), (NULL::text), (NULL::text), "*SELECT* 1".codeid0, (NULL::varchar), (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text) Sort Method: quicksort Memory: 6006kB -> Append (cost=0.28..5726531720.32 rows=190467591390 width=3314) (actual time=3.004..1631.143 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.28..1448.14 rows=2 width=1169) (actual time=3.003..9.908 rows=1 loops=1) -> Result (cost=0.28..1448.11 rows=2 width=1169) (actual time=3.000..9.903 rows=1 loops=1) -> Append (cost=0.28..1448.09 rows=2 width=434) (actual time=2.997..9.898 rows=1 loops=1) -> Subquery Scan on "*SELECT* 1_1" (cost=0.28..613.91 rows=1 width=151) (actual time=2.996..5.716 rows=1 loops=1) -> Nested Loop Left Join (cost=0.28..613.90 rows=1 width=1127) (actual time=2.994..5.713 rows=1 loops=1) -> Seq Scan on v_orddddd o (cost=0.00..605.60 rows=1 width=97) (actual time=2.968..5.684 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Index Scan using pk_v_orddddd on v_orddddd oo (cost=0.28..8.30 rows=1 width=43) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: ((pk_org)::text = (o.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2" (cost=656.78..834.17 rows=1 width=716) (actual time=4.176..4.179 rows=0 loops=1) -> Nested Loop Left Join (cost=656.78..834.16 rows=1 width=2176) (actual time=4.175..4.177 rows=0 loops=1) -> Nested Loop Left Join (cost=656.50..825.85 rows=1 width=167) (actual time=4.174..4.176 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=4.173..4.175 rows=0 loops=1) Hash Cond: ((bg.pk_region)::text = (d1.glbdef2)::text) -> Seq Scan on bd_region bg (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=165) (actual time=4.159..4.160 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1 (cost=0.00..656.20 rows=1 width=165) (actual time=4.158..4.158 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Index Scan using pk_v_orddddd on v_orddddd o1 (cost=0.28..8.30 rows=1 width=43) (never executed) Index Cond: ((pk_org)::text = (d1.pk_org)::text) -> Index Scan using pk_org_dept on org_dept dd (cost=0.28..8.30 rows=1 width=43) (never executed) Index Cond: ((pk_dept)::text = (d1.pk_fatherorg)::text) /* 後面還有 800 行計劃，不過都不是重點 */ Planning Time: 84.632 ms Execution Time: 1695.994 ms (813 rows)
可以從上面單獨SQL的執行計劃發現緩慢在 v_source 檢視：append 、sort + Unique 節點（執行計劃標紅處）。
v_source 檢視結構：
\d+ v_source View "ncc.v_source" Column | Type | Collation | Nullable | Default | Storage | Description ---------------+-----------------------------+-----------+----------+---------+----------+------------- orgid | varchar | | | | extended | oldorgid | character varying(101 char) | | | | extended | codeid | character varying(40 char) | | | | extended | orgallname | character varying(300 char) | | | | extended | orgname | character varying(300 char) | | | | extended | orggrade | integer | | | | plain | parentorgid | character varying(20 char) | | | | extended | parentorgname | character varying(300 char) | | | | extended | isenable | integer | | | | plain | orgtype | text | | | | extended | orgname0 | character varying(300 char) | | | | extended | orgname1 | varchar | | | | extended | orgname2 | text | | | | extended | orgname3 | text | | | | extended | orgname4 | text | | | | extended | orgname5 | text | | | | extended | orgname6 | text | | | | extended | orgname7 | text | | | | extended | orgid0 | varchar | | | | extended | orgid1 | varchar | | | | extended | orgid2 | text | | | | extended | orgid3 | text | | | | extended | orgid4 | text | | | | extended | orgid5 | text | | | | extended | orgid6 | text | | | | extended | orgid7 | text | | | | extended | codeid0 | character varying(40 char) | | | | extended | codeid1 | varchar | | | | extended | codeid2 | text | | | | extended | codeid3 | text | | | | extended | codeid4 | text | | | | extended | codeid5 | text | | | | extended | codeid6 | text | | | | extended | codeid7 | text | | | | extended | View definition: SELECT v.orgid, v.oldorgid, v.codeid, v.orgallname, v.orgname, 0 AS orggrade, v.parentorgid, v.parentorgname, v.isenable, v.orgtype, v.orgallname AS orgname0, NULL::varchar AS orgname1, NULL::text AS orgname2, NULL::text AS orgname3, NULL::text AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, NULL::bpchar AS orgid1, NULL::text AS orgid2, NULL::text AS orgid3, NULL::text AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, NULL::varchar AS codeid1, NULL::text AS codeid2, NULL::text AS codeid3, NULL::text AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v WHERE v.codeid::text = '000'::text UNION SELECT v1.orgid, v1.oldorgid, v1.codeid, v1.orgallname, v1.orgname, 1 AS orggrade, v1.parentorgid, v1.parentorgname, v1.isenable, v1.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, NULL::text AS orgname2, NULL::text AS orgname3, NULL::text AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, NULL::text AS orgid2, NULL::text AS orgid3, NULL::text AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, NULL::text AS codeid2, NULL::text AS codeid3, NULL::text AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v2.orgid, v2.oldorgid, v2.codeid, v2.orgallname, v2.orgname, 2 AS orggrade, v2.parentorgid, v2.parentorgname, v2.isenable, v2.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, NULL::text AS orgname3, NULL::text AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, NULL::text AS orgid3, NULL::text AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, NULL::text AS codeid3, NULL::text AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v3.orgid, v3.oldorgid, v3.codeid, v3.orgallname, v3.orgname, 3 AS orggrade, v3.parentorgid, v3.parentorgname, v3.isenable, v3.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, v3.orgallname AS orgname3, NULL::text AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, v3.orgid AS orgid3, NULL::text AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, v3.codeid AS codeid3, NULL::text AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text JOIN v_sour_sour_v v3 ON v2.orgid::text = v3.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v4.orgid, v4.oldorgid, v4.codeid, v4.orgallname, v4.orgname, 4 AS orggrade, v4.parentorgid, v4.parentorgname, v4.isenable, v4.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, v3.orgallname AS orgname3, v4.orgallname AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, v3.orgid AS orgid3, v4.orgid AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, v3.codeid AS codeid3, v4.codeid AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text JOIN v_sour_sour_v v3 ON v2.orgid::text = v3.parentorgid::text JOIN v_sour_sour_v v4 ON v3.orgid::text = v4.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v5.orgid, v5.oldorgid, v5.codeid, v5.orgallname, v5.orgname, 5 AS orggrade, v5.parentorgid, v5.parentorgname, v5.isenable, v5.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, v3.orgallname AS orgname3, v4.orgallname AS orgname4, v5.orgallname AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, v3.orgid AS orgid3, v4.orgid AS orgid4, v5.orgid AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, v3.codeid AS codeid3, v4.codeid AS codeid4, v5.codeid AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text JOIN v_sour_sour_v v3 ON v2.orgid::text = v3.parentorgid::text JOIN v_sour_sour_v v4 ON v3.orgid::text = v4.parentorgid::text JOIN v_sour_sour_v v5 ON v4.orgid::text = v5.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v6.orgid, v6.oldorgid, v6.codeid, v6.orgallname, v6.orgname, 6 AS orggrade, v6.parentorgid, v6.parentorgname, v6.isenable, v6.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, v3.orgallname AS orgname3, v4.orgallname AS orgname4, v5.orgallname AS orgname5, v6.orgallname AS orgname6, NULL::text AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, v3.orgid AS orgid3, v4.orgid AS orgid4, v5.orgid AS orgid5, v6.orgid AS orgid6, NULL::text AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, v3.codeid AS codeid3, v4.codeid AS codeid4, v5.codeid AS codeid5, v6.codeid AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text JOIN v_sour_sour_v v3 ON v2.orgid::text = v3.parentorgid::text JOIN v_sour_sour_v v4 ON v3.orgid::text = v4.parentorgid::text JOIN v_sour_sour_v v5 ON v4.orgid::text = v5.parentorgid::text JOIN v_sour_sour_v v6 ON v5.orgid::text = v6.parentorgid::text WHERE v.codeid::text = '000'::text UNION SELECT v7.orgid, v7.oldorgid, v7.codeid, v7.orgallname, v7.orgname, 7 AS orggrade, v7.parentorgid, v7.parentorgname, v7.isenable, v7.orgtype, v.orgallname AS orgname0, v1.orgallname AS orgname1, v2.orgallname AS orgname2, v3.orgallname AS orgname3, v4.orgallname AS orgname4, v5.orgallname AS orgname5, v6.orgallname AS orgname6, v7.orgallname AS orgname7, v.orgid AS orgid0, v1.orgid AS orgid1, v2.orgid AS orgid2, v3.orgid AS orgid3, v4.orgid AS orgid4, v5.orgid AS orgid5, v6.orgid AS orgid6, v7.orgid AS orgid7, v.codeid AS codeid0, v1.codeid AS codeid1, v2.codeid AS codeid2, v3.codeid AS codeid3, v4.codeid AS codeid4, v5.codeid AS codeid5, v6.codeid AS codeid6, v7.codeid AS codeid7 FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid::text = v1.parentorgid::text JOIN v_sour_sour_v v2 ON v1.orgid::text = v2.parentorgid::text JOIN v_sour_sour_v v3 ON v2.orgid::text = v3.parentorgid::text JOIN v_sour_sour_v v4 ON v3.orgid::text = v4.parentorgid::text JOIN v_sour_sour_v v5 ON v4.orgid::text = v5.parentorgid::text JOIN v_sour_sour_v v6 ON v5.orgid::text = v6.parentorgid::text JOIN v_sour_sour_v v7 ON v6.orgid::text = v7.parentorgid::text WHERE v.codeid::text = '000'::text; Options: status=true
可以看到 v_source 是由另外一個檢視 v_sour_sour_v 構造的一張 union 遞迴檢視。
v_source 檢視主要的邏輯是通過 union + join 操作，實現 子層級.parentorgid = 父層級.orgid 之間的遞迴查詢。
v_source 檢視邏輯解析：
-- 檢視邏輯解析： SELECT 0 AS orggrade -- 遞迴層級，理解成 Oracle的 level 關鍵字 FROM v_sour_sour_v v WHERE v.codeid ::text = '000' ::text union SELECT 1 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 2 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 3 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text JOIN v_sour_sour_v v3 ON v2.orgid ::text = v3.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 4 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text JOIN v_sour_sour_v v3 ON v2.orgid ::text = v3.parentorgid ::text JOIN v_sour_sour_v v4 ON v3.orgid ::text = v4.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 5 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text JOIN v_sour_sour_v v3 ON v2.orgid ::text = v3.parentorgid ::text JOIN v_sour_sour_v v4 ON v3.orgid ::text = v4.parentorgid ::text JOIN v_sour_sour_v v5 ON v4.orgid ::text = v5.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 6 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text JOIN v_sour_sour_v v3 ON v2.orgid ::text = v3.parentorgid ::text JOIN v_sour_sour_v v4 ON v3.orgid ::text = v4.parentorgid ::text JOIN v_sour_sour_v v5 ON v4.orgid ::text = v5.parentorgid ::text JOIN v_sour_sour_v v6 ON v5.orgid ::text = v6.parentorgid ::text WHERE v.codeid ::text = '000' ::text union SELECT 7 AS orggrade FROM v_sour_sour_v v JOIN v_sour_sour_v v1 ON v.orgid ::text = v1.parentorgid ::text JOIN v_sour_sour_v v2 ON v1.orgid ::text = v2.parentorgid ::text JOIN v_sour_sour_v v3 ON v2.orgid ::text = v3.parentorgid ::text JOIN v_sour_sour_v v4 ON v3.orgid ::text = v4.parentorgid ::text JOIN v_sour_sour_v v5 ON v4.orgid ::text = v5.parentorgid ::text JOIN v_sour_sour_v v6 ON v5.orgid ::text = v6.parentorgid ::text JOIN v_sour_sour_v v7 ON v6.orgid ::text = v7.parentorgid ::text WHERE v.codeid ::text = '000' ::text;
這種 union 遞迴的方式效能非常差，每次存取都要 v_source ，都要把所有層級的資料遍歷一遍，拿到結果後在進行 append -> sort -> unique(去重)。
kingbase 資料庫是相容Oracle 樹狀查詢語句：START WITH .. CONNECT BY PRIOR 的語法，但是我測試了一下，效能一般，所以沒采用這種改寫的優化手段。
筆者選擇使用PG資料庫CTE遞迴代替方案來優化 v_source 檢視的 union 遞迴查詢邏輯。
建立 v_source_1檢視使用CTE遞迴查詢邏輯：
CREATE OR REPLACE VIEW v_source_1 AS WITH RECURSIVE org_source AS ( SELECT orgid, oldorgid, codeid, orgallname, orgname, 0 AS orggrade, parentorgid, parentorgname, isenable, orgtype, orgallname AS orgname0, NULL::varchar AS orgname1, NULL::text AS orgname2, NULL::text AS orgname3, NULL::text AS orgname4, NULL::text AS orgname5, NULL::text AS orgname6, NULL::text AS orgname7, orgid AS orgid0, NULL::varchar AS orgid1, NULL::text AS orgid2, NULL::text AS orgid3, NULL::text AS orgid4, NULL::text AS orgid5, NULL::text AS orgid6, NULL::text AS orgid7, codeid AS codeid0, NULL::varchar AS codeid1, NULL::text AS codeid2, NULL::text AS codeid3, NULL::text AS codeid4, NULL::text AS codeid5, NULL::text AS codeid6, NULL::text AS codeid7 FROM v_sour_sour_v WHERE codeid::text = '000'::text UNION ALL SELECT c.orgid, c.oldorgid, c.codeid, c.orgallname, c.orgname, f.orggrade + 1 AS orggrade, c.parentorgid, c.parentorgname, c.isenable, c.orgtype, f.orgname0, CASE WHEN f.orggrade = 0 THEN c.orgallname ELSE f.orgname1::varchar END, CASE WHEN f.orggrade = 1 THEN c.orgallname ELSE f.orgname2::text END, CASE WHEN f.orggrade = 2 THEN c.orgallname ELSE f.orgname3::text END, CASE WHEN f.orggrade = 3 THEN c.orgallname ELSE f.orgname4::text END, CASE WHEN f.orggrade = 4 THEN c.orgallname ELSE f.orgname5::text END, CASE WHEN f.orggrade = 5 THEN c.orgallname ELSE f.orgname6::text END, CASE WHEN f.orggrade = 6 THEN c.orgallname ELSE f.orgname7::text END, f.orgid0, CASE WHEN f.orggrade = 0 THEN c.orgid ELSE f.orgid1::varchar END, CASE WHEN f.orggrade = 1 THEN c.orgid ELSE f.orgid2::text END, CASE WHEN f.orggrade = 2 THEN c.orgid ELSE f.orgid3::text END, CASE WHEN f.orggrade = 3 THEN c.orgid ELSE f.orgid4::text END, CASE WHEN f.orggrade = 4 THEN c.orgid ELSE f.orgid5::text END, CASE WHEN f.orggrade = 5 THEN c.orgid ELSE f.orgid6::text END, CASE WHEN f.orggrade = 6 THEN c.orgid ELSE f.orgid7::text END, f.codeid0, CASE WHEN f.orggrade = 0 THEN c.codeid ELSE f.codeid1::varchar END, CASE WHEN f.orggrade = 1 THEN c.codeid ELSE f.codeid2::text END, CASE WHEN f.orggrade = 2 THEN c.codeid ELSE f.codeid3::text END, CASE WHEN f.orggrade = 3 THEN c.codeid ELSE f.codeid4::text END, CASE WHEN f.orggrade = 4 THEN c.codeid ELSE f.codeid5::text END, CASE WHEN f.orggrade = 5 THEN c.codeid ELSE f.codeid6::text END, CASE WHEN f.orggrade = 6 THEN c.codeid ELSE f.codeid7::text END FROM v_sour_sour_v c INNER JOIN org_source f ON c.parentorgid = f.orgid -- 子層級.parentorgid = 父層級.orgid /* 相當於 Oracle的 SELECT LEVEL AS orggrade -- 遞迴層級 FROM v_sour_sour_v c START WITH c.codeid = '000'; -- 以 c.codeid = '000' 作為起點，向下遞迴查詢 CONNECT BY PRIOR c.orgid = c.parentorgid -- 採用自上而下的搜尋方式，先找父節點再找葉子節點 */ ) SELECT * FROM org_source;
新檢視 v_source_1 執行時間（184 ms） 、新檢視執行計劃、原來檢視 v_source 差集比較（返回空：等價）：
select count(1) from v_source_1; count ------- 7370 (1 row) Time: 184.705 ms
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------ CTE Scan on org_source (cost=39251.05..39398.49 rows=7372 width=3314) (actual time=2.281..198.696 rows=7370 loops=1) CTE org_source -> Recursive Union (cost=0.28..39251.05 rows=7372 width=2020) (actual time=2.278..187.470 rows=7370 loops=1) -> Result (cost=0.28..1448.11 rows=2 width=1169) (actual time=2.275..5.313 rows=1 loops=1) -> Append (cost=0.28..1448.09 rows=2 width=434) (actual time=2.272..5.309 rows=1 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.28..613.91 rows=1 width=151) (actual time=2.271..3.822 rows=1 loops=1) -> Nested Loop Left Join (cost=0.28..613.90 rows=1 width=1127) (actual time=2.270..3.820 rows=1 loops=1) -> Seq Scan on v_orddddd o (cost=0.00..605.60 rows=1 width=97) (actual time=2.249..3.798 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Index Scan using pk_v_orddddd on v_orddddd oo (cost=0.28..8.30 rows=1 width=43) (actual time=0.011..0.011 ro ws=0 loops=1) Index Cond: ((pk_org)::text = (o.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2_1" (cost=656.78..834.17 rows=1 width=716) (actual time=1.480..1.483 rows=0 loops=1) -> Nested Loop Left Join (cost=656.78..834.16 rows=1 width=2176) (actual time=1.479..1.482 rows=0 loops=1) -> Nested Loop Left Join (cost=656.50..825.85 rows=1 width=167) (actual time=1.478..1.480 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=1.477..1.479 rows=0 loops=1) Hash Cond: ((bg.pk_region)::text = (d1.glbdef2)::text) -> Seq Scan on bd_region bg (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=165) (actual time=1.471..1.471 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1 (cost=0.00..656.20 rows=1 width=165) (actual time=1.470..1.470 r ows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Index Scan using pk_v_orddddd on v_orddddd o1 (cost=0.28..8.30 rows=1 width=43) (never executed) Index Cond: ((pk_org)::text = (d1.pk_org)::text) -> Index Scan using pk_org_dept on org_dept dd (cost=0.28..8.30 rows=1 width=43) (never executed) Index Cond: ((pk_dept)::text = (d1.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2" (cost=661.54..3772.92 rows=737 width=2020) (actual time=6.697..22.045 rows=921 loops=8) -> Hash Join (cost=661.54..3765.55 rows=737 width=2020) (actual time=6.685..21.749 rows=921 loops=8) Hash Cond: (("*SELECT* 1_1".parentorgid)::text = (f.orgid)::text) -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=1.010..18.856 rows=7370 loops=8) -> Subquery Scan on "*SELECT* 1_1" (cost=660.89..1251.59 rows=234 width=151) (actual time=1.009..4.720 rows=234 loo ps=8) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=1.009..4.692 rows=234 loops=8) Hash Cond: ((o_1.pk_fatherorg)::text = (oo_1.pk_org)::text) -> Seq Scan on v_orddddd o_1 (cost=0.00..587.16 rows=234 width=97) (actual time=0.005..3.451 rows=234 lo ops=8) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=7.997..7.997 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_1 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.003..6.692 row s=7373 loops=1) -> Subquery Scan on "*SELECT* 2_2" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=4.079..13.616 rows=7136 loops=8) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=4.078..12.871 rows=7136 loops=8) Hash Cond: ((d1_1.glbdef2)::text = (bg_1.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=3.889..9.421 rows=7136 loops =8) Hash Cond: ((d1_1.pk_fatherorg)::text = (dd_1.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=2.857..6.276 rows=71 36 loops=8) Merge Cond: ((o1_1.pk_org)::text = (d1_1.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_1 (cost=0.28..1304.68 rows=7373 width=43) (a ctual time=0.010..0.162 rows=232 loops=8) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=2.821..3.341 rows=7136 loo ps=8) Sort Key: d1_1.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_1 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.0 07..5.285 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=8.229..8.229 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_1 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.026..6.2 95 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.477..1.478 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_1 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.013..1.012 ro ws=3224 loops=1) -> Hash (cost=0.40..0.40 rows=20 width=1354) (actual time=1.154..1.154 rows=921 loops=8) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 690kB -> WorkTable Scan on org_source f (cost=0.00..0.40 rows=20 width=1354) (actual time=0.003..0.531 rows=921 loops=8) Planning Time: 5.623 ms Execution Time: 199.672 ms (63 rows)
select * from v_source_1 except select * from v_source; orgid | oldorgid | codeid | orgallname | orgname | orggrade | parentorgid | parentorgname | isenable | orgtype | orgname0 | orgname1 | orgname2 | orgname3 | orgname4 | orgname5 | orgname6 | orgname7 | orgid0 | orgid1 | orgid2 | orgid3 | orgid4 | orgid5 | orgid6 | orgid7 | codeid0 | codeid1 | codeid2 | codeid3 | codeid4 | codeid5 | codeid6 | codeid7 -------+----------+--------+------------+---------+----------+-------------+---------------+----------+---------+----------+----------+----------+ ----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+--------+--------+--------+---------+--------- +---------+---------+---------+---------+---------+--------- (0 rows) Time: 1857.676 ms (00:01.858)
執行單獨的SQL替換成 v_source_1 （執行時間：211.141 ms）
explain analyze SELECT DISTINCT orgid7, orgname7 FROM v_source_1 WHERE orgid7 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname7 LIKE '%公司%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ Unique (cost=39443.31..39443.32 rows=1 width=64) (actual time=210.455..210.477 rows=0 loops=1) -> Sort (cost=39443.31..39443.32 rows=1 width=64) (actual time=210.454..210.475 rows=0 loops=1) Sort Key: org_source.orgid7, org_source.orgname7 Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=39252.02..39443.30 rows=1 width=64) (actual time=210.450..210.471 rows=0 loops=1) -> CTE Scan on org_source (cost=39251.05..39416.92 rows=2 width=3314) (actual time=210.449..210.468 rows=0 loops=1) Filter: ((orgid7 IS NOT NULL) AND (orgname7 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 CTE org_source -> Recursive Union (cost=0.28..39251.05 rows=7372 width=2020) (actual time=2.177..196.604 rows=7370 loops=1) -> Result (cost=0.28..1448.11 rows=2 width=1169) (actual time=2.173..5.873 rows=1 loops=1) -> Append (cost=0.28..1448.09 rows=2 width=434) (actual time=2.169..5.867 rows=1 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.28..613.91 rows=1 width=151) (actual time=2.168..3.943 rows=1 loops=1) -> Nested Loop Left Join (cost=0.28..613.90 rows=1 width=1127) (actual time=2.166..3.940 rows=1 l oops=1) -> Seq Scan on v_orddddd o (cost=0.00..605.60 rows=1 width=97) (actual time=2.142..3.915 row s=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Index Scan using pk_v_orddddd on v_orddddd oo (cost=0.28..8.30 rows=1 width=43) (actual ti me=0.014..0.014 rows=0 loops=1) Index Cond: ((pk_org)::text = (o.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2_1" (cost=656.78..834.17 rows=1 width=716) (actual time=1.914..1.918 row s=0 loops=1) -> Nested Loop Left Join (cost=656.78..834.16 rows=1 width=2176) (actual time=1.913..1.917 rows=0 loops=1) -> Nested Loop Left Join (cost=656.50..825.85 rows=1 width=167) (actual time=1.912..1.916 r ows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=1.912..1.914 r ows=0 loops=1) Hash Cond: ((bg.pk_region)::text = (d1.glbdef2)::text) -> Seq Scan on bd_region bg (cost=0.00..149.24 rows=3224 width=21) (never execu ted) -> Hash (cost=656.20..656.20 rows=1 width=165) (actual time=1.902..1.903 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1 (cost=0.00..656.20 rows=1 width=165) (actual t ime=1.900..1.900 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Index Scan using pk_v_orddddd on v_orddddd o1 (cost=0.28..8.30 rows=1 width=43) (nev er executed) Index Cond: ((pk_org)::text = (d1.pk_org)::text) -> Index Scan using pk_org_dept on org_dept dd (cost=0.28..8.30 rows=1 width=43) (never exe cuted) Index Cond: ((pk_dept)::text = (d1.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2" (cost=661.54..3772.92 rows=737 width=2020) (actual time=7.245..23.092 rows=921 loo ps=8) -> Hash Join (cost=661.54..3765.55 rows=737 width=2020) (actual time=7.229..22.787 rows=921 loops=8) Hash Cond: (("*SELECT* 1_1".parentorgid)::text = (f.orgid)::text) -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=1.044..19.727 rows=7370 loops=8) -> Subquery Scan on "*SELECT* 1_1" (cost=660.89..1251.59 rows=234 width=151) (actual time=1.043.. 5.126 rows=234 loops=8) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=1.042..5.099 rows =234 loops=8) Hash Cond: ((o_1.pk_fatherorg)::text = (oo_1.pk_org)::text) -> Seq Scan on v_orddddd o_1 (cost=0.00..587.16 rows=234 width=97) (actual time=0.008. .3.833 rows=234 loops=8) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.220..8.221 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_1 (cost=0.00..568.73 rows=7373 width=43) (actual tim e=0.004..6.906 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_2" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=3.712 ..14.081 rows=7136 loops=8) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=3.711..13.301 r ows=7136 loops=8) Hash Cond: ((d1_1.glbdef2)::text = (bg_1.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=3.500..9.7 38 rows=7136 loops=8) Hash Cond: ((d1_1.pk_fatherorg)::text = (dd_1.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=2. 748..6.679 rows=7136 loops=8) Merge Cond: ((o1_1.pk_org)::text = (d1_1.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_1 (cost=0.28..1304.68 rows =7373 width=43) (actual time=0.014..0.223 rows=232 loops=8) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=2.702..3 .360 rows=7136 loops=8) Sort Key: d1_1.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_1 (cost=0.00..638.36 rows=7136 width=165 ) (actual time=0.014..5.825 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=5.981..5.982 rows =7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_1 (cost=0.00..638.36 rows=7136 width=43) (actu al time=0.017..4.686 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.634..1.635 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_1 (cost=0.00..149.24 rows=3224 width=21) (actual ti me=0.017..1.155 rows=3224 loops=1) -> Hash (cost=0.40..0.40 rows=20 width=1354) (actual time=1.194..1.194 rows=921 loops=8) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 690kB -> WorkTable Scan on org_source f (cost=0.00..0.40 rows=20 width=1354) (actual time=0.004..0.606 rows=921 loops=8) -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (never executed) Join Filter: ((org_source.orgid)::text = (v_orddddd.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (never executed) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (never executed) -> Seq Scan on sm_role (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede (cost=0.41..8.43 rows=1 width=42) (never executed) Index Cond: ((subjectid = (sm_role.pk_role)::text) AND (pk_org = (org_source.orgid)::text)) Heap Fetches: 0 -> Index Scan using i_sm_u_r_role on v_xxxxxx (cost=0.28..0.40 rows=1 width=21) (never executed) Index Cond: ((pk_role)::text = (v_ededede.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using pk_v_orddddd on v_orddddd (cost=0.28..0.33 rows=1 width=21) (never executed) Index Cond: (pk_org = (v_ededede.pk_org)::text) Heap Fetches: 0 Planning Time: 6.838 ms Execution Time: 211.141 ms (85 rows) Time: 225.300 ms
最後驗證整體的SQL，使用CTE表示式改寫了一下：
with t as ( select * from v_source_1 ) SELECT DISTINCT org_dept.pk_org AS orgid, org2.name AS orgname FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org INNER JOIN t ON t.orgid = v_ededede.pk_org INNER JOIN org_dept ON org_dept.pk_dept = v_ededede.pk_org LEFT OUTER JOIN v_orddddd org2 ON org2.pk_org = org_dept.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%' AND v_orddddd.isbusinessunit = 'N' AND org2.name LIKE '%公司%' UNION ALL SELECT DISTINCT orgid1 AS orgid, orgname1 AS orgname FROM t WHERE orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND t.orgname1 IS NOT NULL AND orgname1 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid3, orgname3 FROM t WHERE orgid3 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname3 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid4, orgname4 FROM t WHERE orgid4 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname4 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid5, orgname5 FROM t WHERE orgid5 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname5 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid6, orgname6 FROM t WHERE orgid6 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname6 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid7, orgname7 FROM t WHERE orgid7 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname7 LIKE '%公司%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- Append (cost=40312.31..41466.14 rows=15 width=51) (actual time=302.462..561.836 rows=11 loops=1) CTE t -> CTE Scan on org_source (cost=39251.05..39398.49 rows=7372 width=3314) (actual time=3.658..295.551 rows=7370 loops=1) CTE org_source -> Recursive Union (cost=0.28..39251.05 rows=7372 width=2020) (actual time=3.654..280.971 rows=7370 loops=1) -> Result (cost=0.28..1448.11 rows=2 width=1169) (actual time=3.648..9.872 rows=1 loops=1) -> Append (cost=0.28..1448.09 rows=2 width=434) (actual time=3.642..9.865 rows=1 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.28..613.91 rows=1 width=151) (actual time=3.641..6.693 rows=1 loops=1) -> Nested Loop Left Join (cost=0.28..613.90 rows=1 width=1127) (actual time=3.638..6.687 rows=1 loops=1) -> Seq Scan on v_orddddd o (cost=0.00..605.60 rows=1 width=97) (actual time=3.596..6.643 rows=1 loops= 1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Index Scan using pk_v_orddddd on v_orddddd oo (cost=0.28..8.30 rows=1 width=43) (actual time=0.027.. 0.027 rows=0 loops=1) Index Cond: ((pk_org)::text = (o.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2_1" (cost=656.78..834.17 rows=1 width=716) (actual time=3.160..3.166 rows=0 loops= 1) -> Nested Loop Left Join (cost=656.78..834.16 rows=1 width=2176) (actual time=3.159..3.164 rows=0 loops=1) -> Nested Loop Left Join (cost=656.50..825.85 rows=1 width=167) (actual time=3.158..3.162 rows=0 loop s=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=3.157..3.160 rows=0 loop s=1) Hash Cond: ((bg.pk_region)::text = (d1.glbdef2)::text) -> Seq Scan on bd_region bg (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=165) (actual time=3.145..3.147 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1 (cost=0.00..656.20 rows=1 width=165) (actual time=3.144. .3.144 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Index Scan using pk_v_orddddd on v_orddddd o1 (cost=0.28..8.30 rows=1 width=43) (never execute d) Index Cond: ((pk_org)::text = (d1.pk_org)::text) -> Index Scan using pk_org_dept on org_dept dd (cost=0.28..8.30 rows=1 width=43) (never executed) Index Cond: ((pk_dept)::text = (d1.pk_fatherorg)::text) -> Subquery Scan on "*SELECT* 2" (cost=661.54..3772.92 rows=737 width=2020) (actual time=10.083..32.963 rows=921 loops=8) -> Hash Join (cost=661.54..3765.55 rows=737 width=2020) (actual time=10.065..32.616 rows=921 loops=8) Hash Cond: (("*SELECT* 1_1".parentorgid)::text = (f.orgid)::text) -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=2.073..28.739 rows=7370 loops=8) -> Subquery Scan on "*SELECT* 1_1" (cost=660.89..1251.59 rows=234 width=151) (actual time=2.073..7.826 rows =234 loops=8) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=2.071..7.790 rows=234 loops =8) Hash Cond: ((o_1.pk_fatherorg)::text = (oo_1.pk_org)::text) -> Seq Scan on v_orddddd o_1 (cost=0.00..587.16 rows=234 width=97) (actual time=0.011..5.411 row s=234 loops=8) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=16.417..16.418 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_1 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.006..1 3.850 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_2" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=6.861..20.307 r ows=7136 loops=8) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=6.860..19.364 rows=7136 l oops=8) Hash Cond: ((d1_1.glbdef2)::text = (bg_1.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=6.472..14.778 rows=7 136 loops=8) Hash Cond: ((d1_1.pk_fatherorg)::text = (dd_1.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=5.080..10.33 7 rows=7136 loops=8) Merge Cond: ((o1_1.pk_org)::text = (d1_1.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_1 (cost=0.28..1304.68 rows=7373 widt h=43) (actual time=0.019..0.350 rows=232 loops=8) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=5.019..5.875 rows= 7136 loops=8) Sort Key: d1_1.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_1 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.016..10.482 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=11.094..11.095 rows=7136 lo ops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_1 (cost=0.00..638.36 rows=7136 width=43) (actual time=0. 031..8.520 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=3.050..3.051 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_1 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.033.. 2.189 rows=3224 loops=1) -> Hash (cost=0.40..0.40 rows=20 width=1354) (actual time=1.620..1.620 rows=921 loops=8) Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 690kB -> WorkTable Scan on org_source f (cost=0.00..0.40 rows=20 width=1354) (actual time=0.006..0.863 rows=921 l oops=8) -> Unique (cost=840.11..840.18 rows=9 width=43) (actual time=302.461..302.492 rows=3 loops=1) -> Sort (cost=840.11..840.13 rows=9 width=43) (actual time=302.460..302.473 rows=28 loops=1) Sort Key: org_dept.pk_org, org2.name Sort Method: quicksort Memory: 28kB -> Nested Loop (cost=662.05..839.97 rows=9 width=43) (actual time=137.452..302.414 rows=28 loops=1) -> Hash Join (cost=661.76..836.94 rows=9 width=117) (actual time=137.426..301.862 rows=28 loops=1) Hash Cond: ((t.orgid)::text = (v_ededede.pk_org)::text) -> CTE Scan on t (cost=0.00..147.44 rows=7372 width=32) (actual time=3.661..298.595 rows=7370 loops=1) -> Hash (cost=661.65..661.65 rows=9 width=85) (actual time=1.692..1.700 rows=28 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Merge Join (cost=634.41..661.65 rows=9 width=85) (actual time=1.045..1.679 rows=28 loops=1) Merge Cond: ((org2.pk_org)::text = (org_dept.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd org2 (cost=0.28..1323.11 rows=500 width=43) (actual time=0.01 9..0.404 rows=139 loops=1) Filter: ((name)::text ~~ '%公司%'::text) Rows Removed by Filter: 58 -> Sort (cost=620.89..621.22 rows=134 width=63) (actual time=0.954..0.965 rows=37 loops=1) Sort Key: org_dept.pk_org Sort Method: quicksort Memory: 30kB -> Nested Loop (cost=9.61..616.15 rows=134 width=63) (actual time=0.208..0.903 rows=37 loops=1) -> Nested Loop (cost=9.33..569.81 rows=137 width=21) (actual time=0.131..0.239 rows=46 loops= 1) -> Nested Loop (cost=8.92..16.91 rows=1 width=42) (actual time=0.103..0.112 rows=1 loop s=1) -> Seq Scan on sm_role (cost=0.00..3.98 rows=1 width=21) (actual time=0.053..0.05 7 rows=1 loops=1) Filter: ((role_code)::text ~~ '%qiwei%'::text) Rows Removed by Filter: 81 -> Bitmap Heap Scan on v_xxxxxx (cost=8.92..12.93 rows=1 width=21) (actual ti me=0.045..0.048 rows=1 loops=1) Recheck Cond: (((pk_role)::text = (sm_role.pk_role)::text) AND ((cuserid)::te xt = '1001A11000000003PYR5'::text)) Heap Blocks: exact=1 -> BitmapAnd (cost=8.92..8.92 rows=1 width=0) (actual time=0.039..0.041 row s=0 loops=1) -> Bitmap Index Scan on i_sm_u_r_role (cost=0.00..4.32 rows=6 width=0 ) (actual time=0.016..0.016 rows=5 loops=1) Index Cond: ((pk_role)::text = (sm_role.pk_role)::text) -> Bitmap Index Scan on i_sm_u_r_cuserid (cost=0.00..4.34 rows=9 widt h=0) (actual time=0.019..0.019 rows=7 loops=1) Index Cond: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede (cost=0.41..542.53 rows=103 6 width=42) (actual time=0.024..0.116 rows=46 loops=1) Index Cond: (subjectid = (v_xxxxxx.pk_role)::text) Heap Fetches: 46 -> Index Scan using pk_org_dept on org_dept (cost=0.28..0.34 rows=1 width=42) (actual time=0. 013..0.013 rows=1 loops=46) Index Cond: ((pk_dept)::text = (v_ededede.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd (cost=0.28..0.34 rows=1 width=21) (actual time=0.017..0.017 rows=1 loops=28) Index Cond: ((pk_org)::text = (v_ededede.pk_org)::text) Filter: (isbusinessunit = 'N'::bpchar) -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=228.804..228.827 rows=4 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=228.803..228.811 rows=45 loops=1) Sort Key: t_1.orgid1, t_1.orgname1 Sort Method: quicksort Memory: 31kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=0.198..228.711 rows=45 loops=1) -> CTE Scan on t t_1 (cost=0.00..165.87 rows=2 width=96) (actual time=0.009..5.567 rows=7364 loops=1) Filter: ((orgname1 IS NOT NULL) AND ((orgname1)::text ~~ '%公司%'::text)) Rows Removed by Filter: 6 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (actual time=0.030..0.030 rows=0 loops=7364) Join Filter: ((t_1.orgid)::text = (v_orddddd_1.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (actual time=0.030..0.030 rows=0 loops=7364) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (actual time=0.029..0.029 rows=0 loops=7364) -> Seq Scan on sm_role sm_role_1 (cost=0.00..3.98 rows=1 width=21) (actual time=0.018..0.019 rows=1 loops =7364) Filter: ((role_code)::text ~~ '%qiwei%'::text) Rows Removed by Filter: 81 -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_1 (cost=0.41..8.43 rows=1 widt h=42) (actual time=0.010..0.010 rows=0 loops=7364) Index Cond: ((subjectid = (sm_role_1.pk_role)::text) AND (pk_org = (t_1.orgid)::text)) Heap Fetches: 45 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_1 (cost=0.28..0.40 rows=1 width=21) (actual time =0.008..0.008 rows=1 loops=45) Index Cond: ((pk_role)::text = (v_ededede_1.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) Rows Removed by Filter: 3 -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_1 (cost=0.28..0.33 rows=1 width=21) (actual time=0.011..0.0 11 rows=1 loops=45) Index Cond: (pk_org = (v_ededede_1.pk_org)::text) Heap Fetches: 45 -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=10.862..10.875 rows=4 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=10.861..10.868 rows=5 loops=1) Sort Key: ((t_2.orgid3)::varchar), ((t_2.orgname3)::varchar) Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=4.253..10.843 rows=5 loops=1) -> CTE Scan on t t_2 (cost=0.00..165.87 rows=2 width=96) (actual time=0.122..4.655 rows=218 loops=1) Filter: ((orgid3 IS NOT NULL) AND (orgname3 ~~ '%公司%'::text)) Rows Removed by Filter: 7152 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (actual time=0.028..0.028 rows=0 loops=218) Join Filter: ((t_2.orgid)::text = (v_orddddd_2.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (actual time=0.028..0.028 rows=0 loops=218) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (actual time=0.027..0.027 rows=0 loops=218) -> Seq Scan on sm_role sm_role_2 (cost=0.00..3.98 rows=1 width=21) (actual time=0.017..0.017 rows=1 loops =218) Filter: ((role_code)::text ~~ '%qiwei%'::text) Rows Removed by Filter: 81 -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_2 (cost=0.41..8.43 rows=1 widt h=42) (actual time=0.009..0.009 rows=0 loops=218) Index Cond: ((subjectid = (sm_role_2.pk_role)::text) AND (pk_org = (t_2.orgid)::text)) Heap Fetches: 5 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_2 (cost=0.28..0.40 rows=1 width=21) (actual time =0.009..0.009 rows=1 loops=5) Index Cond: ((pk_role)::text = (v_ededede_2.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) Rows Removed by Filter: 3 -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_2 (cost=0.28..0.33 rows=1 width=21) (actual time=0.013..0.0 13 rows=1 loops=5) Index Cond: (pk_org = (v_ededede_2.pk_org)::text) Heap Fetches: 5 -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=4.418..4.424 rows=0 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=4.418..4.423 rows=0 loops=1) Sort Key: ((t_3.orgid4)::varchar), ((t_3.orgname4)::varchar) Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=4.406..4.410 rows=0 loops=1) -> CTE Scan on t t_3 (cost=0.00..165.87 rows=2 width=96) (actual time=4.405..4.405 rows=0 loops=1) Filter: ((orgid4 IS NOT NULL) AND (orgname4 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (never executed) Join Filter: ((t_3.orgid)::text = (v_orddddd_3.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (never executed) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (never executed) -> Seq Scan on sm_role sm_role_3 (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_3 (cost=0.41..8.43 rows=1 widt h=42) (never executed) Index Cond: ((subjectid = (sm_role_3.pk_role)::text) AND (pk_org = (t_3.orgid)::text)) Heap Fetches: 0 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_3 (cost=0.28..0.40 rows=1 width=21) (never execu ted) Index Cond: ((pk_role)::text = (v_ededede_3.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_3 (cost=0.28..0.33 rows=1 width=21) (never executed) Index Cond: (pk_org = (v_ededede_3.pk_org)::text) Heap Fetches: 0 -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=5.153..5.158 rows=0 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=5.152..5.156 rows=0 loops=1) Sort Key: ((t_4.orgid5)::varchar), ((t_4.orgname5)::varchar) Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=5.140..5.144 rows=0 loops=1) -> CTE Scan on t t_4 (cost=0.00..165.87 rows=2 width=96) (actual time=5.139..5.140 rows=0 loops=1) Filter: ((orgid5 IS NOT NULL) AND (orgname5 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (never executed) Join Filter: ((t_4.orgid)::text = (v_orddddd_4.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (never executed) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (never executed) -> Seq Scan on sm_role sm_role_4 (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_4 (cost=0.41..8.43 rows=1 widt h=42) (never executed) Index Cond: ((subjectid = (sm_role_4.pk_role)::text) AND (pk_org = (t_4.orgid)::text)) Heap Fetches: 0 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_4 (cost=0.28..0.40 rows=1 width=21) (never execu ted) Index Cond: ((pk_role)::text = (v_ededede_4.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_4 (cost=0.28..0.33 rows=1 width=21) (never executed) Index Cond: (pk_org = (v_ededede_4.pk_org)::text) Heap Fetches: 0 -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=4.834..4.838 rows=0 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=4.833..4.837 rows=0 loops=1) Sort Key: ((t_5.orgid6)::varchar), ((t_5.orgname6)::varchar) Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=4.817..4.820 rows=0 loops=1) -> CTE Scan on t t_5 (cost=0.00..165.87 rows=2 width=96) (actual time=4.816..4.817 rows=0 loops=1) Filter: ((orgid6 IS NOT NULL) AND (orgname6 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (never executed) Join Filter: ((t_5.orgid)::text = (v_orddddd_5.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (never executed) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (never executed) -> Seq Scan on sm_role sm_role_5 (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_5 (cost=0.41..8.43 rows=1 widt h=42) (never executed) Index Cond: ((subjectid = (sm_role_5.pk_role)::text) AND (pk_org = (t_5.orgid)::text)) Heap Fetches: 0 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_5 (cost=0.28..0.40 rows=1 width=21) (never execu ted) Index Cond: ((pk_role)::text = (v_ededede_5.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_5 (cost=0.28..0.33 rows=1 width=21) (never executed) Index Cond: (pk_org = (v_ededede_5.pk_org)::text) Heap Fetches: 0 -> Unique (cost=192.24..192.25 rows=1 width=64) (actual time=5.175..5.179 rows=0 loops=1) -> Sort (cost=192.24..192.25 rows=1 width=64) (actual time=5.174..5.177 rows=0 loops=1) Sort Key: ((t_6.orgid7)::varchar), ((t_6.orgname7)::varchar) Sort Method: quicksort Memory: 25kB -> Nested Loop Semi Join (cost=0.97..192.23 rows=1 width=64) (actual time=5.159..5.162 rows=0 loops=1) -> CTE Scan on t t_6 (cost=0.00..165.87 rows=2 width=96) (actual time=5.157..5.158 rows=0 loops=1) Filter: ((orgid7 IS NOT NULL) AND (orgname7 ~~ '%公司%'::text)) Rows Removed by Filter: 7370 -> Nested Loop (cost=0.97..13.17 rows=1 width=42) (never executed) Join Filter: ((t_6.orgid)::text = (v_orddddd_6.pk_org)::text) -> Nested Loop (cost=0.69..12.83 rows=1 width=21) (never executed) -> Nested Loop (cost=0.41..12.42 rows=1 width=63) (never executed) -> Seq Scan on sm_role sm_role_6 (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Index Only Scan using i_sm_sub_o_subid on v_ededede v_ededede_6 (cost=0.41..8.43 rows=1 widt h=42) (never executed) Index Cond: ((subjectid = (sm_role_6.pk_role)::text) AND (pk_org = (t_6.orgid)::text)) Heap Fetches: 0 -> Index Scan using i_sm_u_r_role on v_xxxxxx v_xxxxxx_6 (cost=0.28..0.40 rows=1 width=21) (never execu ted) Index Cond: ((pk_role)::text = (v_ededede_6.subjectid)::text) Filter: ((cuserid)::text = '1001A11000000003PYR5'::text) -> Index Only Scan using pk_v_orddddd on v_orddddd v_orddddd_6 (cost=0.28..0.33 rows=1 width=21) (never executed) Index Cond: (pk_org = (v_ededede_6.pk_org)::text) Heap Fetches: 0 Planning Time: 28.913 ms Execution Time: 563.733 ms (247 rows) Time: 609.213 ms
改寫完成後的完整SQL和原SQL校驗過是等價的：
with t as ( select * from v_source_1 ) select * from ( SELECT DISTINCT org_dept.pk_org AS orgid, org2.name AS orgname FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org INNER JOIN t ON t.orgid = v_ededede.pk_org INNER JOIN org_dept ON org_dept.pk_dept = v_ededede.pk_org LEFT OUTER JOIN v_orddddd org2 ON org2.pk_org = org_dept.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%' AND v_orddddd.isbusinessunit = 'N' AND org2.name LIKE '%公司%' UNION ALL SELECT DISTINCT orgid1 AS orgid, orgname1 AS orgname FROM t WHERE orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND t.orgname1 IS NOT NULL AND orgname1 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid3, orgname3 FROM t WHERE orgid3 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname3 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid4, orgname4 FROM t WHERE orgid4 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname4 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid5, orgname5 FROM t WHERE orgid5 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname5 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid6, orgname6 FROM t WHERE orgid6 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname6 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid7, orgname7 FROM t WHERE orgid7 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname7 LIKE '%公司%') except select * from ( SELECT DISTINCT org_dept.pk_org AS orgid, org2.name AS orgname FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org INNER JOIN v_source ON v_source.orgid = v_ededede.pk_org INNER JOIN org_dept ON org_dept.pk_dept = v_ededede.pk_org LEFT OUTER JOIN v_orddddd org2 ON org2.pk_org = org_dept.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%' AND v_orddddd.isbusinessunit = 'N' AND org2.name LIKE '%公司%' UNION ALL SELECT DISTINCT orgid1 AS orgid, orgname1 AS orgname FROM v_source WHERE orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND v_source.orgname1 IS NOT NULL AND orgname1 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid3, orgname3 FROM v_source WHERE orgid3 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname3 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid4, orgname4 FROM v_source WHERE orgid4 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname4 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid5, orgname5 FROM v_source WHERE orgid5 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname5 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid6, orgname6 FROM v_source WHERE orgid6 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname6 LIKE '%公司%' UNION ALL SELECT DISTINCT orgid7, orgname7 FROM v_source WHERE orgid7 IS NOT NULL AND orgid IN (SELECT v_ededede.pk_org FROM v_xxxxxx INNER JOIN v_ededede ON v_xxxxxx.pk_role = v_ededede.subjectid INNER JOIN sm_role ON sm_role.pk_role = v_xxxxxx.pk_role INNER JOIN v_orddddd ON v_orddddd.pk_org = v_ededede.pk_org WHERE cuserid = '1001A11000000003PYR5' AND role_code LIKE '%qiwei%') AND orgname7 LIKE '%公司%'); orgid | orgname -------+--------- (0 rows) Time: 15595.861 ms (00:15.596)
至此，這條SQL已經優化完成，從原來 15053.564 ms (00:15.054)執行時間，通過中間層檢視邏輯改寫後降低到 Execution Time: 563.733 ms 就能出結果，新舊查詢邏輯也驗證過是等價的。