Search Postgresql Archives

Weird explain output

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux