服務器配置都比較好32核，128Gb，六臺服務器集群。
EXPLAIN ANALYZE SELECT * FROM wifiinfo3 bb
Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..65233.65 rows=638166784 width=36)
Rows out: 638164281 rows at destination with 3.061 ms to first row, 257551 ms to end, start offset by 0.492 ms.
-> Table Scan on wifiinfo3 (cost=0.00..2361.45 rows=53180566 width=36)
Rows out: Avg 53180356.8 rows x 12 workers. Max 53186352 rows (seg4) with 0.098 ms to first row, 13416 ms to end, start offset by 1.495 ms.
Slice statistics:
(slice0) Executor memory: 322K bytes.
(slice1) Executor memory: 162K bytes avg x 12 workers, 179K bytes max (seg4).
Statement statistics:
Memory used: 128000K bytes
Settings: effective_cache_size=64000MB; enable_indexscan=on; enable_seqscan=off
Optimizer status: PQO version 2.53.8
Total runtime: 300824.082 ms
EXPLAIN ANALYZE SELECT * FROM aaa aa
Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..431.00 rows=1 width=28)
Rows out: 1 rows at destination with 53 ms to end, start offset by 0.513 ms.
-> Table Scan on aaa (cost=0.00..431.00 rows=1 width=28)
Rows out: 1 rows (seg4) with 13 ms to end, start offset by 38 ms.
Slice statistics:
(slice0) Executor memory: 322K bytes.
(slice1) Executor memory: 155K bytes avg x 12 workers, 155K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: effective_cache_size=64000MB; enable_indexscan=on; enable_seqscan=off
Optimizer status: PQO version 2.53.8
Total runtime: 56.018 ms
EXPLAIN ANALYZE SELECT * FROM aaa aa JOIN wifiinfo3 bb ON aa.f_id = bb.mac LIMIT 1
Limit (cost=0.00..431.50 rows=1 width=64)
Rows out: 1 rows with 0.984 ms to first row, 0.985 ms to end, start offset by 0.516 ms.
-> Gather Motion 12:1 (slice1; segments: 12) (cost=0.00..431.50 rows=1 width=64)
Rows out: 1 rows at destination with 0.972 ms to end, start offset by 0.527 ms.
-> Nested Loop (cost=0.00..431.50 rows=1 width=64)
Join Filter: true
Rows out: 1 rows (seg4) with 0.355 ms to first row, 0.364 ms to end, start offset by 1.043 ms.
-> Table Scan on aaa (cost=0.00..431.00 rows=1 width=28)
Rows out: 1 rows (seg4) with 0.128 ms to first row, 0.131 ms to end, start offset by 1.049 ms.
-> Index Scan using wifiinfo3_pkey on wifiinfo3 (cost=0.00..0.50 rows=1 width=36)
Index Cond: wifiinfo3.mac = aaa.f_id
Rows out: 1 rows (seg4) with 0.201 ms to first row, 0.204 ms to end, start offset by 1.195 ms.
Slice statistics:
(slice0) Executor memory: 386K bytes.
(slice1) Executor memory: 169K bytes avg x 12 workers, 193K bytes max (seg4).
Statement statistics:
Memory used: 128000K bytes
Settings: effective_cache_size=64000MB; enable_indexscan=on; enable_seqscan=off
Optimizer status: PQO version 2.53.8
Total runtime: 1.742 ms
EXPLAIN ANALYZE SELECT * FROM aaa aa LEFT JOIN wifiinfo3 bb ON aa.f_id = bb.mac LIMIT 1
Limit (cost=0.00..102400000086410752.00 rows=1 width=64)
Rows out: 1 rows with 947853 ms to end, start offset by 13 ms.
-> Gather Motion 12:1 (slice2; segments: 12) (cost=0.00..102400000086410752.00 rows=2 width=64)
Rows out: 1 rows at destination with 947853 ms to end, start offset by 13 ms.
-> Nested Loop Left Join (cost=0.00..102400000086410752.00 rows=1 width=64)
Join Filter: aaa.f_id = wifiinfo3.mac
Rows out: 1 rows (seg4) with 797049 ms to first row, 947850 ms to end, start offset by 16 ms.
-> Table Scan on aaa (cost=0.00..431.00 rows=1 width=28)
Rows out: 1 rows (seg4) with 70 ms to end, start offset by 795741 ms.
-> Materialize (cost=0.00..100000000028896.42 rows=638166784 width=36)
Rows out: Avg 53180357.8 rows x 12 workers. Max 638164282 rows (seg4) with 795725 ms to first row, 871834 ms to end of 2 scans, start offset by 795811 ms.
Work_mem used: 127584K bytes avg, 127584K bytes max (seg0). Workfile: (12 spilling)
Work_mem wanted: 32466211K bytes avg, 32466240K bytes max (seg4) to lessen workfile I/O affecting 12 workers.
-> Broadcast Motion 12:12 (slice1; segments: 12) (cost=0.00..100000000005922.42 rows=638166784 width=36)
Rows out: Avg 638164281.0 rows x 12 workers at destination. Max 638164281 rows (seg0) with 0.144 ms to first row, 590632 ms to end, start offset by 17 ms.
-> Table Scan on wifiinfo3 (cost=0.00..2361.45 rows=53180566 width=36)
Rows out: Avg 53180356.8 rows x 12 workers. Max 53186352 rows (seg4) with 0.472 ms to first row, 15394 ms to end, start offset by 15 ms.
Slice statistics:
(slice0) Executor memory: 386K bytes.
(slice1) Executor memory: 368K bytes avg x 12 workers, 442K bytes max (seg11).
(slice2) * Executor memory: 127963K bytes avg x 12 workers, 127963K bytes max (seg0). Work_mem: 127584K bytes max, 32466240K bytes wanted.
Statement statistics:
Memory used: 128000K bytes
Memory wanted: 32466840K bytes
Settings: effective_cache_size=64000MB; enable_indexscan=on; enable_seqscan=off
Optimizer status: PQO version 2.53.8
Total runtime: 947866.820 ms
that's ok?
uj5u.com熱心網友回復：
前幾天遇到同樣的問題，join很快，left join像蝸牛一樣慢uj5u.com熱心網友回復：
建議在pg10上跑這樣的sql或者函式，在gp上實難解決。uj5u.com熱心網友回復：
這種問題，有一下幾種方式：1.收集表的統計資訊，analyze 表名；
2.兩張表的分布鍵按照關聯鍵來設定
3.可以試試打開orca優化器
4.建議大表關聯小表
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/78206.html
標籤：Greenplum
上一篇：greenplum 擴容失敗