GP SQL 监控 nmon及SQL 优化方案

这里SQL  执行时只有两个核芯在处理运算，导致处理速度较慢

在使用explain analyze 来分析sql 时，会打印每个阶段sql 执行花费的时间。

1. 如果一个表的数据非常多，最好指定它分布在一个节点上，可以节省网络io 的成本

select attname as column_name from pg_attribute; //查询库中所有表的分布键

1. 如果出现scan table 这样的SQL 解析，则可以通过检索引来让数据能定位到某一条，减少计算量

2. Broadcast Motion 8:8  (slice3; segments: 8)  (cost=0.00..11375.50 rows=109195 width=8)

这种情况就是数据分散，网络传输较多，造成sql 执行效率低下

Limit  (cost=168.09..168.20 rows=5 width=215)
  ->  Gather Motion 8:1  (slice6; segments: 8)  (cost=168.09..168.20 rows=5 width=215)
        Merge Key: (round(tmp.f_curamt / 10000::numeric, 2))
        ->  Limit  (cost=168.09..168.10 rows=1 width=215)
              ->  Sort  (cost=168.09..169.85 rows=88 width=215)
                    Sort Key: (round(tmp.f_curamt / 10000::numeric, 2))
                    ->  Hash Join  (cost=77.44..156.40 rows=88 width=215)
                          Hash Cond: h.s_induscode::text = (tmp.s_induscode || '00'::text)
                          ->  Redistribute Motion 8:8  (slice1; segments: 8)  (cost=0.00..60.50 rows=219 width=28)
                                Hash Key: h.s_induscode
                                ->  Append-only Columnar Scan on tp_industry h  (cost=0.00..25.50 rows=219 width=28)
                          ->  Hash  (cost=68.64..68.64 rows=88 width=192)
                                ->  Redistribute Motion 8:8  (slice5; segments: 8)  (cost=0.00..68.64 rows=88 width=192)
                                      Hash Key: tmp.s_induscode || '00'::text
                                      ->  Subquery Scan tmp  (cost=0.00..47.52 rows=88 width=192)
                                            ->  HashAggregate  (cost=989388.46..989404.30 rows=88 width=192)
                                                  Group By: (substr(f.s_induscode::text, 1, 2))
                                                  ->  Redistribute Motion 8:8  (slice4; segments: 8)  (cost=989328.62..989370.86 rows=88 width=192)
                                                        Hash Key: (substr(f.s_induscode::text, 1, 2))
                                                        ->  HashAggregate  (cost=989328.62..989356.78 rows=88 width=192)
                                                              Group By: substr(f.s_induscode::text, 1, 2)
                                                              ->  Hash Join  (cost=49666.17..717043.88 rows=2269040 width=27)
                                                                    Hash Cond: a.s_bdgsbtcode::text = e.s_bdgsbtcode::text
                                                                    ->  Hash Join  (cost=27371.17..103436.91 rows=68086 width=64)
                                                                          Hash Cond: a.s_roottrecode = b.s_trecode
                                                                          ->  Hash Join  (cost=27359.17..85722.64 rows=68086 width=53)
                                                                                Hash Cond: f.i_entno = a.i_entno
                                                                                ->  Append-only Columnar Scan on tp_enterprise f  (cost=0.00..44363.33 rows=359580 width=13)
                                                                                ->  Hash  (cost=20550.60..20550.60 rows=68086 width=41)
                                                                                      ->  Append  (cost=0.00..20550.60 rows=68086 width=41)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_out_layingdays a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__1 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__2 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__3 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__4 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__5 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__6 a  (cost=0.00..1.00 rows=1 width=152)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__7 a  (cost=0.00..8648.30 rows=38142 width=40)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                                            ->  Append-only Columnar Scan on tna_originc_normal_1_prt_pn__8 a  (cost=0.00..11895.30 rows=29944 width=40)
                                                                                                  Filter: (d_acct >= '2018-01-01'::date AND d_acct <= '2018-10-31'::date) OR (d_acct >= '2017-01-01'::date AND d_acct <= '2017-10-31'::date) OR (d_acct >= '2018-09-01'::date AND d_acct <= '2018-09-30'::date)
                                                                          ->  Hash  (cost=10.00..10.00 rows=20 width=11)
                                                                                ->  Broadcast Motion 8:8  (slice2; segments: 8)  (cost=0.00..10.00 rows=20 width=11)
                                                                                      ->  Append-only Columnar Scan on tp_areatre b  (cost=0.00..8.20 rows=3 width=11)
                                                                    ->  Hash  (cost=11375.50..11375.50 rows=109195 width=8)
                                                                          ->  Broadcast Motion 8:8  (slice3; segments: 8)  (cost=0.00..11375.50 rows=109195 width=8)
                                                                                ->  Append-only Columnar Scan on tp_bdgsbt e  (cost=0.00..1547.95 rows=13650 width=8)
Settings:  optimizer=off
Optimizer status: legacy query optimizer