Server configuration are good 32 nuclear, 128 gb, six server cluster,
The EXPLAIN ANALYZE the 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 the first row, 257551 ms to the end, the start offset by 0.492 ms.
-> A Table Scan on wifiinfo3 (cost=0.00.. 2361.45 rows=53180566 width=36)
Rows Rows out: Avg 53180356.8 x 12 workers. The Max 53186352 Rows (seg4) with 0.098 ms to the first row, 13416 ms to the end, the start offset by 1.495 ms.
Slice the statistics:
(slice0) Executor memory: 322 k bytes.
(slice1) Executor memory: 162 k bytes avg x 12 workers, 179 k bytes Max (seg4).
The Statement statistics:
The Memory, informs: 128000 k bytes
Settings: effective_cache_size=64000 MB. Enable_indexscan=on; Enable_seqscan=off
The Optimizer status: PQO version 2.53.8
The Total runtime: 300824.082 ms
The EXPLAIN ANALYZE the 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 the end, the start offset by 0.513 ms.
-> A Table Scan on aaa (cost=0.00.. 431.00 rows=1 width=28)
Rows out: 1 Rows (seg4) with 13 ms to the end, the start offset by 38 Ms.
Slice the statistics:
(slice0) Executor memory: 322 k bytes.
(slice1) Executor memory: 155 k bytes avg x 12 workers, 155 k bytes Max (seg0).
The Statement statistics:
The Memory, informs: 128000 k bytes
Settings: effective_cache_size=64000 MB. Enable_indexscan=on; Enable_seqscan=off
The Optimizer status: PQO version 2.53.8
The Total runtime: 56.018 ms
The EXPLAIN ANALYZE the 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 the first row, 0.985 ms to the end, the 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 the end, the start offset by 0.527 ms.
-> Nested Loop (cost=0.00.. 431.50 rows=1 width=64)
Join the Filter: true
Rows out: 1 Rows (seg4) with 0.355 ms to the first row, 0.364 ms to the end, the start offset by 1.043 ms.
-> A Table Scan on aaa (cost=0.00.. 431.00 rows=1 width=28)
Rows out: 1 Rows (seg4) with 0.128 ms to the first row, 0.131 ms to the end, the start offset by 1.049 ms.
-> The Index Scan using wifiinfo3_pkey on wifiinfo3 (cost=0.00.. 0.50 rows=1 width=36)
The Index Cond: wifiinfo3. MAC=aaa. F_id
Rows out: 1 Rows (seg4) with 0.201 ms to the first row, 0.204 ms to the end, the start offset by 1.195 ms.
Slice the statistics:
(slice0) Executor memory: 386 k bytes.
(slice1) Executor memory: 169 k bytes avg x 12 workers, 193 k bytes Max (seg4).
The Statement statistics:
The Memory, informs: 128000 k bytes
Settings: effective_cache_size=64000 MB. Enable_indexscan=on; Enable_seqscan=off
The Optimizer status: PQO version 2.53.8
The Total runtime: 1.742 ms
The EXPLAIN ANALYZE the 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 the end, the 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 the end, the start offset by 13 Ms.
-> Nested Loop Left Join (cost=0.00.. 102400000086410752.00 rows=1 width=64)
Join the Filter: aaa. F_id=wifiinfo3. MAC
Rows out: 1 Rows (seg4) with 797049 ms to the first row, 947850 ms to the end, the start offset by 16 ms.
-> A Table Scan on aaa (cost=0.00.. 431.00 rows=1 width=28)
Rows out: 1 Rows (seg4) with 70 ms to the end, the start offset by 795741 ms.
-> Materialize (cost=0.00.. 100000000028896.42 rows=638166784 width=36)
Rows Rows out: Avg 53180357.8 x 12 workers. The Max 638164282 Rows (seg4) with 795725 ms to the first row, 871834 ms to the end of 2 scans, start offset by 795811 ms.
Work_mem 2:127584 k bytes avg, 127584 k bytes Max (seg0). Workfile: (12 spilling)
Work_mem wanted: 32466211 k bytes avg, 32466240 k bytes Max (seg4) to lessen workfile I/O affecting 12 workers.
-> Broadcast Motion also 12:12 (slice1; Segments: 12) (cost=0.00.. 100000000005922.42 rows=638166784 width=36)
Rows Rows out: Avg 638164281.0 x 12 workers at destination. The Max 638164281 Rows (seg0) with 0.144 ms to the first row, 590632 ms to the end, the start offset by 17 ms.
-> A Table Scan on wifiinfo3 (cost=0.00.. 2361.45 rows=53180566 width=36)
Rows Rows out: Avg 53180356.8 x 12 workers. The Max 53186352 Rows (seg4) with 0.472 ms to the first row, 15394 ms to the end, the start offset by 15 ms.
Slice the statistics:
(slice0) Executor memory: 386 k bytes.
(slice1) Executor memory: 368 k bytes avg x 12 workers, 442 k bytes Max (seg11).
(slice2) * Executor memory: 127963 k bytes avg x 12 workers, 127963 k bytes Max (seg0). Work_mem: 127584 k bytes Max, 32466240 k bytes wanted.
The Statement statistics:
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
