explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9KZR

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 8,302.582 ↓ 39.0 39 1

Unique (cost=158,903.56..158,903.57 rows=1 width=36) (actual time=8,302.569..8,302.582 rows=39 loops=1)

2. 0.424 8,302.570 ↓ 39.0 39 1

Sort (cost=158,903.56..158,903.56 rows=1 width=36) (actual time=8,302.568..8,302.570 rows=39 loops=1)

  • Sort Key: (replace(concat(gaa.area_name, ', ', gsa.street_name, ', ', gha.house_number, '/', gha.building_number, ', подъезд ', pa.porche_num, ' (', bfa.box_feature_name, ')'), '/,'::text, ','::text)), hba.box_id
  • Sort Method: quicksort Memory: 30kB
3. 0.661 8,302.146 ↓ 39.0 39 1

Nested Loop (cost=15,740.67..158,903.55 rows=1 width=36) (actual time=77.374..8,302.146 rows=39 loops=1)

4. 0.194 8,300.822 ↓ 39.0 39 1

Nested Loop (cost=15,740.24..158,900.02 rows=1 width=159) (actual time=77.295..8,300.822 rows=39 loops=1)

5. 0.000 8,299.770 ↓ 39.0 39 1

Nested Loop (cost=15,739.82..158,891.56 rows=1 width=83) (actual time=77.280..8,299.770 rows=39 loops=1)

  • Join Filter: (gha.unified_street_id = gsa.unified_street_id)
  • Rows Removed by Join Filter: 48,126
6. 0.000 77.576 ↓ 39.0 39 1

Nested Loop (cost=14,739.82..55,731.52 rows=1 width=60) (actual time=76.263..77.576 rows=39 loops=1)

7. 0.000 76.948 ↓ 39.0 39 1

Gather (cost=14,739.53..55,727.20 rows=1 width=68) (actual time=76.249..76.948 rows=39 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
8. 0.504 192.478 ↓ 10.0 10 4 / 4

Parallel Hash Join (cost=13,739.53..54,727.10 rows=1 width=68) (actual time=66.674..192.478 rows=10 loops=4)

  • Hash Cond: (gha.unified_house_id = ghlc.unified_house_id)
9. 0.655 137.181 ↓ 3.8 1,456 4 / 4

Nested Loop (cost=302.84..41,288.97 rows=383 width=68) (actual time=4.259..137.181 rows=1,456 loops=4)

10. 0.513 121.961 ↓ 2.9 1,456 4 / 4

Nested Loop (cost=302.28..37,043.19 rows=505 width=45) (actual time=4.180..121.961 rows=1,456 loops=4)

11. 1.648 114.166 ↑ 1.8 1,456 4 / 4

Hash Join (cost=301.85..34,756.41 rows=2,602 width=37) (actual time=4.118..114.166 rows=1,456 loops=4)

  • Hash Cond: (bfla.box_feature_id = bfa.box_feature_id)
12. 108.528 108.528 ↑ 1.4 16,208 4 / 4

Parallel Seq Scan on box_feature_links_all bfla (cost=0.00..34,393.27 rows=22,939 width=8) (actual time=0.021..108.528 rows=16,208 loops=4)

  • Filter: ((billing_id = 1) AND (((CURRENT_TIMESTAMP >= active_from) AND (CURRENT_TIMESTAMP <= active_to)) OR (active_to IS NULL)))
  • Rows Removed by Filter: 527,196
13. 0.021 3.990 ↓ 1.1 33 4 / 4

Hash (cost=301.48..301.48 rows=30 width=37) (actual time=3.990..3.990 rows=33 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 3.969 3.969 ↓ 1.1 33 4 / 4

Seq Scan on box_features_all bfa (cost=0.00..301.48 rows=30 width=37) (actual time=0.243..3.969 rows=33 loops=4)

  • Filter: (((box_feature_name)::text ~~ '%ОУ%'::text) AND (billing_id = 1))
  • Rows Removed by Filter: 11,332
15. 7.282 7.282 ↑ 1.0 1 5,826 / 4

Index Scan using house_boxes_all_pkey on house_boxes_all hba (cost=0.42..0.88 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=5,826)

  • Index Cond: ((box_id = bfla.box_id) AND (billing_id = 1))
16. 14.565 14.565 ↑ 1.0 1 5,826 / 4

Index Scan using geo_houses_all_pk on geo_houses_all gha (cost=0.56..8.41 rows=1 width=35) (actual time=0.010..0.010 rows=1 loops=5,826)

  • Index Cond: ((house_id = hba.house_id) AND (billing_id = 1))
17. 0.084 54.793 ↓ 27.0 27 4 / 4

Parallel Hash (cost=13,436.67..13,436.67 rows=1 width=16) (actual time=54.793..54.793 rows=27 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 136kB
18. 54.709 54.709 ↓ 27.0 27 4 / 4

Parallel Seq Scan on geo_houses_link_campuses_all ghlc (cost=0.00..13,436.67 rows=1 width=16) (actual time=27.969..54.709 rows=27 loops=4)

  • Filter: ((campus_id = 5) AND (billing_id = 1))
  • Rows Removed by Filter: 180,738
19. 0.897 0.897 ↑ 1.0 1 39

Index Only Scan using campuses_all_pk on campuses_all ca (cost=0.29..4.31 rows=1 width=8) (actual time=0.017..0.023 rows=1 loops=39)

  • Index Cond: ((campus_id = 5) AND (billing_id = 1))
  • Heap Fetches: 0
20. 545.688 8,228.025 ↑ 1.0 1,235 39

Gather (cost=1,000.00..103,144.50 rows=1,243 width=39) (actual time=9.761..210.975 rows=1,235 loops=39)

  • Workers Planned: 4
  • Workers Launched: 4
21. 7,682.337 7,682.337 ↑ 1.3 247 195 / 5

Parallel Seq Scan on geo_streets_all gsa (cost=0.00..102,020.20 rows=311 width=39) (actual time=0.166..196.983 rows=247 loops=195)

  • Filter: ((area_id = 1) AND (billing_id = 1))
  • Rows Removed by Filter: 1,318,283
22. 0.858 0.858 ↑ 1.0 1 39

Index Scan using geo_areas_all_pk on geo_areas_all gaa (cost=0.43..8.45 rows=1 width=92) (actual time=0.020..0.022 rows=1 loops=39)

  • Index Cond: ((area_id = 1) AND (billing_id = 1))
23. 0.663 0.663 ↑ 1.0 1 39

Index Scan using porches_all_pkey on porches_all pa (cost=0.43..3.52 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=39)

  • Index Cond: ((billing_id = 1) AND (porche_id = hba.porche))