kingbase SQL优化案例 (union+join层级查询优化 )
本案例比较复杂，最难以理解的是 case when 那段父子分层关系和父子继承关系。
读者不必完全搞懂本文中所有细节，只需要大致理解笔者逻辑思路即可。
同事上午找我看条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）：
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 '%公司%'; --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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 d.orgid, d.oldorgid, d.codeid, d.orgallname, d.orgname, x.orggrade + 1 AS orggrade, d.parentorgid, d.parentorgname, d.isenable, d.orgtype, x.orgname0, CASE WHEN x.orggrade = 0 THEN d.orgallname ELSE x.orgname1::varchar END, CASE WHEN x.orggrade = 1 THEN d.orgallname ELSE x.orgname2::text END, CASE WHEN x.orggrade = 2 THEN d.orgallname ELSE x.orgname3::text END, CASE WHEN x.orggrade = 3 THEN d.orgallname ELSE x.orgname4::text END, CASE WHEN x.orggrade = 4 THEN d.orgallname ELSE x.orgname5::text END, CASE WHEN x.orggrade = 5 THEN d.orgallname ELSE x.orgname6::text END, CASE WHEN x.orggrade = 6 THEN d.orgallname ELSE x.orgname7::text END, x.orgid0, CASE WHEN x.orggrade = 0 THEN d.orgid ELSE x.orgid1::varchar END, CASE WHEN x.orggrade = 1 THEN d.orgid ELSE x.orgid2::text END, CASE WHEN x.orggrade = 2 THEN d.orgid ELSE x.orgid3::text END, CASE WHEN x.orggrade = 3 THEN d.orgid ELSE x.orgid4::text END, CASE WHEN x.orggrade = 4 THEN d.orgid ELSE x.orgid5::text END, CASE WHEN x.orggrade = 5 THEN d.orgid ELSE x.orgid6::text END, CASE WHEN x.orggrade = 6 THEN d.orgid ELSE x.orgid7::text END, x.codeid0, CASE WHEN x.orggrade = 0 THEN d.codeid ELSE x.codeid1::varchar END, CASE WHEN x.orggrade = 1 THEN d.codeid ELSE x.codeid2::text END, CASE WHEN x.orggrade = 2 THEN d.codeid ELSE x.codeid3::text END, CASE WHEN x.orggrade = 3 THEN d.codeid ELSE x.codeid4::text END, CASE WHEN x.orggrade = 4 THEN d.codeid ELSE x.codeid5::text END, CASE WHEN x.orggrade = 5 THEN d.codeid ELSE x.codeid6::text END, CASE WHEN x.orggrade = 6 THEN d.codeid ELSE x.codeid7::text END FROM v_sour_sour_v d INNER JOIN org_source x ON d.parentorgid = x.orgid ) 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 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.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 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) -> 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 loops=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 loops=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 rows=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=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.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 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.007..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.295 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 rows=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=1loops=1) -> Nested Loop Left Join (cost=0.28..613.90 rows=1 width=1127) (actual time=2.166..3.940 rows=1 loops=1) -> Seq Scan on v_orddddd o (cost=0.00..605.60 rows=1 width=97) (actual time=2.142..3.915 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.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 rows=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 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=1.912..1.914 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.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 time=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) (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=7.245..23.092 rows=921 loops=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=7373loops=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.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 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.500..9.738 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) (actual 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=3224loops=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.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.606rows=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表达式改写了一下：
EXPLAIN ANALYZE 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 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=145) (actual time=3.157..3.160 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=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 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=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 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=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..13.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 rows=7136 loops=8) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=6.860..19.364 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=6.472..14.778 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=5.080..10.337 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.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) (actualtime=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 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.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 loops=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.019..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 loops=1) -> Seq Scan on sm_role (cost=0.00..3.98 rows=1 width=21) (actual time=0.053..0.057 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 time=0.045..0.048 rows=1 loops=1) Recheck Cond: (((pk_role)::text = (sm_role.pk_role)::text) AND ((cuserid)::text = '1001A11000000003PYR5'::text)) Heap Blocks: exact=1 -> BitmapAnd (cost=8.92..8.92 rows=1 width=0) (actual time=0.039..0.041 rows=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 width=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=1036 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 width=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.011 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 width=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.013 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 width=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 executed) 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 width=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 executed) 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 width=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 executed) 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 width=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 executed) 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是否等价：
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 就能出结果，新旧查询逻辑也验证过是等价的。😎
同事又找我了，继续搬砖。。。。🤣🤣🤣🤣
