本案例比較複雜，讀者不必完全搞懂本文中所有細節，只需要大致理解筆者邏輯思路即可。
同事上午找我看條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) -> Gather (cost=2478.47..4252.90 rows=74 width=1396) (actual time=32.606..79.786 rows=13 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1478.47..3245.50 rows=31 width=1396) (actual time=25.048..26.879 rows=4 loops=3) Hash Cond: (("*SELECT* 2_2".parentorgid)::text = ("*SELECT* 2_1".orgid)::text) -> Parallel Append (cost=660.89..2416.26 rows=3070 width=698) (actual time=18.133..23.749 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_2" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=39.863..50.740 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=39.862..49.939 rows=7136 loops=1) 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=37.891..44.557 rows=7136 loops=1) 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=29.057..33.304 rows=7136 loops=1) 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.051..0.422 rows=232 loops=1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=28.915..29.602 rows=7136 loops=1) Sort Key: d1_1.pk_org Worker 1: Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_1 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.022..7.119 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=8.813..8.814 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.020..6.553 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.943..1.944 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.050..1.381 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_3" (cost=660.89..1251.59 rows=234 width=151) (actual time=14.533..19.956 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=14.531..19.923 rows=234 loops=1) 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.015..5.155 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=14.483..14.484 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.007..12.118 rows=7373 loops=1) -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=2.576..2.578 rows=0 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=2.951..7.680 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_1" (cost=656.21..817.56 rows=1 width=59) (actual time=2.719..2.720 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=2.718..2.719 rows=0 loops=1) Hash Cond: ((bg_2.pk_region)::text = (d1_2.glbdef2)::text) -> Seq Scan on bd_region bg_2 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=2.702..2.702 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_2 (cost=0.00..656.20 rows=1 width=116) (actual time=2.700..2.701 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_2" (cost=0.00..605.61 rows=1 width=59) (actual time=2.950..4.956 rows=1 loops=1) -> Seq Scan on v_orddddd o_2 (cost=0.00..605.60 rows=1 width=2381) (actual time=2.948..4.953 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Subquery Scan on "*SELECT* 3" (cost=3521.09..5600.38 rows=2727 width=1396) (actual time=36.915..74.611 rows=224 loops=1) -> Gather (cost=3521.09..5573.11 rows=2727 width=1359) (actual time=36.911..74.539 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=2521.09..4300.41 rows=1136 width=1359) (actual time=21.897..27.244 rows=75 loops=3) Hash Cond: (("*SELECT* 2_5".parentorgid)::text = ("*SELECT* 2_4".orgid)::text) -> Parallel Append (cost=660.89..2416.26 rows=3070 width=698) (actual time=14.110..19.108 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_5" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=33.978..43.783 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=33.976..43.035 rows=7136 loops=1) Hash Cond: ((d1_3.glbdef2)::text = (bg_3.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=32.084..37.925 rows=7136 loops=1) Hash Cond: ((d1_3.pk_fatherorg)::text = (dd_2.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=25.785..29.481 rows=7136 loops=1) Merge Cond: ((o1_2.pk_org)::text = (d1_3.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_2 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.057..0.368 rows=232 loops=1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=25.661..26.260 rows=7136 loops=1) Sort Key: d1_3.pk_org Worker 0: Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_3 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.029..6.971 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=6.277..6.278 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_2 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.023..4.599 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.858..1.859 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_3 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.049..1.280 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_6" (cost=660.89..1251.59 rows=234 width=151) (actual time=8.348..13.011 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=8.346..12.980 rows=234 loops=1) Hash Cond: ((o_3.pk_fatherorg)::text = (oo_2.pk_org)::text) -> Seq Scan on v_orddddd o_3 (cost=0.00..587.16 rows=234 width=97) (actual time=0.017..4.422 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.300..8.301 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_2 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.004..6.873 rows=7373 loops=1) -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=7.583..7.589 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=2.675..10.016 rows=6 loops=2) Hash Cond: (("*SELECT* 2_4".parentorgid)::text = ("*SELECT* 2_3".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.009..6.983 rows=3685 loops=2) -> Subquery Scan on "*SELECT* 2_4" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.603..9.925 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.602..9.309 rows=7136 loops=1) Hash Cond: ((d1_4.glbdef2)::text = (bg_4.pk_region)::text) -> Seq Scan on org_dept d1_4 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.012..1.277 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.568..1.569 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_4 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.008..1.052 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_5" (cost=0.00..589.50 rows=234 width=76) (actual time=0.017..3.603 rows=234 loops=1) -> Seq Scan on v_orddddd o_4 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.016..3.581 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=2.651..2.654 rows=0 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=1.978..5.292 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_3" (cost=656.21..817.56 rows=1 width=59) (actual time=1.606..1.607 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=1.605..1.606 rows=0 loops=1) Hash Cond: ((bg_5.pk_region)::text = (d1_5.glbdef2)::text) -> Seq Scan on bd_region bg_5 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=1.595..1.595 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_5 (cost=0.00..656.20 rows=1 width=116) (actual time=1.594..1.594 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_4" (cost=0.00..605.61 rows=1 width=59) (actual time=1.977..3.681 rows=1 loops=1) -> Seq Scan on v_orddddd o_5 (cost=0.00..605.60 rows=1 width=2381) (actual time=1.975..3.678 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Subquery Scan on "*SELECT* 4" (cost=8505.82..11031.71 rows=100490 width=1396) (actual time=145.048..149.362 rows=1518 loops=1) -> Merge Join (cost=8505.82..10026.81 rows=100490 width=1322) (actual time=145.044..149.032 rows=1518 loops=1) Merge Cond: (("*SELECT* 2_8".orgid)::text = ("*SELECT* 1_10".parentorgid)::text) -> Sort (cost=4343.04..4349.85 rows=2727 width=177) (actual time=52.003..52.129 rows=223 loops=1) Sort Key: "*SELECT* 2_8".orgid Sort Method: quicksort Memory: 84kB -> Gather (cost=2860.20..4187.42 rows=2727 width=177) (actual time=36.419..50.726 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1860.20..2914.72 rows=1136 width=177) (actual time=17.751..22.938 rows=75 loops=3) Hash Cond: (("*SELECT* 2_8".parentorgid)::text = ("*SELECT* 2_7".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.818..5.718 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_8" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.393..12.428 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.391..11.746 rows=7136 loops=1) Hash Cond: ((d1_6.glbdef2)::text = (bg_6.pk_region)::text) -> Seq Scan on org_dept d1_6 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.059..2.389 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.295..2.296 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_6 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.022..1.662 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_9" (cost=0.00..589.50 rows=234 width=76) (actual time=0.056..4.198 rows=234 loops=1) -> Seq Scan on v_orddddd o_6 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.055..4.172 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=16.649..16.655 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=6.445..9.504 rows=4 loops=3) Hash Cond: (("*SELECT* 2_7".parentorgid)::text = ("*SELECT* 2_6".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.713..7.082 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_7" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.087..11.687 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.086..11.024 rows=7136 loops=1) Hash Cond: ((d1_7.glbdef2)::text = (bg_7.pk_region)::text) -> Seq Scan on org_dept d1_7 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.027..2.319 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.032..2.033 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_7 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.018..1.451 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_8" (cost=0.00..589.50 rows=234 width=76) (actual time=0.050..9.079 rows=234 loops=1) -> Seq Scan on v_orddddd o_7 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.048..8.994 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=2.125..2.128 rows=0 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=2.070..6.363 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_6" (cost=656.21..817.56 rows=1 width=59) (actual time=2.614..2.616 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=2.613..2.614 rows=0 loops=1) Hash Cond: ((bg_8.pk_region)::text = (d1_8.glbdef2)::text) -> Seq Scan on bd_region bg_8 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=2.605..2.606 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_8 (cost=0.00..656.20 rows=1 width=116) (actual time=2.604..2.604 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_7" (cost=0.00..605.61 rows=1 width=59) (actual time=2.069..3.744 rows=1 loops=1) -> Seq Scan on v_orddddd o_8 (cost=0.00..605.60 rows=1 width=2381) (actual time=2.068..3.741 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Sort (cost=4162.79..4181.21 rows=7370 width=698) (actual time=92.940..93.590 rows=7370 loops=1) Sort Key: "*SELECT* 1_10".parentorgid Sort Method: quicksort Memory: 2145kB -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=9.227..58.925 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_10" (cost=660.89..1251.59 rows=234 width=151) (actual time=9.225..13.047 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=9.223..13.017 rows=234 loops=1) Hash Cond: ((o_9.pk_fatherorg)::text = (oo_3.pk_org)::text) -> Seq Scan on v_orddddd o_9 (cost=0.00..587.16 rows=234 width=97) (actual time=0.035..3.608 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=9.164..9.165 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_3 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.005..7.473 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_9" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=35.840..45.308 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=35.839..44.548 rows=7136 loops=1) Hash Cond: ((d1_9.glbdef2)::text = (bg_9.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=34.127..39.642 rows=7136 loops=1) Hash Cond: ((d1_9.pk_fatherorg)::text = (dd_3.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=27.947..31.386 rows=7136 loops=1) Merge Cond: ((o1_3.pk_org)::text = (d1_9.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_3 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.036..0.275 rows=232 loops=1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=27.835..28.393 rows=7136 loops=1) Sort Key: d1_9.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_9 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.014..5.688 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=6.155..6.156 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_3 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.032..4.695 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.692..1.693 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_9 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.033..1.188 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 5" (cost=31827.36..124691.84 rows=3703056 width=1396) (actual time=200.639..205.266 rows=626 loops=1) -> Merge Join (cost=31827.36..87661.28 rows=3703056 width=1285) (actual time=200.634..205.121 rows=626 loops=1) Merge Cond: (("*SELECT* 1_15".parentorgid)::text = ("*SELECT* 1_14".orgid)::text) -> Sort (cost=4162.79..4181.21 rows=7370 width=698) (actual time=96.617..97.284 rows=7370 loops=1) Sort Key: "*SELECT* 1_15".parentorgid Sort Method: quicksort Memory: 2145kB -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=8.848..55.880 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_15" (cost=660.89..1251.59 rows=234 width=151) (actual time=8.847..12.641 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=8.845..12.612 rows=234 loops=1) Hash Cond: ((o_10.pk_fatherorg)::text = (oo_4.pk_org)::text) -> Seq Scan on v_orddddd o_10 (cost=0.00..587.16 rows=234 width=97) (actual time=0.039..3.549 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.783..8.784 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_4 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.005..7.290 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_14" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=29.553..42.472 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=29.551..41.496 rows=7136 loops=1) Hash Cond: ((d1_10.glbdef2)::text = (bg_10.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=28.006..35.666 rows=7136 loops=1) Hash Cond: ((d1_10.pk_fatherorg)::text = (dd_4.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=22.266..27.108 rows=7136 loops=1) Merge Cond: ((o1_4.pk_org)::text = (d1_10.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_4 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.036..0.340 rows=232 loops=1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=22.176..22.875 rows=7136 loops=1) Sort Key: d1_10.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_10 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.013..5.588 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=5.716..5.717 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_4 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.034..4.361 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.527..1.527 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_10 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.034..1.048 rows=3224 loops=1) -> Materialize (cost=27664.58..28167.03 rows=100490 width=236) (actual time=103.384..103.835 rows=1902 loops=1) -> Sort (cost=27664.58..27915.80 rows=100490 width=236) (actual time=103.376..103.578 rows=1516 loops=1) Sort Key: "*SELECT* 1_14".orgid Sort Method: quicksort Memory: 452kB -> Merge Join (cost=6458.04..7979.02 rows=100490 width=236) (actual time=91.364..94.530 rows=1518 loops=1) Merge Cond: (("*SELECT* 2_12".orgid)::text = ("*SELECT* 1_14".parentorgid)::text) -> Sort (cost=4343.04..4349.85 rows=2727 width=177) (actual time=42.563..42.683 rows=223 loops=1) Sort Key: "*SELECT* 2_12".orgid Sort Method: quicksort Memory: 84kB -> Gather (cost=2860.20..4187.42 rows=2727 width=177) (actual time=28.150..41.673 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1860.20..2914.72 rows=1136 width=177) (actual time=9.243..14.115 rows=75 loops=3) Hash Cond: (("*SELECT* 2_12".parentorgid)::text = ("*SELECT* 2_11".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.719..5.319 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_12" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.115..11.716 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.113..11.046 rows=7136 loops=1) Hash Cond: ((d1_11.glbdef2)::text = (bg_11.pk_region)::text) -> Seq Scan on org_dept d1_11 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.046..2.284 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.022..2.022 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_11 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.022..1.457 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_13" (cost=0.00..589.50 rows=234 width=76) (actual time=0.039..3.744 rows=234 loops=1) -> Seq Scan on v_orddddd o_11 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.038..3.720 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=8.302..8.308 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=2.820..10.305 rows=6 loops=2) Hash Cond: (("*SELECT* 2_11".parentorgid)::text = ("*SELECT* 2_10".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.016..7.132 rows=3685 loops=2) -> Subquery Scan on "*SELECT* 2_11" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.610..10.104 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.608..9.434 rows=7136 loops=1) Hash Cond: ((d1_12.glbdef2)::text = (bg_12.pk_region)::text) -> Seq Scan on org_dept d1_12 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.021..1.335 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.568..1.568 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_12 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.009..1.085 rows=3224 loops= 1) -> Subquery Scan on "*SELECT* 1_12" (cost=0.00..589.50 rows=234 width=76) (actual time=0.032..3.714 rows=234 loops=1) -> Seq Scan on v_orddddd o_12 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.031..3.690 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=2.788..2.791 rows=0 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=2.068..5.566 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_10" (cost=656.21..817.56 rows=1 width=59) (actual time=1.717..1.719 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=1.716..1.717 rows=0 loops=1) Hash Cond: ((bg_13.pk_region)::text = (d1_13.glbdef2)::text) -> Seq Scan on bd_region bg_13 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=1.706..1.707 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_13 (cost=0.00..656.20 rows=1 width=116) (actual time=1.706..1.706 rows=0 loops= 1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_11" (cost=0.00..605.61 rows=1 width=59) (actual time=2.067..3.844 rows=1 loops=1) -> Seq Scan on v_orddddd o_13 (cost=0.00..605.60 rows=1 width=2381) (actual time=2.066..3.841 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=48.714..49.227 rows=7370 loops=1) Sort Key: "*SELECT* 1_14".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.040..14.955 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_14" (cost=0.00..589.50 rows=234 width=76) (actual time=0.039..3.768 rows=234 loops=1) -> Seq Scan on v_orddddd o_14 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.038..3.742 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_13" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.579..10.714 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.578..9.971 rows=7136 loops=1) Hash Cond: ((d1_14.glbdef2)::text = (bg_14.pk_region)::text) -> Seq Scan on org_dept d1_14 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.028..1.423 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.528..1.528 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_14 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.008..1.034 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 6" (cost=160032.84..3572651.11 rows=136457614 width=1396) (actual time=288.496..292.900 rows=727 loops=1) -> Merge Join (cost=160032.84..2208074.97 rows=136457614 width=1248) (actual time=288.493..292.741 rows=727 loops=1) Merge Cond: (("*SELECT* 1_19".orgid)::text = ("*SELECT* 1_20".parentorgid)::text) -> Sort (cost=27664.58..27915.80 rows=100490 width=236) (actual time=108.868..109.085 rows=1515 loops=1) Sort Key: "*SELECT* 1_19".orgid Sort Method: quicksort Memory: 452kB -> Merge Join (cost=6458.04..7979.02 rows=100490 width=236) (actual time=97.184..100.244 rows=1518 loops=1) Merge Cond: (("*SELECT* 2_17".orgid)::text = ("*SELECT* 1_19".parentorgid)::text) -> Sort (cost=4343.04..4349.85 rows=2727 width=177) (actual time=44.332..44.449 rows=223 loops=1) Sort Key: "*SELECT* 2_17".orgid Sort Method: quicksort Memory: 84kB -> Gather (cost=2860.20..4187.42 rows=2727 width=177) (actual time=28.517..43.445 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1860.20..2914.72 rows=1136 width=177) (actual time=11.619..17.306 rows=75 loops=3) Hash Cond: (("*SELECT* 2_17".parentorgid)::text = ("*SELECT* 2_16".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.769..6.155 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_17" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.229..12.211 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.227..11.524 rows=7136 loops=1) Hash Cond: ((d1_15.glbdef2)::text = (bg_15.pk_region)::text) -> Seq Scan on org_dept d1_15 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.060..2.401 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.135..2.136 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_15 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.021..1.569 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_18" (cost=0.00..589.50 rows=234 width=76) (actual time=0.076..4.986 rows=234 loops=1) -> Seq Scan on v_orddddd o_15 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.074..4.960 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=10.595..10.602 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=1.821..7.037 rows=4 loops=3) Hash Cond: (("*SELECT* 2_16".parentorgid)::text = ("*SELECT* 2_15".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.012..4.969 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_16" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.650..10.611 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.648..9.937 rows=7136 loops=1) Hash Cond: ((d1_16.glbdef2)::text = (bg_16.pk_region)::text) -> Seq Scan on org_dept d1_16 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.025..1.403 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.589..1.590 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_16 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.009..1.100 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_17" (cost=0.00..589.50 rows=234 width=76) (actual time=0.036..3.797 rows=234 loops=1) -> Seq Scan on v_orddddd o_16 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.035..3.771 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=1.796..1.799 rows=0 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=2.023..5.381 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_15" (cost=656.21..817.56 rows=1 width=59) (actual time=1.732..1.733 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=1.731..1.732 rows=0 loops=1) Hash Cond: ((bg_17.pk_region)::text = (d1_17.glbdef2)::text) -> Seq Scan on bd_region bg_17 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=1.721..1.722 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_17 (cost=0.00..656.20 rows=1 width=116) (actual time=1.720..1.720 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_16" (cost=0.00..605.61 rows=1 width=59) (actual time=2.022..3.644 rows=1 loops=1) -> Seq Scan on v_orddddd o_17 (cost=0.00..605.60 rows=1 width=2381) (actual time=2.021..3.641 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=52.765..53.240 rows=7370 loops=1) Sort Key: "*SELECT* 1_19".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.048..20.017 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_19" (cost=0.00..589.50 rows=234 width=76) (actual time=0.048..6.369 rows=234 loops=1) -> Seq Scan on v_orddddd o_18 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.046..6.344 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_18" (cost=189.54..1015.22 rows=7136 width=117) (actual time=3.271..13.155 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=3.269..12.344 rows=7136 loops=1) Hash Cond: ((d1_18.glbdef2)::text = (bg_18.pk_region)::text) -> Seq Scan on org_dept d1_18 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.044..1.514 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=3.187..3.187 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_18 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.014..2.054 rows=3224 loops=1) -> Materialize (cost=132368.26..133726.18 rows=271584 width=814) (actual time=178.646..180.171 rows=7369 loops=1) -> Sort (cost=132368.26..133047.22 rows=271584 width=814) (actual time=178.641..179.135 rows=7369 loops=1) Sort Key: "*SELECT* 1_20".parentorgid Sort Method: quicksort Memory: 2747kB -> Merge Join (cost=6277.79..10388.40 rows=271584 width=814) (actual time=149.922..159.534 rows=7369 loops=1) Merge Cond: (("*SELECT* 1_20".orgid)::text = ("*SELECT* 1_21".parentorgid)::text) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=64.207..64.826 rows=7343 loops=1) Sort Key: "*SELECT* 1_20".orgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.057..14.325 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_20" (cost=0.00..589.50 rows=234 width=76) (actual time=0.056..3.784 rows=234 loops=1) -> Seq Scan on v_orddddd o_19 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.055..3.760 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_19" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.552..10.107 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.551..9.412 rows=7136 loops=1) Hash Cond: ((d1_19.glbdef2)::text = (bg_19.pk_region)::text) -> Seq Scan on org_dept d1_19 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.034..1.377 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.496..1.497 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_19 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.009..1.016 rows=3224 loops=1) -> Sort (cost=4162.79..4181.21 rows=7370 width=698) (actual time=85.694..86.371 rows=7370 loops=1) Sort Key: "*SELECT* 1_21".parentorgid Sort Method: quicksort Memory: 2145kB -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=8.431..51.659 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_21" (cost=660.89..1251.59 rows=234 width=151) (actual time=8.429..12.162 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=8.428..12.134 rows=234 loops=1) Hash Cond: ((o_20.pk_fatherorg)::text = (oo_5.pk_org)::text) -> Seq Scan on v_orddddd o_20 (cost=0.00..587.16 rows=234 width=97) (actual time=0.066..3.550 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.338..8.339 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_5 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.006..6.920 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_20" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=29.552..38.989 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=29.551..38.224 rows=7136 loops=1) Hash Cond: ((d1_20.glbdef2)::text = (bg_20.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=27.950..33.457 rows=7136 loops=1) Hash Cond: ((d1_20.pk_fatherorg)::text = (dd_5.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=21.931..25.356 rows=7136 loops=1) Merge Cond: ((o1_5.pk_org)::text = (d1_20.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_5 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.050..0.288 rows=232 loops= 1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=21.838..22.351 rows=7136 loops=1) Sort Key: d1_20.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_20 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.012..5.654 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=5.998..5.999 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_5 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.047..4.538 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.578..1.578 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_20 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.042..1.070 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 7" (cost=1634547.44..127356600.82 rows=5028463076 width=1396) (actual time=342.956..347.828 rows=2943 loops=1) -> Merge Join (cost=1634547.44..77071970.06 rows=5028463076 width=1211) (actual time=342.952..347.136 rows=2943 loops=1) Merge Cond: (("*SELECT* 1_27".parentorgid)::text = ("*SELECT* 1_26".orgid)::text) -> Sort (cost=132368.26..133047.22 rows=271584 width=814) (actual time=180.176..180.810 rows=7369 loops=1) Sort Key: "*SELECT* 1_27".parentorgid Sort Method: quicksort Memory: 2747kB -> Merge Join (cost=6277.79..10388.40 rows=271584 width=814) (actual time=151.506..161.143 rows=7369 loops=1) Merge Cond: (("*SELECT* 1_27".orgid)::text = ("*SELECT* 1_28".parentorgid)::text) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=64.982..65.548 rows=7343 loops=1) Sort Key: "*SELECT* 1_27".orgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.070..14.483 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_27" (cost=0.00..589.50 rows=234 width=76) (actual time=0.069..3.919 rows=234 loops=1) -> Seq Scan on v_orddddd o_21 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.068..3.894 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_26" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.568..10.126 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.567..9.454 rows=7136 loops=1) Hash Cond: ((d1_21.glbdef2)::text = (bg_21.pk_region)::text) -> Seq Scan on org_dept d1_21 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.041..1.390 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.505..1.506 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_21 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.009..1.024 rows=3224 loops=1) -> Sort (cost=4162.79..4181.21 rows=7370 width=698) (actual time=86.509..87.252 rows=7370 loops=1) Sort Key: "*SELECT* 1_28".parentorgid Sort Method: quicksort Memory: 2145kB -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=8.541..51.833 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_28" (cost=660.89..1251.59 rows=234 width=151) (actual time=8.540..12.306 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=8.538..12.275 rows=234 loops=1) Hash Cond: ((o_22.pk_fatherorg)::text = (oo_6.pk_org)::text) -> Seq Scan on v_orddddd o_22 (cost=0.00..587.16 rows=234 width=97) (actual time=0.056..3.554 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.459..8.459 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_6 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.007..7.021 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_27" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=29.507..38.986 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=29.506..38.222 rows=7136 loops=1) Hash Cond: ((d1_22.glbdef2)::text = (bg_22.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=27.888..33.470 rows=7136 loops=1) Hash Cond: ((d1_22.pk_fatherorg)::text = (dd_6.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=21.738..25.174 rows=7136 loops=1) Merge Cond: ((o1_6.pk_org)::text = (d1_22.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_6 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.052..0.295 rows=232 loops=1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=21.644..22.162 rows=7136 loops=1) Sort Key: d1_22.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_22 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.019..5.650 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=6.130..6.131 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_6 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.046..4.685 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.597..1.598 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_22 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.046..1.108 rows=3224 loops=1) -> Materialize (cost=1502179.18..1520694.46 rows=3703056 width=295) (actual time=161.751..162.173 rows=3201 loops=1) -> Sort (cost=1502179.18..1511436.82 rows=3703056 width=295) (actual time=161.745..161.916 rows=604 loops=1) Sort Key: "*SELECT* 1_26".orgid Sort Method: quicksort Memory: 314kB -> Merge Join (cost=29779.58..85613.49 rows=3703056 width=295) (actual time=153.833..158.235 rows=626 loops=1) Merge Cond: (("*SELECT* 1_26".parentorgid)::text = ("*SELECT* 1_25".orgid)::text) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=45.785..46.402 rows=7370 loops=1) Sort Key: "*SELECT* 1_26".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.063..14.323 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_26" (cost=0.00..589.50 rows=234 width=76) (actual time=0.062..3.745 rows=234 loops=1) -> Seq Scan on v_orddddd o_23 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.061..3.719 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_25" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.562..10.153 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.561..9.442 rows=7136 loops=1) Hash Cond: ((d1_23.glbdef2)::text = (bg_23.pk_region)::text) -> Seq Scan on org_dept d1_23 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.038..1.377 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.500..1.501 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_23 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.010..1.020 rows=3224 loops=1) -> Materialize (cost=27664.58..28167.03 rows=100490 width=236) (actual time=107.470..107.971 rows=1902 loops=1) -> Sort (cost=27664.58..27915.80 rows=100490 width=236) (actual time=107.462..107.683 rows=1516 loops=1) Sort Key: "*SELECT* 1_25".orgid Sort Method: quicksort Memory: 452kB -> Merge Join (cost=6458.04..7979.02 rows=100490 width=236) (actual time=95.204..98.485 rows=1518 loops=1) Merge Cond: (("*SELECT* 2_23".orgid)::text = ("*SELECT* 1_25".parentorgid)::text) -> Sort (cost=4343.04..4349.85 rows=2727 width=177) (actual time=42.351..42.477 rows=223 loops=1) Sort Key: "*SELECT* 2_23".orgid Sort Method: quicksort Memory: 84kB -> Gather (cost=2860.20..4187.42 rows=2727 width=177) (actual time=27.652..41.408 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1860.20..2914.72 rows=1136 width=177) (actual time=8.967..13.881 rows=75 loops=3) Hash Cond: (("*SELECT* 2_23".parentorgid)::text = ("*SELECT* 2_22".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.755..5.389 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_23" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.213..11.939 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.211..11.258 rows=7136 loops=1) Hash Cond: ((d1_24.glbdef2)::text = (bg_24.pk_region)::text) -> Seq Scan on org_dept d1_24 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.067..2.323 rows=7136 loops= 1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.107..2.108 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_24 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.027..1.525 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_24" (cost=0.00..589.50 rows=234 width=76) (actual time=0.049..3.718 rows=234 loops=1) -> Seq Scan on v_orddddd o_24 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.048..3.693 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=7.978..7.983 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=2.672..10.107 rows=6 loops=2) Hash Cond: (("*SELECT* 2_22".parentorgid)::text = ("*SELECT* 2_21".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.021..7.074 rows=3685 loops=2) -> Subquery Scan on "*SELECT* 2_22" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.616..10.010 rows=7 136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.615..9.400 rows=7136 loops=1) Hash Cond: ((d1_25.glbdef2)::text = (bg_25.pk_region)::text) -> Seq Scan on org_dept d1_25 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.030..1.334 rows =7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.565..1.566 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_25 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.008..1.08 2 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 1_23" (cost=0.00..589.50 rows=234 width=76) (actual time=0.040..3.710 rows=234 loo ps=1) -> Seq Scan on v_orddddd o_25 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.039..3.686 rows=234 lo ops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=2.637..2.639 rows=0 loops=2) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=1.994..5.266 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_21" (cost=656.21..817.56 rows=1 width=59) (actual time=1.614..1.616 rows=0 loops=1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=1.614..1.615 rows=0 loops=1) Hash Cond: ((bg_26.pk_region)::text = (d1_26.glbdef2)::text) -> Seq Scan on bd_region bg_26 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=1.604..1.605 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_26 (cost=0.00..656.20 rows=1 width=116) (actual time=1.603..1 .604 rows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_22" (cost=0.00..605.61 rows=1 width=59) (actual time=1.994..3.647 rows=1 l oops=1) -> Seq Scan on v_orddddd o_26 (cost=0.00..605.60 rows=1 width=2381) (actual time=1.992..3.645 rows=1 loops=1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=52.768..53.302 rows=7370 loops=1) Sort Key: "*SELECT* 1_25".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.062..15.310 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_25" (cost=0.00..589.50 rows=234 width=76) (actual time=0.061..3.932 rows=234 loops=1) -> Seq Scan on v_orddddd o_27 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.060..3.906 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_24" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.713..10.870 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.712..10.087 rows=7136 loops=1) Hash Cond: ((d1_27.glbdef2)::text = (bg_27.pk_region)::text) -> Seq Scan on org_dept d1_27 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.049..1.478 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.639..1.640 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_27 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.010..1.100 rows=3224 loops=1) -> Subquery Scan on "*SELECT* 8" (cost=10607578.45..4643117485.73 rows=185298864351 width=1396) (actual time=469.246..470.931 rows=1318 loops=1) -> Merge Join (cost=10607578.45..2790128842.22 rows=185298864351 width=1174) (actual time=469.242..470.600 rows=1318 loops=1) Merge Cond: (("*SELECT* 1_33".orgid)::text = ("*SELECT* 1_34".parentorgid)::text) -> Sort (cost=1502179.18..1511436.82 rows=3703056 width=295) (actual time=163.876..164.018 rows=371 loops=1) Sort Key: "*SELECT* 1_33".orgid Sort Method: quicksort Memory: 314kB -> Merge Join (cost=29779.58..85613.49 rows=3703056 width=295) (actual time=156.450..160.565 rows=626 loops=1) Merge Cond: (("*SELECT* 1_33".parentorgid)::text = ("*SELECT* 1_32".orgid)::text) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=51.522..52.094 rows=7370 loops=1) Sort Key: "*SELECT* 1_33".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.074..15.670 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_33" (cost=0.00..589.50 rows=234 width=76) (actual time=0.073..4.075 rows=234 loops=1) -> Seq Scan on v_orddddd o_28 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.072..4.050 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_32" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.663..11.135 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.662..10.390 rows=7136 loops=1) Hash Cond: ((d1_28.glbdef2)::text = (bg_28.pk_region)::text) -> Seq Scan on org_dept d1_28 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.055..1.606 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.582..1.582 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_28 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.010..1.085 rows=3224 loops=1) -> Materialize (cost=27664.58..28167.03 rows=100490 width=236) (actual time=104.346..104.790 rows=1902 loops=1) -> Sort (cost=27664.58..27915.80 rows=100490 width=236) (actual time=104.338..104.540 rows=1516 loops=1) Sort Key: "*SELECT* 1_32".orgid Sort Method: quicksort Memory: 452kB -> Merge Join (cost=6458.04..7979.02 rows=100490 width=236) (actual time=92.515..95.629 rows=1518 loops=1) Merge Cond: (("*SELECT* 2_30".orgid)::text = ("*SELECT* 1_32".parentorgid)::text) -> Sort (cost=4343.04..4349.85 rows=2727 width=177) (actual time=44.003..44.125 rows=223 loops=1) Sort Key: "*SELECT* 2_30".orgid Sort Method: quicksort Memory: 84kB -> Gather (cost=2860.20..4187.42 rows=2727 width=177) (actual time=28.750..42.947 rows=224 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join (cost=1860.20..2914.72 rows=1136 width=177) (actual time=8.019..12.979 rows=75 loops=3) Hash Cond: (("*SELECT* 2_30".parentorgid)::text = ("*SELECT* 2_29".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.741..5.422 rows=2457 loops=3) -> Subquery Scan on "*SELECT* 2_30" (cost=189.54..1015.22 rows=7136 width=117) (actual time=2.176..11.995 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=2.175..11.295 rows=7136 loops=1) Hash Cond: ((d1_29.glbdef2)::text = (bg_29.pk_region)::text) -> Seq Scan on org_dept d1_29 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.051..2.291 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=2.084..2.085 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_29 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.020..1.509 rows=3224 loops= 1) -> Subquery Scan on "*SELECT* 1_31" (cost=0.00..589.50 rows=234 width=76) (actual time=0.044..3.766 rows=234 loops=1) -> Seq Scan on v_orddddd o_29 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.043..3.741 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=1859.81..1859.81 rows=31 width=118) (actual time=7.055..7.060 rows=4 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Hash Join (cost=817.58..1859.81 rows=31 width=118) (actual time=5.908..21.129 rows=13 loops=1) Hash Cond: (("*SELECT* 2_29".parentorgid)::text = ("*SELECT* 2_28".orgid)::text) -> Parallel Append (cost=0.00..1030.57 rows=3070 width=116) (actual time=0.049..14.519 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 2_29" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.664..10.160 rows=7136 lo ops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.663..9.491 rows=7136 loops=1) Hash Cond: ((d1_30.glbdef2)::text = (bg_30.pk_region)::text) -> Seq Scan on org_dept d1_30 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.031..1.322 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.612..1.612 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_30 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.009..1.114 rows =3224 loops=1) -> Subquery Scan on "*SELECT* 1_30" (cost=0.00..589.50 rows=234 width=76) (actual time=0.048..3.904 rows=234 loops=1) -> Seq Scan on v_orddddd o_30 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.047..3.879 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Parallel Hash (cost=817.57..817.57 rows=1 width=59) (actual time=5.836..5.840 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Parallel Append (cost=0.00..817.57 rows=1 width=59) (actual time=2.220..5.826 rows=1 loops=1) -> Subquery Scan on "*SELECT* 2_28" (cost=656.21..817.56 rows=1 width=59) (actual time=1.761..1.763 rows=0 loops =1) -> Hash Right Join (cost=656.21..817.55 rows=1 width=2865) (actual time=1.761..1.762 rows=0 loops=1) Hash Cond: ((bg_31.pk_region)::text = (d1_31.glbdef2)::text) -> Seq Scan on bd_region bg_31 (cost=0.00..149.24 rows=3224 width=21) (never executed) -> Hash (cost=656.20..656.20 rows=1 width=116) (actual time=1.751..1.751 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on org_dept d1_31 (cost=0.00..656.20 rows=1 width=116) (actual time=1.733..1.733 r ows=0 loops=1) Filter: ((code)::text = '000'::text) Rows Removed by Filter: 7136 -> Subquery Scan on "*SELECT* 1_29" (cost=0.00..605.61 rows=1 width=59) (actual time=2.219..4.060 rows=1 loops=1 ) -> Seq Scan on v_orddddd o_31 (cost=0.00..605.60 rows=1 width=2381) (actual time=2.218..4.057 rows=1 loops= 1) Filter: ((isbusinessunit = 'Y'::bpchar) AND ((code)::text = '000'::text)) Rows Removed by Filter: 7372 -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=48.432..48.906 rows=7370 loops=1) Sort Key: "*SELECT* 1_32".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.070..14.856 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_32" (cost=0.00..589.50 rows=234 width=76) (actual time=0.069..3.900 rows=234 loops=1) -> Seq Scan on v_orddddd o_32 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.068..3.875 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_31" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.579..10.523 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.578..9.776 rows=7136 loops=1) Hash Cond: ((d1_32.glbdef2)::text = (bg_32.pk_region)::text) -> Seq Scan on org_dept d1_32 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.052..1.448 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.503..1.503 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_32 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.010..1.041 rows=3224 loops=1) -> Materialize (cost=9105399.27..9155438.62 rows=10007870 width=873) (actual time=302.476..304.057 rows=7356 loops=1) -> Sort (cost=9105399.27..9130418.95 rows=10007870 width=873) (actual time=302.471..302.971 rows=7356 loops=1) Sort Key: "*SELECT* 1_34".parentorgid Sort Method: quicksort Memory: 3964kB -> Merge Join (cost=60223.76..211057.62 rows=10007870 width=873) (actual time=273.314..286.606 rows=7356 loops=1) Merge Cond: (("*SELECT* 1_36".parentorgid)::text = ("*SELECT* 1_35".orgid)::text) -> Sort (cost=4162.79..4181.21 rows=7370 width=698) (actual time=95.849..96.650 rows=7370 loops=1) Sort Key: "*SELECT* 1_36".parentorgid Sort Method: quicksort Memory: 2145kB -> Append (cost=660.89..3689.36 rows=7370 width=698) (actual time=8.791..52.545 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_36" (cost=660.89..1251.59 rows=234 width=151) (actual time=8.790..12.663 rows=234 loops=1) -> Hash Left Join (cost=660.89..1249.25 rows=234 width=1127) (actual time=8.788..12.631 rows=234 loops=1) Hash Cond: ((o_33.pk_fatherorg)::text = (oo_7.pk_org)::text) -> Seq Scan on v_orddddd o_33 (cost=0.00..587.16 rows=234 width=97) (actual time=0.072..3.682 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Hash (cost=568.73..568.73 rows=7373 width=43) (actual time=8.689..8.689 rows=7373 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 611kB -> Seq Scan on v_orddddd oo_7 (cost=0.00..568.73 rows=7373 width=43) (actual time=0.008..7.213 rows=7373 loops=1) -> Subquery Scan on "*SELECT* 2_35" (cost=2025.58..2400.91 rows=7136 width=716) (actual time=29.716..39.361 rows=7136 loops=1) -> Hash Left Join (cost=2025.58..2329.55 rows=7136 width=2176) (actual time=29.714..38.594 rows=7136 loops=1) Hash Cond: ((d1_33.glbdef2)::text = (bg_33.pk_region)::text) -> Hash Left Join (cost=1836.04..1988.37 rows=7136 width=209) (actual time=28.089..33.730 rows=7136 loops=1) Hash Cond: ((d1_33.pk_fatherorg)::text = (dd_7.pk_dept)::text) -> Merge Right Join (cost=1108.48..1242.07 rows=7136 width=187) (actual time=22.330..25.870 rows=7136 loops=1) Merge Cond: ((o1_7.pk_org)::text = (d1_33.pk_org)::text) -> Index Scan using pk_v_orddddd on v_orddddd o1_7 (cost=0.28..1304.68 rows=7373 width=43) (actual time=0.069..0.316 rows=232 loops= 1) -> Sort (cost=1095.10..1112.94 rows=7136 width=165) (actual time=22.215..22.781 rows=7136 loops=1) Sort Key: d1_33.pk_org Sort Method: quicksort Memory: 2083kB -> Seq Scan on org_dept d1_33 (cost=0.00..638.36 rows=7136 width=165) (actual time=0.015..5.621 rows=7136 loops=1) -> Hash (cost=638.36..638.36 rows=7136 width=43) (actual time=5.738..5.738 rows=7136 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 593kB -> Seq Scan on org_dept dd_7 (cost=0.00..638.36 rows=7136 width=43) (actual time=0.067..4.374 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.605..1.605 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_33 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.054..1.121 rows=3224 loops=1) -> Materialize (cost=56060.98..57418.90 rows=271584 width=175) (actual time=177.445..179.416 rows=12294 loops=1) -> Sort (cost=56060.98..56739.94 rows=271584 width=175) (actual time=177.439..178.024 rows=7342 loops=1) Sort Key: "*SELECT* 1_35".orgid Sort Method: quicksort Memory: 2034kB -> Merge Join (cost=4230.00..8340.61 rows=271584 width=175) (actual time=116.482..126.200 rows=7369 loops=1) Merge Cond: (("*SELECT* 1_34".orgid)::text = ("*SELECT* 1_35".parentorgid)::text) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=68.152..68.807 rows=7343 loops=1) Sort Key: "*SELECT* 1_34".orgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.080..14.785 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_34" (cost=0.00..589.50 rows=234 width=76) (actual time=0.079..3.796 rows=234 loops=1) -> Seq Scan on v_orddddd o_34 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.077..3.771 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_33" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.600..10.556 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.599..9.808 rows=7136 loops=1) Hash Cond: ((d1_34.glbdef2)::text = (bg_34.pk_region)::text) -> Seq Scan on org_dept d1_34 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.058..1.546 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.518..1.518 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_34 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.013..1.039 rows=3224 loops=1) -> Sort (cost=2115.00..2133.43 rows=7370 width=116) (actual time=48.317..48.990 rows=7370 loops=1) Sort Key: "*SELECT* 1_35".parentorgid Sort Method: quicksort Memory: 1237kB -> Append (cost=0.00..1641.57 rows=7370 width=116) (actual time=0.088..14.971 rows=7370 loops=1) -> Subquery Scan on "*SELECT* 1_35" (cost=0.00..589.50 rows=234 width=76) (actual time=0.087..3.792 rows=234 loops=1) -> Seq Scan on v_orddddd o_35 (cost=0.00..587.16 rows=234 width=2340) (actual time=0.085..3.767 rows=234 loops=1) Filter: (isbusinessunit = 'Y'::bpchar) Rows Removed by Filter: 7139 -> Subquery Scan on "*SELECT* 2_34" (cost=189.54..1015.22 rows=7136 width=117) (actual time=1.722..10.597 rows=7136 loops=1) -> Hash Left Join (cost=189.54..943.86 rows=7136 width=2865) (actual time=1.721..9.901 rows=7136 loops=1) Hash Cond: ((d1_35.glbdef2)::text = (bg_35.pk_region)::text) -> Seq Scan on org_dept d1_35 (cost=0.00..638.36 rows=7136 width=116) (actual time=0.059..1.465 rows=7136 loops=1) -> Hash (cost=149.24..149.24 rows=3224 width=21) (actual time=1.642..1.642 rows=3224 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 199kB -> Seq Scan on bd_region bg_35 (cost=0.00..149.24 rows=3224 width=21) (actual time=0.013..1.119 rows=3224 loops=1) -> Sort (cost=620.40..620.74 rows=137 width=42) (never executed) Sort Key: v_ededede.pk_org -> Nested Loop (cost=9.61..615.54 rows=137 width=42) (never executed) -> Nested Loop (cost=9.33..569.81 rows=137 width=21) (never executed) -> Nested Loop (cost=8.92..16.91 rows=1 width=42) (never executed) -> Seq Scan on sm_role (cost=0.00..3.98 rows=1 width=21) (never executed) Filter: ((role_code)::text ~~ '%qiwei%'::text) -> Bitmap Heap Scan on v_xxxxxx (cost=8.92..12.93 rows=1 width=21) (never executed) Recheck Cond: (((pk_role)::text = (sm_role.pk_role)::text) AND ((cuserid)::text = '1001A11000000003PYR5'::text)) -> BitmapAnd (cost=8.92..8.92 rows=1 width=0) (never executed) -> Bitmap Index Scan on i_sm_u_r_role (cost=0.00..4.32 rows=6 width=0) (never executed) 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) (never executed) 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) (never executed) Index Cond: (subjectid = (v_xxxxxx.pk_role)::text) Heap Fetches: 0 -> 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: 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 就能出結果，新舊查詢邏輯也驗證過是等價的。😎
同事又找我了，繼續搬磚。。。。🤣🤣🤣🤣
