explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pu1A

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,464.164 12,842.461 ↓ 1.4 4,612,030 1

Merge Join (cost=63,631.10..205,024.67 rows=3,319,966 width=69) (actual time=1,395.660..12,842.461 rows=4,612,030 loops=1)

  • Merge Cond: (minfo.mdu_info_id = mser.mdu_id)
2. 613.630 3,152.247 ↓ 1.3 219,530 1

Nested Loop (cost=1,289.56..167,905.53 rows=164,134 width=57) (actual time=16.009..3,152.247 rows=219,530 loops=1)

3. 832.052 1,660.369 ↓ 1.3 219,562 1

Merge Join (cost=1,289.13..68,537.51 rows=164,134 width=55) (actual time=15.995..1,660.369 rows=219,562 loops=1)

  • Merge Cond: (ciadd.mdu_id = minfo.mdu_info_id)
4. 697.921 697.921 ↑ 1.4 1,115,152 1

Index Scan using f_mdu_ci_address_link_mdu_id_idx on f_mdu_ci_address_link ciadd (cost=0.43..60,925.73 rows=1,544,895 width=8) (actual time=0.008..697.921 rows=1,115,152 loops=1)

5. 117.437 130.396 ↓ 78.8 219,608 1

Sort (cost=1,288.70..1,295.67 rows=2,786 width=47) (actual time=15.052..130.396 rows=219,608 loops=1)

  • Sort Key: minfo.mdu_info_id
  • Sort Method: quicksort Memory: 400kB
6. 3.096 12.959 ↑ 1.1 2,568 1

Hash Join (cost=2.80..1,129.29 rows=2,786 width=47) (actual time=0.090..12.959 rows=2,568 loops=1)

  • Hash Cond: (minfo.corp = geo.corp)
7. 9.788 9.788 ↑ 1.0 2,568 1

Seq Scan on f_mdu_info minfo (cost=0.00..1,085.79 rows=2,568 width=41) (actual time=0.008..9.788 rows=2,568 loops=1)

  • Filter: (contract_type_id = 15)
  • Rows Removed by Filter: 23655
8. 0.027 0.075 ↑ 1.1 36 1

Hash (cost=2.33..2.33 rows=38 width=10) (actual time=0.075..0.075 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.048 0.048 ↑ 1.1 36 1

Seq Scan on stg_kom_d_geog_corp geo (cost=0.00..2.33 rows=38 width=10) (actual time=0.009..0.048 rows=36 loops=1)

  • Filter: (((metro_area_desc)::text = 'NYMA'::text) AND (now() >= begin_date) AND (now() <= end_date))
  • Rows Removed by Filter: 23
10. 878.248 878.248 ↑ 1.0 1 219,562

Index Scan using f_ccip_addresses_pkey on f_ccip_addresses cci_add (cost=0.43..0.60 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=219,562)

  • Index Cond: (id_address = ciadd.address_id)
11. 3,856.956 4,226.050 ↓ 9.6 5,100,611 1

Sort (cost=62,325.25..63,651.30 rows=530,417 width=20) (actual time=1,379.504..4,226.050 rows=5,100,611 loops=1)

  • Sort Key: mser.mdu_id
  • Sort Method: external sort Disk: 17640kB
12. 369.094 369.094 ↑ 1.0 530,417 1

Seq Scan on f_mdu_services mser (cost=0.00..11,891.17 rows=530,417 width=20) (actual time=0.004..369.094 rows=530,417 loops=1)

Planning time : 0.775 ms