greenplum left join查询慢join查询非常快。

[liushun520520]

服务器配置都比较好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?

[dsl279879634]

前几天遇到同样的问题，join很快，left join像蜗牛一样慢

[dsl279879634]

建议在pg10上跑这样的sql或者函数，在gp上实难解决。

[php17]

这种问题，有一下几种方式：
1.收集表的统计信息，analyze 表名；
2.两张表的分布键按照关联键来设置
3.可以试试打开orca优化器
4.建议大表关联小表