Left Join(二)
-+----+------
1 | 1a | |
2 | 2a | |
3 | 3a | |
4 | 4a | |
5 | 5a | |
(5 rows)
bigdatagp=# explain analyze select * from tgt1 a left join tgt2 b on a.id=b.id where a.id>=3 order by a.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 64:1 (slice1; segments: 64) (cost=7.18..7.19 rows=1 width=14)
Merge Key: "?column5?"
Rows out: 3 rows at destination with 21 ms to end, start offset by 559 ms.
-> Sort (cost=7.18..7.19 rows=1 width=14)
Sort Key: a.id
Rows out: Avg 1.0 rows x 3 workers. Max 1 rows (seg52) with 5.452 ms to first row, 5.454 ms to end, start offset by 564 ms.
Executor memory: 63K bytes avg, 74K bytes max (seg2).
Work_mem used: 63K bytes avg, 74K bytes max (seg2). Workfile: (0 spilling, 0 reused)
-> Hash Left Join (cost=2.04..7.15 rows=1 width=14)
Hash Cond: a.id = b.id
Rows out: Avg 1.0 rows x 3 workers. Max 1 rows (seg52) with 4.190 ms to first row, 4.598 ms to end, start offset by 565 ms.
-> Seq Scan on tgt1 a (cost=0.00..5.06 rows=1 width=7)
Filter: id >= 3
Rows out: Avg 1.0 rows x 3 workers. Max 1 rows (seg52) with 0.156 ms to first row, 0.158 ms to end, start offset by 565 ms.
-> Hash (cost=2.02..2.02 rows=1 width=7)
Rows in: (No row requested) 0 rows (seg0) with 0 ms to end.
-> Seq Scan on tgt2 b (cost=0.00..2.02 rows=1 width=7)
Rows out: (No row requested) 0 rows (seg0) with 0 ms to end.
Slice statistics:
(slice0) Executor memory: 332K bytes.
(slice1) Executor memory: 446K bytes avg x 64 workers, 4329K bytes max (seg52). Work_mem: 74K bytes max.
Statement statistics:
Memory used: 128000K bytes
Total runtime: 580.630 ms
(24 rows)
bigdatagp=# explain analyze select * from tgt1 a left join tgt2 b on a.id=b.id and a.id>
=3 order by a.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 64:1 (slice1; segments: 64) (cost=7.23..7.24 rows=1 width=14)
Merge Key: "?column5?"
Rows out: 5 rows at destination with 24 ms to end, start offset by 701 ms.
-> Sort (cost=7.23..7.24 rows=1 width=14)
Sort Key: a.id
Rows out: Avg 1.0 rows x 5 workers. Max 1 rows (seg42) with 6.292 ms to first row, 6.294 ms to end, start offset by 715 ms.
Executor memory: 70K bytes avg, 74K bytes max (seg0).
Work_mem used: 70K bytes avg, 74K bytes max (seg0). Workfile: (0 spilling, 0 reused)
-> Hash Left Join (cost=2.04..7.17 rows=1 width=14)
Hash Cond: a.id = b.id
Join Filter: a.id >= 3
Rows out: Avg 1.0 rows x 5 workers. Max 1 rows (seg42) with 4.422 ms to first row, 5.055 ms to end, start offset by 717 ms.
Executor memory: 1K bytes avg, 1K bytes max (seg42).
Work_mem used: 1K bytes avg, 1K bytes max (seg42). Workfile: (0 spilling, 0 reused)
(seg42) Hash chain length 1.0 avg, 1 max, using 1 of 262151 buckets.
-> Seq Scan on tgt1 a (cost=0.00..5.05 rows=1 width=7)
Rows out: Avg 1.0 rows x 5 workers. Max 1 rows (seg42) with 0.179 ms to first row, 0.180 ms to end, start offset by 717 ms.
-> Hash (cost=2.02..2.02 rows=1 width=7)
Rows in: Avg 1.0 rows x 2 workers. Max 1 rows (seg42) with 0.194 ms to end, start offset by 721 ms.
-> Seq Scan on tgt2 b (cost=0.00..2.02 rows=1 width=7)
Rows out: Avg 1.0 rows x 2 workers. Max 1 rows (seg42) with