explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Ub1 : Optimization for: plan #OgFe

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 998.556 ↓ 39.0 39 1

Unique (cost=121,866.42..121,866.43 rows=1 width=36) (actual time=998.544..998.556 rows=39 loops=1)

  • Buffers: shared hit=501,711
2. 0.375 998.545 ↓ 39.0 39 1

Sort (cost=121,866.42..121,866.42 rows=1 width=36) (actual time=998.543..998.545 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
  • Buffers: shared hit=501,711
3. 0.479 998.170 ↓ 39.0 39 1

Nested Loop (cost=2,494.41..121,866.41 rows=1 width=36) (actual time=14.690..998.170 rows=39 loops=1)

  • Buffers: shared hit=501,711
4. 0.125 997.067 ↓ 39.0 39 1

Nested Loop (cost=2,493.99..121,862.88 rows=1 width=159) (actual time=14.606..997.067 rows=39 loops=1)

  • Buffers: shared hit=501,555
5. 4.571 996.474 ↓ 39.0 39 1

Nested Loop (cost=2,493.56..121,854.43 rows=1 width=83) (actual time=14.586..996.474 rows=39 loops=1)

  • Join Filter: (gha.unified_street_id = gsa.unified_street_id)
  • Rows Removed by Join Filter: 48,126
  • Buffers: shared hit=501,399
6. 0.000 9.727 ↓ 39.0 39 1

Nested Loop (cost=1,303.56..42,495.16 rows=1 width=60) (actual time=7.709..9.727 rows=39 loops=1)

  • Buffers: shared hit=97,398
7. 0.000 9.257 ↓ 39.0 39 1

Gather (cost=1,303.27..42,490.84 rows=1 width=68) (actual time=7.661..9.257 rows=39 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=97,319
8. 0.351 163.337 ↓ 10.0 10 4 / 4

Nested Loop (cost=303.27..41,490.74 rows=1 width=68) (actual time=18.107..163.337 rows=10 loops=4)

  • Buffers: shared hit=97,319
9. 1.578 154.247 ↓ 3.8 1,456 4 / 4

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

  • Buffers: shared hit=71,722
10. 0.668 136.648 ↓ 2.9 1,456 4 / 4

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

  • Buffers: shared hit=42,580
11. 2.086 125.784 ↑ 1.8 1,456 4 / 4

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

  • Hash Cond: (bfla.box_feature_id = bfa.box_feature_id)
  • Buffers: shared hit=19,273
12. 119.058 119.058 ↑ 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.027..119.058 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
  • Buffers: shared hit=18,617
13. 0.020 4.640 ↓ 1.1 33 4 / 4

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=524
14. 4.620 4.620 ↓ 1.1 33 4 / 4

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

  • Filter: (((box_feature_name)::text ~~ '%ОУ%'::text) AND (billing_id = 1))
  • Rows Removed by Filter: 11,332
  • Buffers: shared hit=524
15. 10.196 10.196 ↑ 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.007..0.007 rows=1 loops=5,826)

  • Index Cond: ((box_id = bfla.box_id) AND (billing_id = 1))
  • Buffers: shared hit=23,307
16. 16.021 16.021 ↑ 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.011..0.011 rows=1 loops=5,826)

  • Index Cond: ((house_id = hba.house_id) AND (billing_id = 1))
  • Buffers: shared hit=29,142
17. 8.739 8.739 ↓ 0.0 0 5,826 / 4

Index Scan using idx_unified_house_id on geo_houses_link_campuses_all ghlc (cost=0.42..0.52 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=5,826)

  • Index Cond: (unified_house_id = gha.unified_house_id)
  • Filter: ((campus_id = 5) AND (billing_id = 1))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=25,597
18. 0.546 0.546 ↑ 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.010..0.014 rows=1 loops=39)

  • Index Cond: ((campus_id = 5) AND (billing_id = 1))
  • Heap Fetches: 0
  • Buffers: shared hit=79
19. 842.244 982.176 ↑ 1.0 1,235 39

Bitmap Heap Scan on geo_streets_all gsa (cost=1,190.00..79,343.73 rows=1,243 width=39) (actual time=5.009..25.184 rows=1,235 loops=39)

  • Recheck Cond: (area_id = 1)
  • Filter: (billing_id = 1)
  • Rows Removed by Filter: 60,028
  • Heap Blocks: exact=397,371
  • Buffers: shared hit=404,001
20. 139.932 139.932 ↑ 1.0 61,263 39

Bitmap Index Scan on idx_area_id (cost=0.00..1,189.69 rows=64,168 width=0) (actual time=3.588..3.588 rows=61,263 loops=39)

  • Index Cond: (area_id = 1)
  • Buffers: shared hit=6,630
21. 0.468 0.468 ↑ 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.012..0.012 rows=1 loops=39)

  • Index Cond: ((area_id = 1) AND (billing_id = 1))
  • Buffers: shared hit=156
22. 0.624 0.624 ↑ 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.016..0.016 rows=1 loops=39)

  • Index Cond: ((billing_id = 1) AND (porche_id = hba.porche))
  • Buffers: shared hit=156