慢SQL+执行计划：
explain analyze SELECT t1.acb200, t1.aab001, t2.aab004, t2.bab004, t1.aab301, t1.aab302, t1.aab091, t1.aab02x, t1.aae006, t1.aae007, t1.acb205, t1.aae392, t1.aab092, t1.aae004, t1.aae005, t1.bae017, t1.aae014, t1.aae159, t1.aae393, t1.acb116, t1.star, t1.aah013, t1.acb20a, t1.acb20b, t1.acb20c, t1.acb20d, t1.acb20f, t1.dstatus, t1.dsource, t1.aae013, t1.aae011, t1.aae017, t1.aae036, t1.aae019, t1.aae020, t1.aae022, t1.aae024, t1.aae040, t1.aaf011, t1.aaf017, t1.aaf036, t1.aaf019, t1.aaf020, t1.aaf022, t1.aaf024, t1.label, t1.label_id, t2.aab01x, t1.acb20e, t1.aag013, t1.aag011, t1.aag017, t1.aag036, t1.aag019, t1.aag020, t1.aag022, t1.aag024, t1.bcb116, t1.sys_map_id, t1.aab022_sc, t2.aab048, t2.aab049, t2.aab020, t2.aab056, t2.aab022, t2.aab019, t2.aab003, t2.aab007, t2.aab998, t2.aab029, t2.aae045, t1.ade100, t2.ace752, t2.aab01z, ( SELECT t2_1.gpsx FROM cmap t2_1 WHERE t2_1.fid = t1.acb200 ) AS gpsx, ( SELECT t2_1.gpsy FROM cmap t2_1 WHERE t2_1.fid = t1.acb200 ) AS gpsy, ( SELECT count(*) AS count FROM cb35 t2_1 WHERE t2_1.acb200 = t1.acb200 ) AS countcb35, ( SELECT count(*) AS count FROM ce21 t2_1 WHERE t2_1.acb200 = t1.acb200 ) AS countce21, ( SELECT count(*) AS count FROM cb36 t2_1 WHERE t2_1.acb200 = t1.acb200 ) AS countcb36, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND (cb21.dstatus = '0' OR cb21.dstatus = '1' OR cb21.dstatus = '-1' OR cb21.dstatus = '-3')) AS zwzs, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND (cb21.dstatus = '0' OR cb21.dstatus = '1' OR cb21.dstatus = '-1' OR cb21.dstatus = '-3') AND cb21.ace751 = '3' ) AS dsh, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND (cb21.dstatus = '0' OR cb21.dstatus = '1' OR cb21.dstatus = '-1' OR cb21.dstatus = '-3') AND cb21.ace751 = '1' ) AS shtg, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND (cb21.dstatus = '0' OR cb21.dstatus = '1' OR cb21.dstatus = '-1' OR cb21.dstatus = '-3') AND cb21.ace751 = '2' ) AS shbtg, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND cb21.dstatus = '1' AND cb21.ace751 = '1' AND cb21.ace752 = '1') AS dqyxfb, ( SELECT max(cb21.aae397) AS max FROM cb21 WHERE cb21.acb200 = t1.acb200 AND cb21.dstatus = '1' AND cb21.ace751 = '1' AND cb21.ace752 = '1') AS zxfbrq, ( SELECT count(cb21.acb210) AS count FROM cb21 WHERE cb21.acb200 = t1.acb200 AND (cb21.dstatus = '0' OR cb21.dstatus = '1' OR cb21.dstatus = '-1' OR cb21.dstatus = '-3') AND cb21.ace752 = '0' ) AS wfbs, ( SELECT count(*) AS count FROM cc2b t8 WHERE t8.acb200 = t1.acb200 AND (t8.acc2bz = '1' OR t8.acc2bz = '2' OR t8.acc2bz = '3' )) AS sdjls, ( SELECT count(*) AS count FROM cb2b t9 WHERE t9.acb200 = t1.acb200 ) AS msyqs, ( SELECT count(*) AS count FROM cc2b t4 WHERE t4.acb200 = t1.acb200 AND (t4.acc2bz = '1' OR t4.acc2bz = '2' )) AS dmsjls, ( SELECT count(*) AS count FROM v_cc22_cb2c ta WHERE ta.acb200 = t1.acb200 AND ta.acb2c0 IS NULL) AS dfkjls, ( SELECT count(*) AS count FROM cb2b t3 WHERE t3.acb200 = t1.acb200 AND t3.acb2b2 = '2' ) AS spmsyqs, ( SELECT count(*) AS count FROM cb2g t5 WHERE t5.aab001 = t1.aab001 AND t5.acb2g9 = '1' ) AS jlbckcs, ( SELECT count(*) AS count FROM cb2g t6 WHERE t6.aab001 = t1.aab001 AND t6.acb2ga = '1' ) AS jlbsccs, ( SELECT count(*) AS count FROM cb2g t7 WHERE t7.aab001 = t1.aab001 AND t7.acb2gb = '1' ) AS jlbgzcs FROM cb20 t1, ab01 t2 WHERE t1.aab001 = t2.aab001 ; QUERY PLAN --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ Gather (cost=18082.06..41742212.56 rows=60298 width=6284) (actual time=44.345..9982.260 rows=60293 loops=1) Workers Planned: 3 Workers Launched: 3 -> Parallel Hash Join (cost=17082.06..26780.63 rows=19451 width=5904) (actual time=38.275..85.551 rows=150 73 loops=4) Hash Cond: ((t1.aab001)::text = (t2.aab001)::text) -> Parallel Seq Scan on cb20 t1 (cost=0.00..9647.51 rows=19451 width=5303) (actual time=0.017..7.843 rows=15074 loops=4) -> Parallel Hash (cost=16687.03..16687.03 rows=31603 width=634) (actual time=37.969..37.970 rows=152 26 loops=4) Buckets: 131072 Batches: 1 Memory Usage: 14944kB -> Parallel Seq Scan on ab01 t2 (cost=0.00..16687.03 rows=31603 width=634) (actual time=0.017. .24.647 rows=15226 loops=4) SubPlan 1 -> Index Scan using idx_cmap_fid on cmap t2_1 (cost=0.27..8.29 rows=1 width=10) (actual time=0.002..0.00 2 rows=0 loops=60293) Index Cond: ((fid)::text = (t1.acb200)::text) SubPlan 2 -> Index Scan using idx_cmap_fid on cmap t2_1_1 (cost=0.27..8.29 rows=1 width=9) (actual time=0.001..0.0 01 rows=0 loops=60293) Index Cond: ((fid)::text = (t1.acb200)::text) SubPlan 3 -> Aggregate (cost=19.81..19.82 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=60293) -> Index Only Scan using idx_cb35_acb200_641d8a66_cd838e52 on cb35 t2_1_2 (cost=0.41..19.80 rows=4 width=0) (actual time=0.003..0.003 rows=0 loops=60293) Index Cond: (acb200 = (t1.acb200)::text) Heap Fetches: 19106 SubPlan 4 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Only Scan using idx_ce21_acb200_e55c8666_e4eefd90 on ce21 t2_1_3 (cost=0.28..8.29 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: (acb200 = (t1.acb200)::text) Heap Fetches: 20 SubPlan 5 -> Aggregate (cost=273.84..273.85 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Only Scan using idx_cb36_acb200_750f4546_19689f95 on cb36 t2_1_4 (cost=0.40..273.81 rows= 13 width=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: (acb200 = (t1.acb200)::text) Heap Fetches: 147 SubPlan 6 -> Aggregate (cost=52.32..52.33 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=60293) -> Index Scan using idx_cb21_acb200_1310fdc6_ca7ca85a on cb21 (cost=0.42..52.29 rows=12 width=33) (actual time=0.004..0.008 rows=2 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((dstatus)::text = '0'::text) OR ((dstatus)::text = '1'::text) OR ((dstatus)::text = '-1'::text) OR ((dstatus)::text = '-3'::text)) Rows Removed by Filter: 0 SubPlan 7 -> Aggregate (cost=4.45..4.46 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=60293) -> Index Scan using idx_cb21_ace751_d0e911c2_5c290edb on cb21 cb21_1 (cost=0.42..4.45 rows=1 width =33) (actual time=0.046..0.046 rows=0 loops=60293) Index Cond: ((ace751)::text = '3'::text) Filter: (((acb200)::text = (t1.acb200)::text) AND (((dstatus)::text = '0'::text) OR ((dstatus) ::text = '1'::text) OR ((dstatus)::text = '-1'::text) OR ((dstatus)::text = '-3'::text))) Rows Removed by Filter: 50 SubPlan 8 -> Aggregate (cost=52.35..52.36 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=60293) -> Index Scan using idx_cb21_acb200_1310fdc6_ca7ca85a on cb21 cb21_2 (cost=0.42..52.32 rows=12 wid th=33) (actual time=0.003..0.004 rows=2 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((ace751)::text = '1'::text) AND (((dstatus)::text = '0'::text) OR ((dstatus)::text = '1'::text) OR ((dstatus)::text = '-1'::text) OR ((dstatus)::text = '-3'::text))) Rows Removed by Filter: 0 SubPlan 9 -> Aggregate (cost=4.45..4.46 rows=1 width=8) (actual time=0.036..0.036 rows=1 loops=60293) -> Index Scan using idx_cb21_ace751_d0e911c2_5c290edb on cb21 cb21_3 (cost=0.42..4.45 rows=1 width =33) (actual time=0.035..0.035 rows=0 loops=60293) Index Cond: ((ace751)::text = '2'::text) Filter: (((acb200)::text = (t1.acb200)::text) AND (((dstatus)::text = '0'::text) OR ((dstatus) ::text = '1'::text) OR ((dstatus)::text = '-1'::text) OR ((dstatus)::text = '-3'::text))) Rows Removed by Filter: 35 SubPlan 10 -> Aggregate (cost=52.29..52.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=60293) -> Index Scan using idx_cb21_acb200_1310fdc6_ca7ca85a on cb21 cb21_4 (cost=0.42..52.26 rows=12 wid th=33) (actual time=0.002..0.004 rows=2 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((dstatus)::text = '1'::text) AND ((ace751)::text = '1'::text) AND ((ace752)::text = '1'::text)) Rows Removed by Filter: 0 SubPlan 11 -> Aggregate (cost=52.29..52.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=60293) -> Index Scan using idx_cb21_acb200_1310fdc6_ca7ca85a on cb21 cb21_5 (cost=0.42..52.26 rows=12 wid th=8) (actual time=0.002..0.004 rows=2 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((dstatus)::text = '1'::text) AND ((ace751)::text = '1'::text) AND ((ace752)::text = '1'::text)) Rows Removed by Filter: 0 SubPlan 12 -> Aggregate (cost=52.32..52.33 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=60293) -> Index Scan using idx_cb21_acb200_1310fdc6_ca7ca85a on cb21 cb21_6 (cost=0.42..52.32 rows=1 widt h=33) (actual time=0.004..0.004 rows=0 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((ace752)::text = '0'::text) AND (((dstatus)::text = '0'::text) OR ((dstatus)::text = '1'::text) OR ((dstatus)::text = '-1'::text) OR ((dstatus)::text = '-3'::text))) Rows Removed by Filter: 2 SubPlan 13 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cc2b_acb200_ce8ce986_c93f1a0d on cc2b t8 (cost=0.28..8.30 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((acc2bz)::text = '1'::text) OR ((acc2bz)::text = '2'::text) OR ((acc2bz)::text = '3' ::text)) Rows Removed by Filter: 0 SubPlan 14 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Only Scan using idx_cb2b_aaf020_2816e0dc_1d8350ed on cb2b t9 (cost=0.28..8.29 rows=1 widt h=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: (acb200 = (t1.acb200)::text) Heap Fetches: 46 SubPlan 15 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cc2b_acb200_ce8ce986_c93f1a0d on cc2b t4 (cost=0.28..8.30 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: (((acc2bz)::text = '1'::text) OR ((acc2bz)::text = '2'::text)) Rows Removed by Filter: 0 SubPlan 16 -> Aggregate (cost=44.41..44.42 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=60293) -> Nested Loop Left Join (cost=0.28..44.41 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops= 60293) Filter: (b.acb2c0 IS NULL) Rows Removed by Filter: 0 -> Seq Scan on cc22 a (cost=0.00..11.20 rows=4 width=33) (actual time=0.011..0.011 rows=0 lo ops=60293) Filter: ((acb200)::text = (t1.acb200)::text) Rows Removed by Filter: 32 -> Index Scan using idx_cb2c_acc220_e95e0740_45eb90e2 on cb2c b (cost=0.28..8.29 rows=1 widt h=66) (actual time=0.005..0.006 rows=0 loops=24) Index Cond: ((acc220)::text = (a.acc220)::text) SubPlan 17 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cb2b_aaf020_2816e0dc_1d8350ed on cb2b t3 (cost=0.28..8.30 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=60293) Index Cond: ((acb200)::text = (t1.acb200)::text) Filter: ((acb2b2)::text = '2'::text) Rows Removed by Filter: 0 SubPlan 18 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cb2g_aab001_21334c80_421ac83 on cb2g t5 (cost=0.28..8.30 rows=1 width=0) ( actual time=0.002..0.002 rows=0 loops=60293) Index Cond: ((aab001)::text = (t1.aab001)::text) Filter: ((acb2g9)::text = '1'::text) Rows Removed by Filter: 0 SubPlan 19 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cb2g_aab001_21334c80_421ac83 on cb2g t6 (cost=0.28..8.30 rows=1 width=0) ( actual time=0.001..0.001 rows=0 loops=60293) Index Cond: ((aab001)::text = (t1.aab001)::text) Filter: ((acb2ga)::text = '1'::text) Rows Removed by Filter: 0 SubPlan 20 -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=60293) -> Index Scan using idx_cb2g_aab001_21334c80_421ac83 on cb2g t7 (cost=0.28..8.30 rows=1 width=0) ( actual time=0.001..0.001 rows=0 loops=60293) Index Cond: ((aab001)::text = (t1.aab001)::text) Filter: ((acb2gb)::text = '1'::text) Rows Removed by Filter: 0 Planning Time: 2.713 ms Execution Time: 9988.475 ms (125 行记录)
等价改写优化：
SELECT * FROM ( -- 60293 WITH cb21 AS(SELECT * FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') AND cb21.ace751 IN('1','2','3') AND cb21.ace752 IN ('0','1')), cc2b AS(SELECT * FROM cc2b t8 WHERE t8.acc2bz IN ('1' ,'2','3')), cb2b AS (SELECT * FROM cb2b), cb2g AS (SELECT * FROM cb2g WHERE acb2g9 = '1' OR acb2ga = '1' OR acb2gb = '1') SELECT t1.acb200, t1.aab001, t2.aab004, t2.bab004, t1.aab301, t1.aab302, t1.aab091, t1.aab02x, t1.aae006, t1.aae007, t1.acb205, t1.aae392, t1.aab092, t1.aae004, t1.aae005, t1.bae017, t1.aae014, t1.aae159, t1.aae393, t1.acb116, t1.star, t1.aah013, t1.acb20a, t1.acb20b, t1.acb20c, t1.acb20d, t1.acb20f, t1.dstatus, t1.dsource, t1.aae013, t1.aae011, t1.aae017, t1.aae036, t1.aae019, t1.aae020, t1.aae022, t1.aae024, t1.aae040, t1.aaf011, t1.aaf017, t1.aaf036, t1.aaf019, t1.aaf020, t1.aaf022, t1.aaf024, t1.label, t1.label_id, t2.aab01x, t1.acb20e, t1.aag013, t1.aag011, t1.aag017, t1.aag036, t1.aag019, t1.aag020, t1.aag022, t1.aag024, t1.bcb116, t1.sys_map_id, t1.aab022_sc, t2.aab048, t2.aab049, t2.aab020, t2.aab056, t2.aab022, t2.aab019, t2.aab003, t2.aab007, t2.aab998, t2.aab029, t2.aae045, t1.ade100, t2.ace752, t2.aab01z, t2_1.gpsx AS gpsx, -- 等价 t2_1.gpsy AS gpsy, -- 等价 COALESCE(t2_1_1.cnt ,0) AS countcb35, -- 等价 COALESCE(t2_1_2.cnt ,0) AS countce21, -- 等价 COALESCE(t2_1_3.cnt ,0) AS countcb36, -- 等价 COALESCE(cb21_1.cnt ,0) AS zwzs, -- 等价 COALESCE(cb21_2.cnt ,0) AS dsh, -- 等价 COALESCE(cb21_3.cnt ,0) AS shtg, -- 等价 COALESCE(cb21_4.cnt ,0) AS shbtg, -- 等价 COALESCE(cb21_5.cnt ,0) AS dqyxfb, -- 等价 cb21_6.amax AS zxfbrq, -- 等价 COALESCE(cb21_7.cnt ,0) AS wfbs , -- 等价 COALESCE(cc2b_1.cnt ,0) AS sdjls, -- 等价 COALESCE(cb2b_1.cnt ,0) AS msyqs, -- 等价 COALESCE(cc2b_2.cnt ,0) AS dmsjls , COALESCE(ta.cnt ,0) AS dfkjls, COALESCE(cb2b_2.cnt ,0) AS spmsyqs, COALESCE(cb2g_1.cnt ,0) AS jlbckcs, COALESCE(cb2g_2.cnt ,0) AS jlbsccs, COALESCE(cb2g_3.cnt ,0) AS jlbgzcs FROM cb20 t1 INNER JOIN ab01 t2 ON t1.aab001 = t2.aab001 LEFT JOIN cmap t2_1 ON t2_1.fid = t1.acb200 LEFT JOIN (SELECT acb200 , count(*) cnt FROM cb35 GROUP BY acb200) t2_1_1 ON t2_1_1.acb200 = t1.acb200 LEFT JOIN (SELECT acb200 , count(*) cnt FROM ce21 GROUP BY acb200) t2_1_2 ON t2_1_2.acb200 = t1.acb200 LEFT JOIN (SELECT acb200 , count(*) cnt FROM cb36 GROUP BY acb200) t2_1_3 ON t2_1_3.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') GROUP BY cb21.acb200) cb21_1 ON cb21_1.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') AND cb21.ace751 = '3' GROUP BY cb21.acb200) cb21_2 ON cb21_2.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') AND cb21.ace751 = '1' GROUP BY cb21.acb200) cb21_3 ON cb21_3.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') AND cb21.ace751 = '2' GROUP BY cb21.acb200) cb21_4 ON cb21_4.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus = '1' AND cb21.ace751 = '1' AND cb21.ace752 = '1' GROUP BY cb21.acb200) cb21_5 ON cb21_5.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,max(cb21.aae397) amax FROM cb21 WHERE cb21.dstatus = '1' AND cb21.ace751 = '1' AND cb21.ace752 = '1' GROUP BY cb21.acb200) cb21_6 ON cb21_6.acb200 = t1.acb200 LEFT JOIN (SELECT cb21.acb200,count(cb21.acb210) cnt FROM cb21 WHERE cb21.dstatus IN ('0','1','-1','-3') AND cb21.ace752 = '0' GROUP BY cb21.acb200) cb21_7 ON cb21_7.acb200 = t1.acb200 LEFT JOIN (SELECT acb200,COUNT(1) as cnt FROM cc2b WHERE acc2bz IN ('1' ,'2','3') GROUP BY acb200) cc2b_1 ON cc2b_1.acb200 = t1.acb200 LEFT JOIN (SELECT acb200,COUNT(1) as cnt FROM cc2b WHERE acc2bz IN ('1' ,'2') GROUP BY acb200) cc2b_2 ON cc2b_2.acb200 = t1.acb200 LEFT JOIN (SELECT acb200,count(1) cnt FROM cb2b GROUP BY acb200) cb2b_1 ON cb2b_1.acb200 = t1.acb200 LEFT JOIN (SELECT acb200,count(1) cnt FROM cb2b WHERE acb2b2 = '2' GROUP BY acb200) cb2b_2 ON cb2b_2.acb200 = t1.acb200 LEFT JOIN (SELECT count(1) cnt , acb200 FROM v_cc22_cb2c WHERE acb2c0 IS NULL GROUP BY acb200) ta ON ta.acb200 = t1.acb200 LEFT JOIN (SELECT aab001,count(1) cnt FROM cb2g WHERE acb2g9 = '1' GROUP BY aab001) cb2g_1 ON cb2g_1.aab001 = t1.aab001 LEFT JOIN (SELECT aab001,count(1) cnt FROM cb2g WHERE acb2ga = '1' GROUP BY aab001) cb2g_2 ON cb2g_2.aab001 = t1.aab001 LEFT JOIN (SELECT aab001,count(1) cnt FROM cb2g WHERE acb2gb = '1' GROUP BY aab001) cb2g_3 ON cb2g_3.aab001 = t1.aab001 ) x ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------ Hash Left Join (cost=85835.02..106520.74 rows=60298 width=163) (actual time=1758.644..2221.320 rows=60293 loops=1) Hash Cond: ((t1.aab001)::text = (cb2g_3.aab001)::text) CTE cb21 -> Seq Scan on cb21 cb21_14 (cost=0.00..46231.85 rows=115957 width=3317) (actual time=0.027..268.716 rows=115923 loops=1) Filter: (((ace752)::text = ANY ('{0,1}'::text[])) AND ((ace751)::text = ANY ('{1,2,3}'::text[])) AND ((dstatus)::text = ANY ('{0,1,-1,-3}'::te xt[]))) Rows Removed by Filter: 82 CTE cc2b -> Seq Scan on cc2b t8 (cost=0.00..237.71 rows=1044 width=796) (actual time=0.037..1.067 rows=1044 loops=1) Filter: ((acc2bz)::text = ANY ('{1,2,3}'::text[])) Rows Removed by Filter: 26 CTE cb2b -> Seq Scan on cb2b cb2b_4 (cost=0.00..166.92 rows=1092 width=2554) (actual time=0.014..0.362 rows=1050 loops=1) CTE cb2g -> Seq Scan on cb2g cb2g_6 (cost=0.00..141.27 rows=1044 width=829) (actual time=0.012..0.674 rows=1107 loops=1) Filter: (((acb2g9)::text = '1'::text) OR ((acb2ga)::text = '1'::text) OR ((acb2gb)::text = '1'::text)) Rows Removed by Filter: 14 -> Hash Left Join (cost=39033.52..59560.94 rows=60298 width=188) (actual time=1756.963..2186.922 rows=60293 loops=1) Hash Cond: ((t1.aab001)::text = (cb2g_2.aab001)::text) -> Hash Left Join (cost=39009.77..59378.90 rows=60298 width=180) (actual time=1755.200..2166.051 rows=60293 loops=1) Hash Cond: ((t1.aab001)::text = (cb2g_1.aab001)::text) -> Hash Left Join (cost=38986.03..59196.86 rows=60298 width=172) (actual time=1752.585..2144.321 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (a.acb200)::text) -> Hash Left Join (cost=38857.97..58910.52 rows=60298 width=197) (actual time=1752.376..2129.311 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb2b_2.acb200)::text) -> Hash Left Join (cost=38833.15..58727.39 rows=60298 width=189) (actual time=1751.816..2111.998 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb2b_1.acb200)::text) -> Hash Left Join (cost=38799.35..58535.30 rows=60298 width=181) (actual time=1749.674..2091.094 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cc2b_2.acb200)::text) -> Hash Left Join (cost=38775.29..58352.95 rows=60298 width=173) (actual time=1747.746..2070.861 rows=60293 loop s=1) Hash Cond: ((t1.acb200)::text = (cc2b_1.acb200)::text) -> Hash Left Join (cost=38749.93..58169.30 rows=60298 width=165) (actual time=1744.963..2049.074 rows=6029 3 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_7.acb200)::text) -> Hash Left Join (cost=35560.41..54821.49 rows=60298 width=157) (actual time=1670.982..1959.360 row s=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_6.acb200)::text) -> Hash Left Join (cost=32371.54..51474.33 rows=60298 width=149) (actual time=1450.036..1711.6 73 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_5.acb200)::text) -> Hash Left Join (cost=29182.67..48127.16 rows=60298 width=141) (actual time=1206.695.. 1440.051 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_4.acb200)::text) -> Hash Left Join (cost=25993.16..44779.36 rows=60298 width=133) (actual time=1132 .026..1350.596 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_3.acb200)::text) -> Hash Left Join (cost=22803.65..41431.55 rows=60298 width=125) (actual tim e=882.315..1071.493 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_2.acb200)::text) -> Hash Left Join (cost=19614.14..38083.75 rows=60298 width=117) (actu al time=807.346..981.344 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (cb21_1.acb200)::text) -> Hash Left Join (cost=16697.12..35008.43 rows=60298 width=109) (actual time=72.965..217.540 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (t2_1_3.acb200)::text) -> Hash Left Join (cost=14231.50..32384.52 rows=60298 widt h=101) (actual time=70.667..200.750 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (t2_1_2.acb200)::text) -> Hash Left Join (cost=14079.89..32074.61 rows=6029 8 width=93) (actual time=69.079..183.247 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (t2_1_1.acb200): :text) -> Hash Left Join (cost=10821.28..28657.72 row s=60298 width=85) (actual time=50.980..144.507 rows=60293 loops=1) Hash Cond: ((t1.acb200)::text = (t2_1.fid) ::text) -> Hash Join (cost=10809.70..28417.56 ro ws=60298 width=66) (actual time=50.820..129.392 rows=60293 loops=1) Hash Cond: ((t2.aab001)::text = (t1. aab001)::text) -> Seq Scan on ab01 t2 (cost=0.00. .17350.68 rows=97968 width=33) (actual time=0.014..35.611 rows=60905 loops=1) -> Hash (cost=10055.98..10055.98 r ows=60298 width=66) (actual time=50.747..50.747 rows=60298 loops=1) Buckets: 65536 Batches: 1 Me mory Usage: 6283kB -> Seq Scan on cb20 t1 (cost =0.00..10055.98 rows=60298 width=66) (actual time=0.006..30.571 rows=60298 loops=1) -> Hash (cost=8.48..8.48 rows=248 width= 52) (actual time=0.147..0.147 rows=246 loops=1) Buckets: 1024 Batches: 1 Memory Us age: 29kB -> Seq Scan on cmap t2_1 (cost=0.0 0..8.48 rows=248 width=52) (actual time=0.015..0.075 rows=248 loops=1) -> Hash (cost=3203.40..3203.40 rows=4416 width =41) (actual time=18.079..18.080 rows=4420 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 3 80kB -> Subquery Scan on t2_1_1 (cost=3115.08 ..3203.40 rows=4416 width=41) (actual time=15.494..16.995 rows=4420 loops=1) -> HashAggregate (cost=3115.08..31 59.24 rows=4416 width=41) (actual time=15.493..16.569 rows=4420 loops=1) Group Key: cb35.acb200 -> Seq Scan on cb35 (cost=0. 00..3019.72 rows=19072 width=33) (actual time=0.012..7.302 rows=19107 loops=1) -> Hash (cost=138.62..138.62 rows=1039 width=41) (ac tual time=1.564..1.566 rows=1008 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 88kB -> Subquery Scan on t2_1_2 (cost=117.85..138.6 2 rows=1039 width=41) (actual time=1.025..1.331 rows=1009 loops=1) -> HashAggregate (cost=117.85..128.24 ro ws=1039 width=41) (actual time=1.023..1.232 rows=1009 loops=1) Group Key: ce21.acb200 -> Seq Scan on ce21 (cost=0.00..11 2.23 rows=1123 width=33) (actual time=0.016..0.414 rows=1091 loops=1) -> Hash (cost=2465.47..2465.47 rows=12 width=41) (actual t ime=2.288..2.289 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on t2_1_3 (cost=2464.08..2465.47 ro ws=12 width=41) (actual time=2.245..2.283 rows=12 loops=1) -> GroupAggregate (cost=2464.08..2465.35 rows= 12 width=41) (actual time=2.243..2.279 rows=12 loops=1) Group Key: cb36.acb200 -> Sort (cost=2464.08..2464.46 rows=153 width=33) (actual time=2.232..2.240 rows=153 loops=1) Sort Key: cb36.acb200 Sort Method: quicksort Memory: 36kB -> Seq Scan on cb36 (cost=0.00..24 58.53 rows=153 width=33) (actual time=0.011..2.157 rows=153 loops=1) -> Hash (cost=2914.52..2914.52 rows=200 width=90) (actual time=7 34.353..734.354 rows=12901 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1048kB -> Subquery Scan on cb21_1 (cost=2910.52..2914.52 rows=200 width=90) (actual time=724.992..730.092 rows=12901 loops=1) -> HashAggregate (cost=2910.52..2912.52 rows=200 wid th=90) (actual time=724.991..728.855 rows=12901 loops=1) Group Key: cb21.acb200 -> CTE Scan on cb21 (cost=0.00..2898.93 rows=2 319 width=164) (actual time=0.039..666.222 rows=115923 loops=1) Filter: ((dstatus)::text = ANY ('{0,1,-1,- 3}'::text[])) -> Hash (cost=3189.36..3189.36 rows=12 width=90) (actual time=74.950.. 74.951 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on cb21_2 (cost=3189.03..3189.36 rows=12 width= 90) (actual time=74.929..74.942 rows=7 loops=1) -> GroupAggregate (cost=3189.03..3189.24 rows=12 width=90) (actual time=74.928..74.939 rows=7 loops=1) Group Key: cb21_8.acb200 -> Sort (cost=3189.03..3189.06 rows=12 width=164) (a ctual time=74.914..74.916 rows=26 loops=1) Sort Key: cb21_8.acb200 Sort Method: quicksort Memory: 28kB -> CTE Scan on cb21 cb21_8 (cost=0.00..3188.82 rows=12 width=164) (actual time=0.010..74.882 rows=26 loops=1) Filter: (((ace751)::text = '3'::text) AND ((dstatus)::text = ANY ('{0,1,-1,-3}'::text[]))) Rows Removed by Filter: 115897 -> Hash (cost=3189.36..3189.36 rows=12 width=90) (actual time=249.687..249.6 89 rows=12899 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usag e: 1048kB -> Subquery Scan on cb21_3 (cost=3189.03..3189.36 rows=12 width=90) (a ctual time=191.225..245.617 rows=12899 loops=1) -> GroupAggregate (cost=3189.03..3189.24 rows=12 width=90) (actu al time=191.224..244.209 rows=12899 loops=1) Group Key: cb21_9.acb200 -> Sort (cost=3189.03..3189.06 rows=12 width=164) (actual time=191.201..216.589 rows=115894 loops=1) Sort Key: cb21_9.acb200 Sort Method: quicksort Memory: 19370kB -> CTE Scan on cb21 cb21_9 (cost=0.00..3188.82 rows= 12 width=164) (actual time=0.018..91.818 rows=115894 loops=1) Filter: (((ace751)::text = '1'::text) AND ((dsta tus)::text = ANY ('{0,1,-1,-3}'::text[]))) Rows Removed by Filter: 29 -> Hash (cost=3189.36..3189.36 rows=12 width=90) (actual time=74.647..74.648 rows= 3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on cb21_4 (cost=3189.03..3189.36 rows=12 width=90) (actual time=74.633..74.637 rows=3 loops=1) -> GroupAggregate (cost=3189.03..3189.24 rows=12 width=90) (actual tim e=74.632..74.635 rows=3 loops=1) Group Key: cb21_10.acb200 -> Sort (cost=3189.03..3189.06 rows=12 width=164) (actual time=7 4.616..74.617 rows=3 loops=1) Sort Key: cb21_10.acb200 Sort Method: quicksort Memory: 25kB -> CTE Scan on cb21 cb21_10 (cost=0.00..3188.82 rows=12 wi dth=164) (actual time=0.018..74.601 rows=3 loops=1) Filter: (((ace751)::text = '2'::text) AND ((dstatus):: text = ANY ('{0,1,-1,-3}'::text[]))) Rows Removed by Filter: 115920 -> Hash (cost=3188.86..3188.86 rows=1 width=90) (actual time=243.321..243.324 rows=12889 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1047kB -> Subquery Scan on cb21_5 (cost=3188.83..3188.86 rows=1 width=90) (actual time=18 4.872..239.625 rows=12889 loops=1) -> GroupAggregate (cost=3188.83..3188.85 rows=1 width=90) (actual time=184.8 70..238.239 rows=12889 loops=1) Group Key: cb21_11.acb200 -> Sort (cost=3188.83..3188.83 rows=1 width=164) (actual time=184.849. .210.431 rows=115867 loops=1) Sort Key: cb21_11.acb200 Sort Method: quicksort Memory: 19366kB -> CTE Scan on cb21 cb21_11 (cost=0.00..3188.82 rows=1 width=164 ) (actual time=0.017..88.159 rows=115867 loops=1) Filter: (((dstatus)::text = '1'::text) AND ((ace751)::text = '1'::text) AND ((ace752)::text = '1'::text)) Rows Removed by Filter: 56 -> Hash (cost=3188.86..3188.86 rows=1 width=90) (actual time=220.924..220.926 rows=12889 loops =1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1047kB -> Subquery Scan on cb21_6 (cost=3188.83..3188.86 rows=1 width=90) (actual time=167.064. .217.446 rows=12889 loops=1) -> GroupAggregate (cost=3188.83..3188.85 rows=1 width=90) (actual time=167.062..21 6.045 rows=12889 loops=1) Group Key: cb21_12.acb200 -> Sort (cost=3188.83..3188.83 rows=1 width=90) (actual time=167.038..188.38 9 rows=115867 loops=1) Sort Key: cb21_12.acb200 Sort Method: quicksort Memory: 12125kB -> CTE Scan on cb21 cb21_12 (cost=0.00..3188.82 rows=1 width=90) (actu al time=0.017..87.220 rows=115867 loops=1) Filter: (((dstatus)::text = '1'::text) AND ((ace751)::text = '1':: text) AND ((ace752)::text = '1'::text)) Rows Removed by Filter: 56 -> Hash (cost=3189.36..3189.36 rows=12 width=90) (actual time=73.962..73.964 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Subquery Scan on cb21_7 (cost=3189.03..3189.36 rows=12 width=90) (actual time=73.929..73.94 7 rows=17 loops=1) -> GroupAggregate (cost=3189.03..3189.24 rows=12 width=90) (actual time=73.928..73.943 r ows=17 loops=1) Group Key: cb21_13.acb200 -> Sort (cost=3189.03..3189.06 rows=12 width=164) (actual time=73.914..73.917 rows =35 loops=1) Sort Key: cb21_13.acb200 Sort Method: quicksort Memory: 29kB -> CTE Scan on cb21 cb21_13 (cost=0.00..3188.82 rows=12 width=164) (actual t ime=0.023..73.826 rows=35 loops=1) Filter: (((ace752)::text = '0'::text) AND ((dstatus)::text = ANY ('{0,1, -1,-3}'::text[]))) Rows Removed by Filter: 115888 -> Hash (cost=25.17..25.17 rows=15 width=90) (actual time=2.768..2.770 rows=1021 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 81kB -> Subquery Scan on cc2b_1 (cost=24.88..25.17 rows=15 width=90) (actual time=2.178..2.463 rows=1021 loops=1) -> HashAggregate (cost=24.88..25.02 rows=15 width=90) (actual time=2.177..2.364 rows=1021 loop s=1) Group Key: cc2b.acb200 -> CTE Scan on cc2b (cost=0.00..24.80 rows=16 width=82) (actual time=0.042..1.796 rows=1 044 loops=1) Filter: ((acc2bz)::text = ANY ('{1,2,3}'::text[])) -> Hash (cost=23.93..23.93 rows=10 width=90) (actual time=1.920..1.922 rows=1021 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 81kB -> Subquery Scan on cc2b_2 (cost=23.66..23.93 rows=10 width=90) (actual time=0.950..1.563 rows=1021 loops= 1) -> GroupAggregate (cost=23.66..23.83 rows=10 width=90) (actual time=0.949..1.455 rows=1021 loops=1) Group Key: cc2b_3.acb200 -> Sort (cost=23.66..23.68 rows=10 width=82) (actual time=0.944..1.009 rows=1039 loops=1) Sort Key: cc2b_3.acb200 Sort Method: quicksort Memory: 130kB -> CTE Scan on cc2b cc2b_3 (cost=0.00..23.49 rows=10 width=82) (actual time=0.003..0.426 rows=1039 loops=1) Filter: ((acc2bz)::text = ANY ('{1,2}'::text[])) Rows Removed by Filter: 5 -> Hash (cost=31.30..31.30 rows=200 width=90) (actual time=2.135..2.136 rows=1008 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Subquery Scan on cb2b_1 (cost=27.30..31.30 rows=200 width=90) (actual time=1.616..1.904 rows=1008 loops=1) -> HashAggregate (cost=27.30..29.30 rows=200 width=90) (actual time=1.616..1.806 rows=1008 loops=1) Group Key: cb2b.acb200 -> CTE Scan on cb2b (cost=0.00..21.84 rows=1092 width=82) (actual time=0.016..1.127 rows=1050 loops= 1) -> Hash (cost=24.77..24.77 rows=5 width=90) (actual time=0.551..0.553 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on cb2b_2 (cost=24.63..24.77 rows=5 width=90) (actual time=0.545..0.549 rows=3 loops=1) -> GroupAggregate (cost=24.63..24.72 rows=5 width=90) (actual time=0.544..0.547 rows=3 loops=1) Group Key: cb2b_3.acb200 -> Sort (cost=24.63..24.64 rows=5 width=82) (actual time=0.540..0.540 rows=7 loops=1) Sort Key: cb2b_3.acb200 Sort Method: quicksort Memory: 25kB -> CTE Scan on cb2b cb2b_3 (cost=0.00..24.57 rows=5 width=82) (actual time=0.518..0.527 rows=7 loops =1) Filter: ((acb2b2)::text = '2'::text) Rows Removed by Filter: 1043 -> Hash (cost=128.04..128.04 rows=1 width=41) (actual time=0.201..0.203 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> GroupAggregate (cost=128.01..128.03 rows=1 width=41) (actual time=0.194..0.200 rows=4 loops=1) Group Key: a.acb200 -> Sort (cost=128.01..128.01 rows=1 width=33) (actual time=0.189..0.191 rows=17 loops=1) Sort Key: a.acb200 Sort Method: quicksort Memory: 26kB -> Nested Loop Left Join (cost=0.28..128.00 rows=1 width=33) (actual time=0.036..0.178 rows=17 loops=1) Filter: (b.acb2c0 IS NULL) Rows Removed by Filter: 15 -> Seq Scan on cc22 a (cost=0.00..11.16 rows=16 width=66) (actual time=0.014..0.040 rows=32 loops=1) -> Index Scan using idx_cb2c_acc220_e95e0740_45eb90e2 on cb2c b (cost=0.28..7.29 rows=1 width=66) (actual time=0.004..0.004 rows=0 loops=32) Index Cond: ((acc220)::text = (a.acc220)::text) -> Hash (cost=23.69..23.69 rows=5 width=90) (actual time=2.609..2.611 rows=1008 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Subquery Scan on cb2g_1 (cost=23.55..23.69 rows=5 width=90) (actual time=1.658..2.281 rows=1008 loops=1) -> GroupAggregate (cost=23.55..23.64 rows=5 width=90) (actual time=1.657..2.175 rows=1008 loops=1) Group Key: cb2g.aab001 -> Sort (cost=23.55..23.56 rows=5 width=82) (actual time=1.653..1.721 rows=1107 loops=1) Sort Key: cb2g.aab001 Sort Method: quicksort Memory: 135kB -> CTE Scan on cb2g (cost=0.00..23.49 rows=5 width=82) (actual time=0.015..1.147 rows=1107 loops=1) Filter: ((acb2g9)::text = '1'::text) -> Hash (cost=23.69..23.69 rows=5 width=90) (actual time=1.756..1.757 rows=1004 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Subquery Scan on cb2g_2 (cost=23.55..23.69 rows=5 width=90) (actual time=0.829..1.428 rows=1004 loops=1) -> GroupAggregate (cost=23.55..23.64 rows=5 width=90) (actual time=0.828..1.322 rows=1004 loops=1) Group Key: cb2g_4.aab001 -> Sort (cost=23.55..23.56 rows=5 width=82) (actual time=0.825..0.884 rows=1016 loops=1) Sort Key: cb2g_4.aab001 Sort Method: quicksort Memory: 104kB -> CTE Scan on cb2g cb2g_4 (cost=0.00..23.49 rows=5 width=82) (actual time=0.008..0.322 rows=1016 loops=1) Filter: ((acb2ga)::text = '1'::text) Rows Removed by Filter: 91 -> Hash (cost=23.69..23.69 rows=5 width=90) (actual time=1.666..1.668 rows=1005 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Subquery Scan on cb2g_3 (cost=23.55..23.69 rows=5 width=90) (actual time=0.808..1.416 rows=1005 loops=1) -> GroupAggregate (cost=23.55..23.64 rows=5 width=90) (actual time=0.807..1.308 rows=1005 loops=1) Group Key: cb2g_5.aab001 -> Sort (cost=23.55..23.56 rows=5 width=82) (actual time=0.804..0.869 rows=1015 loops=1) Sort Key: cb2g_5.aab001 Sort Method: quicksort Memory: 104kB -> CTE Scan on cb2g cb2g_5 (cost=0.00..23.49 rows=5 width=82) (actual time=0.008..0.319 rows=1015 loops=1) Filter: ((acb2gb)::text = '1'::text) Rows Removed by Filter: 92 Planning Time: 9.770 ms Execution Time: 2232.544 ms (237 行记录) 时间：2254.037 ms (00:02.254) 时间：0.161 ms
声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
