这里SQL 执行时只有两个核芯在处理运算,导致处理速度较慢
在使用explain analyze 来分析sql 时,会打印每个阶段sql 执行花费的时间。
-
如果一个表的数据非常多,最好指定它分布在一个节点上,可以节省网络io 的成本
select attname as column_name from pg_attribute; //查询库中所有表的分布键
-
如果出现scan table 这样的SQL 解析,则可以通过检索引来让数据能定位到某一条,减少计算量
-
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