explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H4v1

Settings
# exclusive inclusive rows x rows loops node
1. 90.041 978,115.893 ↓ 3.9 54,420 1

Nested Loop (cost=64,970,535.35..122,984,414.31 rows=14,010 width=220) (actual time=831,545.105..978,115.893 rows=54,420 loops=1)

2. 131,587.602 977,917.012 ↓ 3.8 54,420 1

GroupAggregate (cost=64,970,534.93..122,960,269.24 rows=14,184 width=40) (actual time=831,545.025..977,917.012 rows=54,420 loops=1)

  • Group Key: ranges.admter_region_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Key: ranges.admter_federal_district_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Group Key: ranges.admter_federal_district_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Key: ranges.admter_rf_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Group Key: ranges.admter_rf_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Filter: (COALESCE(ranges.admter_region_id, ranges.admter_federal_district_id, ranges.admter_rf_id) IS NOT NULL)
3. 35,860.662 846,329.410 ↑ 7.2 20,307,441 1

Sort (cost=64,970,534.93..65,338,360.60 rows=147,130,271 width=44) (actual time=831,528.559..846,329.410 rows=20,307,441 loops=1)

  • Sort Key: ranges.admter_region_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Method: external merge Disk: 1002728kB
4. 576,744.524 810,468.748 ↑ 7.2 20,307,441 1

Merge Left Join (cost=3,372,023.10..39,837,908.66 rows=147,130,271 width=44) (actual time=31,116.673..810,468.748 rows=20,307,441 loops=1)

  • Merge Cond: (((ranges.house_type)::text = (charges.house_type)::text) AND ((ranges.nsi_3_code)::text = (charges.nsi_3_code)::text) AND ((ranges.admterritory_guid)::text = (charges.admterritory_guid)::text))
  • Join Filter: ((charges.service_total >= ranges.range_begin) AND (charges.service_total < COALESCE(ranges.range_end, (charges.service_total + '1'::numeric))))
  • Rows Removed by Join Filter: 1195766334
5. 166.178 172.305 ↑ 1.0 47,520 1

Sort (cost=4,740.61..4,859.41 rows=47,520 width=65) (actual time=152.838..172.305 rows=47,520 loops=1)

  • Sort Key: ranges.house_type, ranges.nsi_3_code, ranges.admterritory_guid COLLATE "C
  • Sort Method: quicksort Memory: 8219kB
6. 6.127 6.127 ↑ 1.0 47,520 1

Seq Scan on tmp_regions_ranges ranges (cost=0.00..1,049.20 rows=47,520 width=65) (actual time=0.018..6.127 rows=47,520 loops=1)

7. 187,586.840 233,551.919 ↓ 60.0 1,216,033,501 1

Materialize (cost=3,367,282.49..3,468,619.28 rows=20,267,358 width=63) (actual time=30,963.795..233,551.919 rows=1,216,033,501 loops=1)

8. 40,493.632 45,965.079 ↑ 1.0 20,267,226 1

Sort (cost=3,367,282.49..3,417,950.89 rows=20,267,358 width=63) (actual time=30,963.781..45,965.079 rows=20,267,226 loops=1)

  • Sort Key: charges.house_type, charges.nsi_3_code, charges.admterritory_guid COLLATE "C
  • Sort Method: external merge Disk: 1466488kB
9. 5,471.447 5,471.447 ↑ 1.0 20,267,226 1

Seq Scan on tmp_service_charges_by_accommodation charges (cost=0.00..472,137.58 rows=20,267,358 width=63) (actual time=0.103..5,471.447 rows=20,267,226 loops=1)

10. 108.840 108.840 ↑ 1.0 1 54,420

Index Scan using pk_dim_admterritory_id on dim_admterritory adm_ter (cost=0.43..1.66 rows=1 width=161) (actual time=0.002..0.002 rows=1 loops=54,420)

  • Index Cond: (id = (COALESCE(ranges.admter_region_id, ranges.admter_federal_district_id, ranges.admter_rf_id)))
  • Filter: entity_is_actual
Planning time : 0.883 ms
Execution time : 979,356.669 ms