explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1bpXV

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

Unique (cost=159,735.16..159,735.17 rows=1 width=36) (actual time=8,222.878..8,222.891 rows=39 loops=1)

2. 0.363 8,222.879 ↓ 39.0 39 1

Sort (cost=159,735.16..159,735.17 rows=1 width=36) (actual time=8,222.877..8,222.879 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.634 8,222.516 ↓ 39.0 39 1

Nested Loop (cost=15,747.11..159,735.15 rows=1 width=36) (actual time=51.671..8,222.516 rows=39 loops=1)

4. 0.214 8,221.336 ↓ 39.0 39 1

Nested Loop (cost=15,746.69..159,731.63 rows=1 width=158) (actual time=51.607..8,221.336 rows=39 loops=1)

5. 0.000 8,220.225 ↓ 39.0 39 1

Nested Loop (cost=15,746.26..159,723.18 rows=1 width=83) (actual time=51.562..8,220.225 rows=39 loops=1)

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

Nested Loop (cost=14,746.26..56,549.77 rows=1 width=60) (actual time=50.652..52.142 rows=39 loops=1)

7. 0.000 51.378 ↓ 39.0 39 1

Gather (cost=14,745.97..56,541.45 rows=1 width=68) (actual time=50.630..51.378 rows=39 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
8. 0.529 165.266 ↓ 10.0 10 4 / 4

Parallel Hash Join (cost=13,745.97..55,541.35 rows=1 width=68) (actual time=49.692..165.266 rows=10 loops=4)

  • Hash Cond: (gha.unified_house_id = ghlc.unified_house_id)
9. 0.250 126.513 ↓ 2.7 1,456 4 / 4

Nested Loop (cost=303.52..42,096.87 rows=539 width=68) (actual time=4.181..126.513 rows=1,456 loops=4)

10. 1.371 113.155 ↓ 2.6 1,456 4 / 4

Nested Loop (cost=302.96..37,397.51 rows=559 width=45) (actual time=4.105..113.155 rows=1,456 loops=4)

11. 1.557 105.958 ↑ 2.0 1,456 4 / 4

Hash Join (cost=302.53..34,798.31 rows=2,956 width=37) (actual time=4.057..105.958 rows=1,456 loops=4)

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

Parallel Seq Scan on box_feature_links_all bfla (cost=0.00..34,434.55 rows=22,916 width=8) (actual time=0.023..100.477 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.022 3.924 ↑ 1.0 33 4 / 4

Hash (cost=302.11..302.11 rows=34 width=37) (actual time=3.924..3.924 rows=33 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 3.902 3.902 ↑ 1.0 33 4 / 4

Seq Scan on box_features_all bfa (cost=0.00..302.11 rows=34 width=37) (actual time=0.251..3.902 rows=33 loops=4)

  • Filter: (((box_feature_name)::text ~~ '%ОУ%'::text) AND (billing_id = 1))
  • Rows Removed by Filter: 11,332
15. 5.826 5.826 ↑ 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.004..0.004 rows=1 loops=5,826)

  • Index Cond: ((box_id = bfla.box_id) AND (billing_id = 1))
16. 13.108 13.108 ↑ 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.009..0.009 rows=1 loops=5,826)

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

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

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

Parallel Seq Scan on geo_houses_link_campuses_all ghlc (cost=0.00..13,442.44 rows=1 width=16) (actual time=18.742..38.126 rows=27 loops=4)

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

Index Only Scan using campuses_all_pk on campuses_all ca (cost=0.29..8.31 rows=1 width=8) (actual time=0.019..0.025 rows=1 loops=39)

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

Gather (cost=1,000.00..103,157.76 rows=1,252 width=39) (actual time=10.005..209.583 rows=1,235 loops=39)

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

Parallel Seq Scan on geo_streets_all gsa (cost=0.00..102,032.56 rows=313 width=39) (actual time=0.159..195.307 rows=247 loops=195)

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

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

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

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

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