explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3gu5

Settings
# exclusive inclusive rows x rows loops node
1. 80,259.037 1,611,500.261 ↑ 79.6 2,364,622 1

Hash Left Join (cost=18,369,063.97..75,471,017.11 rows=188,117,713 width=685) (actual time=406,635.442..1,611,500.261 rows=2,364,622 loops=1)

  • Hash Cond: ((adr.fias_house_code = cost_two.fias_house_code) AND ((serv.nsi_3_code)::text = (cost_two.nsi_3_code)::text) AND ((serv.nsi_2_code)::text = (cost_two.nsi_2_code)::text))
  • Filter: (COALESCE(charges.fias_house_code, cons.fias_house_code, cost_one.fias_house_code, cost_two.fias_house_code) IS NOT NULL)
  • Rows Removed by Filter: 192371139
2. 260,499.064 1,531,224.047 ↓ 1.0 194,735,761 1

Hash Left Join (cost=18,368,198.39..55,703,519.02 rows=189,063,028 width=633) (actual time=406,618.153..1,531,224.047 rows=194,735,761 loops=1)

  • Hash Cond: (CASE WHEN ("substring"((adr.oktmo_code)::text, 1, 3) = ANY ('{718,719,118}'::text[])) THEN ("substring"((adr.oktmo_code)::text, 1, 3) || '00000'::text) ELSE ("substring"((adr.oktmo_code)::text, 1, 2) || '000000'::text) END = (mun_region (...)
3. 119,615.752 1,270,644.077 ↓ 1.0 194,735,761 1

Hash Join (cost=18,347,082.35..55,032,479.56 rows=189,063,028 width=610) (actual time=406,537.210..1,270,644.077 rows=194,735,761 loops=1)

  • Hash Cond: ((adr.oktmo_code)::text = (mun.row_code)::text)
4. 410,952.837 1,149,842.667 ↓ 1.0 195,256,325 1

Hash Join (cost=18,323,890.13..54,503,010.87 rows=192,862,714 width=576) (actual time=405,351.146..1,149,842.667 rows=195,256,325 loops=1)

  • Hash Cond: (((adr.dim_admterritory_root_guid)::character varying)::text = (adm.root_guid)::text)
5. 212,778.048 735,853.674 ↑ 1.0 195,256,325 1

Hash Left Join (cost=18,085,299.09..38,009,509.33 rows=195,256,534 width=300) (actual time=402,028.848..735,853.674 rows=195,256,325 loops=1)

  • Hash Cond: ((adr.fias_house_code = cost_one.fias_house_code) AND ((serv.nsi_3_code)::text = (cost_one.nsi_3_code)::text))
  • Join Filter: ((serv.nsi_2_code)::text = '0'::text)
  • Rows Removed by Join Filter: 179224
6. 120,123.613 521,454.282 ↑ 1.0 195,256,325 1

Hash Right Join (cost=17,982,354.19..23,697,626.18 rows=195,256,534 width=230) (actual time=400,406.626..521,454.282 rows=195,256,325 loops=1)

  • Hash Cond: ((charges.fias_house_code = adr.fias_house_code) AND ((charges.nsi_3_code)::text = (serv.nsi_3_code)::text))
  • Join Filter: ((serv.nsi_2_code)::text = '0'::text)
  • Rows Removed by Join Filter: 240004
7. 1,093.872 1,093.872 ↑ 1.0 2,395,306 1

Seq Scan on tmp_service_charges charges (cost=0.00..56,113.06 rows=2,395,306 width=77) (actual time=0.086..1,093.872 rows=2,395,306 loops=1)

8. 132,881.094 400,236.797 ↑ 1.0 195,256,325 1

Hash (cost=10,667,861.18..10,667,861.18 rows=195,256,534 width=155) (actual time=400,236.797..400,236.797 rows=195,256,325 loops=1)

  • Buckets: 262144 Batches: 2048 Memory Usage: 14442kB
9. 201,235.485 267,355.703 ↑ 1.0 195,256,325 1

Hash Left Join (cost=103,206.20..10,667,861.18 rows=195,256,534 width=155) (actual time=1,701.983..267,355.703 rows=195,256,325 loops=1)

  • Hash Cond: ((adr.fias_house_code = cons.fias_house_code) AND ((serv.nsi_3_code)::text = (cons.nsi_3_code)::text))
  • Join Filter: ((serv.nsi_2_code)::text = '0'::text)
  • Rows Removed by Join Filter: 216844
10. 41,306.059 64,419.208 ↑ 1.0 195,256,325 1

Nested Loop (cost=0.00..2,838,190.75 rows=195,256,534 width=89) (actual time=0.038..64,419.208 rows=195,256,325 loops=1)

11. 5,362.574 5,362.574 ↑ 1.0 17,750,575 1

Seq Scan on tmp_addresses adr (cost=0.00..397,482.94 rows=17,750,594 width=70) (actual time=0.023..5,362.574 rows=17,750,575 loops=1)

12. 17,750.556 17,750.575 ↑ 1.0 11 17,750,575

Materialize (cost=0.00..1.17 rows=11 width=19) (actual time=0.000..0.001 rows=11 loops=17,750,575)

13. 0.019 0.019 ↑ 1.0 11 1

Seq Scan on serv_res serv (cost=0.00..1.11 rows=11 width=19) (actual time=0.008..0.019 rows=11 loops=1)

14. 1,005.982 1,701.010 ↑ 1.0 2,093,888 1

Hash (cost=47,259.88..47,259.88 rows=2,093,888 width=68) (actual time=1,701.010..1,701.010 rows=2,093,888 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 30469kB
15. 695.028 695.028 ↑ 1.0 2,093,888 1

Seq Scan on tmp_consumption cons (cost=0.00..47,259.88 rows=2,093,888 width=68) (actual time=0.020..695.028 rows=2,093,888 loops=1)

16. 894.391 1,621.344 ↑ 1.0 2,087,596 1

Hash (cost=47,165.96..47,165.96 rows=2,087,596 width=72) (actual time=1,621.344..1,621.344 rows=2,087,596 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 30809kB
17. 726.953 726.953 ↑ 1.0 2,087,596 1

Seq Scan on tmp_service_cost_one cost_one (cost=0.00..47,165.96 rows=2,087,596 width=72) (actual time=0.111..726.953 rows=2,087,596 loops=1)

18. 1,462.116 3,036.156 ↓ 1.0 1,314,845 1

Hash (cost=164,391.44..164,391.44 rows=1,314,528 width=329) (actual time=3,036.156..3,036.156 rows=1,314,845 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 26552kB
19. 1,574.040 1,574.040 ↓ 1.0 1,314,845 1

Seq Scan on dim_admterritory adm (cost=0.00..164,391.44 rows=1,314,528 width=329) (actual time=0.013..1,574.040 rows=1,314,845 loops=1)

  • Filter: entity_is_actual
  • Rows Removed by Filter: 15964
20. 72.523 1,185.658 ↓ 1.0 177,537 1

Hash (cost=20,973.03..20,973.03 rows=177,535 width=45) (actual time=1,185.657..1,185.658 rows=177,537 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 15131kB
21. 1,113.135 1,113.135 ↓ 1.0 177,537 1

Seq Scan on dim_munterritory mun (cost=0.00..20,973.03 rows=177,535 width=45) (actual time=0.016..1,113.135 rows=177,537 loops=1)

  • Filter: entity_is_actual
  • Rows Removed by Filter: 3566
22. 0.000 80.906 ↑ 1.0 94 1

Hash (cost=21,114.84..21,114.84 rows=96 width=45) (actual time=80.906..80.906 rows=94 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 15.088 80.986 ↑ 1.0 94 1

Gather (cost=1,000.00..21,114.84 rows=96 width=45) (actual time=0.591..80.986 rows=94 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
24. 65.898 65.898 ↑ 1.3 31 3

Parallel Seq Scan on dim_munterritory mun_regions (cost=0.00..20,105.24 rows=40 width=45) (actual time=39.191..65.898 rows=31 loops=3)

  • Filter: (entity_is_actual AND (level <= 2))
  • Rows Removed by Filter: 60336
25. 8.702 17.177 ↑ 1.0 21,112 1

Hash (cost=496.12..496.12 rows=21,112 width=78) (actual time=17.176..17.177 rows=21,112 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2532kB
26. 8.475 8.475 ↑ 1.0 21,112 1

Seq Scan on tmp_service_cost_two cost_two (cost=0.00..496.12 rows=21,112 width=78) (actual time=0.081..8.475 rows=21,112 loops=1)

Planning time : 50.655 ms
Execution time : 1,611,731.062 ms