I have a long SQL with many join, the explain analyse output like
-> Hash Left Join (cost=197749705.15..5320438982425.18
rows=5245599776015 width=7542) (actual time=674.634..2645.293 rows=4568
loops=1)
Hash Cond: (p.id = ps.partner_id)
-> Merge Join (cost=197747693.55..78881918575.29
rows=5245599776015 width=7486) (actual time=636.814..653.137 rows=4568
loops=1)
Merge Cond: (a3.team_id = t.id)
-> Sort (cost=3509574.57..3554578.91 rows=18001735
width=112) (actual time=518.318..519.221 rows=4569 loops=1)
Sort Key: a3.team_id
Sort Method: quicksort Memory: 549kB
..................... sniped
-> Materialize (cost=194238118.98..194529512.87
rows=58278779 width=7390) (actual time=118.488..122.408 rows=4569 loops=1)
-> Sort (cost=194238118.98..194383815.92
rows=58278779 width=7390) (actual time=118.486..119.723 rows=3687 loops=1)
Sort Key: t.id
Sort Method: quicksort Memory: 6951kB
............................. sniped
-> Hash (cost=1917.17..1917.17 rows=7555 width=68) (actual
time=37.578..37.578 rows=7555 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 958kB
............................ sniped
We can see the 'Hash Left Join node' spend 2645.293 ms, so I change the sql
and remove the left join node, and expect the SQL will end with 653.137 ms.
But the explain analyse output like
-> Merge Join (cost=197548498.05..314933709300.46 rows=5245599776015
width=7478) (actual time=775.495..2744.759 rows=4568 loops=1)
Merge Cond: (a3.team_id = t.id)
-> Sort (cost=3509574.57..3554578.91 rows=18001735 width=112)
(actual time=540.636..541.392 rows=4569 loops=1)
Sort Key: a3.team_id
Sort Method: quicksort Memory: 549kB
.......................... sniped
-> Materialize (cost=194038923.48..194330317.37 rows=58278779
width=7382) (actual time=234.632..238.065 rows=4569 loops=1)
-> Sort (cost=194038923.48..194184620.42 rows=58278779
width=7382) (actual time=234.628..235.744 rows=3687 loops=1)
Sort Key: t.id
Sort Method: quicksort Memory: 6899kB
......................... sniped
We can see the 'Merge Join' spend 2744.759 ms. The sniped output under the
'Merge Join' node in the two explain output are similar, Why does this
happen?
