explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OprJ

Settings
# exclusive inclusive rows x rows loops node
1. 34.505 646,102.856 ↑ 1.1 16,534 1

Nested Loop (cost=3,656,398.66..3,693,728.40 rows=18,477 width=272) (actual time=481,204.464..646,102.856 rows=16,534 loops=1)

2. 142,831.294 646,018.749 ↑ 1.1 16,534 1

GroupAggregate (cost=3,656,398.23..3,663,302.48 rows=18,706 width=134) (actual time=481,204.400..646,018.749 rows=16,534 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)
  • Rows Removed by Filter: 243
3. 56,638.864 503,187.455 ↓ 631.6 20,930,138 1

Sort (cost=3,656,398.23..3,656,481.08 rows=33,140 width=138) (actual time=481,183.689..503,187.455 rows=20,930,138 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: 1051024kB
4. 9,450.332 446,548.591 ↓ 631.6 20,930,138 1

Hash Left Join (cost=58,684.88..3,653,910.03 rows=33,140 width=138) (actual time=1,381.903..446,548.591 rows=20,930,138 loops=1)

  • Hash Cond: ((charges.admterritory_guid)::text = (adm_in_row.admter_root_guid)::text)
5. 431,593.356 435,717.795 ↓ 631.6 20,930,138 1

Hash Right Join (cost=24.25..3,580,398.73 rows=33,140 width=163) (actual time=0.509..435,717.795 rows=20,930,138 loops=1)

  • Hash Cond: (((charges.house_type)::text = (ranges.house_type)::text) AND ((charges.nsi_3_code)::text = (ranges.nsi_3_code)::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: 1234873363
6. 4,123.983 4,123.983 ↑ 1.0 20,930,057 1

Seq Scan on tmp_service_charges_by_accommodation charges (cost=0.00..487,925.68 rows=20,930,368 width=63) (actual time=0.022..4,123.983 rows=20,930,057 loops=1)

7. 0.206 0.456 ↓ 1.4 780 1

Hash (cost=15.70..15.70 rows=570 width=110) (actual time=0.456..0.456 rows=780 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
8. 0.250 0.250 ↓ 1.4 780 1

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

9. 651.418 1,380.464 ↑ 1.0 1,314,845 1

Hash (cost=29,383.17..29,383.17 rows=1,314,917 width=49) (actual time=1,380.464..1,380.464 rows=1,314,845 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 17104kB
10. 729.046 729.046 ↑ 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=37.363..729.046 rows=1,314,845 loops=1)

11. 49.602 49.602 ↑ 1.0 1 16,534

Index Scan using pk_dim_admterritory_id on dim_admterritory adm_ter (cost=0.43..1.58 rows=1 width=161) (actual time=0.003..0.003 rows=1 loops=16,534)

  • 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