explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E2mQ

Settings
# exclusive inclusive rows x rows loops node
1. 26,038.284 264,122.324 ↑ 11.6 2,364,622 1

Hash Left Join (cost=612,368.63..11,935,325.18 rows=27,464,836 width=706) (actual time=9,040.992..264,122.324 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: 26072710
2. 38,056.425 238,067.744 ↓ 1.0 28,437,332 1

Hash Left Join (cost=611,503.05..9,048,488.69 rows=27,602,850 width=654) (actual time=9,023.049..238,067.744 rows=28,437,332 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. 17,069.516 199,886.993 ↓ 1.0 28,437,332 1

Hash Join (cost=590,387.01..8,932,485.05 rows=27,602,850 width=631) (actual time=8,898.684..199,886.993 rows=28,437,332 loops=1)

  • Hash Cond: ((adr.oktmo_code)::text = (mun.row_code)::text)
4. 61,769.060 181,704.156 ↓ 1.0 28,507,094 1

Hash Join (cost=567,194.79..8,835,377.40 rows=28,157,602 width=597) (actual time=7,784.960..181,704.156 rows=28,507,094 loops=1)

  • Hash Cond: (((adr.dim_admterritory_root_guid)::character varying)::text = (adm.root_guid)::text)
5. 37,234.197 117,278.556 ↑ 1.0 28,507,094 1

Hash Left Join (cost=328,603.75..6,007,230.18 rows=28,507,094 width=321) (actual time=5,128.194..117,278.556 rows=28,507,094 loops=1)

  • Hash Cond: ((adr.fias_house_code = charges.fias_house_code) AND ((serv.nsi_3_code)::text = (charges.nsi_3_code)::text))
  • Join Filter: ((serv.nsi_2_code)::text = '0'::text)
  • Rows Removed by Join Filter: 240004
6. 33,773.673 78,119.278 ↑ 1.0 28,507,094 1

Hash Left Join (cost=206,151.10..3,643,271.22 rows=28,507,094 width=246) (actual time=3,202.033..78,119.278 rows=28,507,094 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
7. 31,442.050 42,712.472 ↑ 1.0 28,507,094 1

Hash Left Join (cost=103,206.20..1,810,416.98 rows=28,507,094 width=176) (actual time=1,568.033..42,712.472 rows=28,507,094 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
8. 6,208.819 9,716.977 ↑ 1.0 28,507,094 1

Nested Loop (cost=0.00..422,124.35 rows=28,507,094 width=110) (actual time=0.038..9,716.977 rows=28,507,094 loops=1)

9. 916.604 916.604 ↑ 1.0 2,591,554 1

Seq Scan on tmp_addresses adr (cost=0.00..65,784.54 rows=2,591,554 width=91) (actual time=0.020..916.604 rows=2,591,554 loops=1)

10. 2,591.533 2,591.554 ↑ 1.0 11 2,591,554

Materialize (cost=0.00..1.17 rows=11 width=19) (actual time=0.000..0.001 rows=11 loops=2,591,554)

11. 0.021 0.021 ↑ 1.0 11 1

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

12. 866.801 1,553.445 ↑ 1.0 2,093,888 1

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

  • Buckets: 524288 Batches: 8 Memory Usage: 30469kB
13. 686.644 686.644 ↑ 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.017..686.644 rows=2,093,888 loops=1)

14. 897.610 1,633.133 ↑ 1.0 2,087,596 1

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

  • Buckets: 524288 Batches: 8 Memory Usage: 30809kB
15. 735.523 735.523 ↑ 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.037..735.523 rows=2,087,596 loops=1)

16. 1,074.044 1,925.081 ↑ 1.0 2,395,306 1

Hash (cost=56,113.06..56,113.06 rows=2,395,306 width=77) (actual time=1,925.081..1,925.081 rows=2,395,306 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 20384kB
17. 851.037 851.037 ↑ 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.035..851.037 rows=2,395,306 loops=1)

18. 1,129.505 2,656.540 ↓ 1.0 1,314,845 1

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

  • Buckets: 131072 Batches: 16 Memory Usage: 26552kB
19. 1,527.035 1,527.035 ↓ 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.014..1,527.035 rows=1,314,845 loops=1)

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

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 69.509 124.387 ↑ 1.0 94 1

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

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

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

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2532kB
26. 8.147 8.147 ↑ 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.055..8.147 rows=21,112 loops=1)

Planning time : 62.884 ms
Execution time : 264,357.783 ms