explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 51Tf

Settings
# exclusive inclusive rows x rows loops node
1. 83.231 699,585.649 ↓ 1,649.1 54,420 1

Nested Loop (cost=734,568.08..734,651.11 rows=33 width=272) (actual time=553,192.203..699,585.649 rows=54,420 loops=1)

2. 130,907.813 699,393.578 ↓ 1,649.1 54,420 1

GroupAggregate (cost=734,567.65..734,568.94 rows=33 width=134) (actual time=553,192.131..699,393.578 rows=54,420 loops=1)

  • Group Key: adm_in_row.admter_region_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Key: adm_in_row.admter_federal_district_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Group Key: adm_in_row.admter_federal_district_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Key: adm_in_row.admter_rf_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Group Key: adm_in_row.admter_rf_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Filter: (COALESCE(adm_in_row.admter_region_id, adm_in_row.admter_federal_district_id, adm_in_row.admter_rf_id) IS NOT NULL)
3. 39,510.284 568,485.765 ↓ 1,846,131.0 20,307,441 1

Sort (cost=734,567.65..734,567.68 rows=11 width=138) (actual time=553,177.915..568,485.765 rows=20,307,441 loops=1)

  • Sort Key: adm_in_row.admter_region_id, ranges.house_type, ranges.nsi_3_code, ranges.range_begin, ranges.range_end
  • Sort Method: external merge Disk: 1002744kB
4. 521,941.697 528,975.481 ↓ 1,846,131.0 20,307,441 1

Hash Right Join (cost=34,420.72..734,567.46 rows=11 width=138) (actual time=718.438..528,975.481 rows=20,307,441 loops=1)

  • Hash Cond: (((charges.house_type)::text = (ranges.house_type)::text) AND ((charges.nsi_3_code)::text = (ranges.nsi_3_code)::text) AND ((charges.admterritory_guid)::text = (regions.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. 6,357.667 6,357.667 ↑ 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.029..6,357.667 rows=20,267,226 loops=1)

6. 19.527 676.117 ↓ 4,320.0 47,520 1

Hash (cost=34,420.52..34,420.52 rows=11 width=159) (actual time=676.117..676.117 rows=47,520 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5105kB
7. 360.782 656.590 ↓ 4,320.0 47,520 1

Hash Join (cost=106.31..34,420.52 rows=11 width=159) (actual time=111.401..656.590 rows=47,520 loops=1)

  • Hash Cond: ((adm_in_row.admter_root_guid)::text = (regions.admterritory_guid)::text)
8. 184.451 184.451 ↑ 1.0 1,314,845 1

Seq Scan on dim_admterritory_in_row adm_in_row (cost=0.00..29,383.17 rows=1,314,917 width=49) (actual time=0.014..184.451 rows=1,314,845 loops=1)

9. 14.971 111.357 ↓ 4,320.0 47,520 1

Hash (cost=106.17..106.17 rows=11 width=147) (actual time=111.356..111.357 rows=47,520 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4489kB
10. 16.257 96.386 ↓ 4,320.0 47,520 1

Merge Join (cost=95.84..106.17 rows=11 width=147) (actual time=76.876..96.386 rows=47,520 loops=1)

  • Merge Cond: (((ranges.house_type)::text = (regions.house_type)::text) AND ((ranges.nsi_3_code)::text = (regions.nsi_3_code)::text))
11. 6.861 7.026 ↓ 1.4 780 1

Sort (cost=41.79..43.22 rows=570 width=110) (actual time=6.957..7.026 rows=780 loops=1)

  • Sort Key: ranges.house_type, ranges.nsi_3_code
  • Sort Method: quicksort Memory: 76kB
12. 0.165 0.165 ↓ 1.4 780 1

Seq Scan on tmp_service_charges_ranges ranges (cost=0.00..15.70 rows=570 width=110) (actual time=0.015..0.165 rows=780 loops=1)

13. 72.890 73.103 ↓ 59.9 47,461 1

Sort (cost=54.05..56.03 rows=792 width=41) (actual time=69.911..73.103 rows=47,461 loops=1)

  • Sort Key: regions.house_type, regions.nsi_3_code
  • Sort Method: quicksort Memory: 86kB
14. 0.213 0.213 ↑ 1.0 792 1

Seq Scan on tmp_adm_regions_with_charges regions (cost=0.00..15.92 rows=792 width=41) (actual time=0.026..0.213 rows=792 loops=1)

15. 108.840 108.840 ↑ 1.0 1 54,420

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

  • Index Cond: (id = (COALESCE(adm_in_row.admter_region_id, adm_in_row.admter_federal_district_id, adm_in_row.admter_rf_id)))
  • Filter: entity_is_actual
Planning time : 1.188 ms
Execution time : 700,101.722 ms