explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gLU7

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 428.475 ↓ 39.0 39 1

Unique (cost=50,562.18..50,562.19 rows=1 width=36) (actual time=428.462..428.475 rows=39 loops=1)

  • Buffers: shared hit=126,313 read=374
2. 0.326 428.463 ↓ 39.0 39 1

Sort (cost=50,562.18..50,562.18 rows=1 width=36) (actual time=428.461..428.463 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=126,313 read=374
3. 0.325 428.137 ↓ 39.0 39 1

Nested Loop (cost=4,856.99..50,562.17 rows=1 width=36) (actual time=30.480..428.137 rows=39 loops=1)

  • Buffers: shared hit=126,313 read=374
4. 0.116 427.227 ↓ 39.0 39 1

Nested Loop (cost=4,856.56..50,558.64 rows=1 width=159) (actual time=30.366..427.227 rows=39 loops=1)

  • Buffers: shared hit=126,157 read=374
5. 5.404 426.760 ↓ 39.0 39 1

Nested Loop (cost=4,856.14..50,550.18 rows=1 width=83) (actual time=30.305..426.760 rows=39 loops=1)

  • Join Filter: (gha.unified_street_id = gsa.unified_street_id)
  • Rows Removed by Join Filter: 48,126
  • Buffers: shared hit=126,001 read=374
6. 0.000 9.087 ↓ 39.0 39 1

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (bfla.box_feature_id = bfa.box_feature_id)
  • Buffers: shared hit=19,273
12. 136.896 136.896 ↑ 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.036..136.896 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.022 4.533 ↓ 1.1 33 4 / 4

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

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

Seq Scan on box_features_all bfa (cost=0.00..301.48 rows=30 width=37) (actual time=0.267..4.511 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. 18.934 18.934 ↑ 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.013..0.013 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.312 0.312 ↑ 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.007..0.008 rows=1 loops=39)

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

Bitmap Heap Scan on geo_streets_all gsa (cost=3,552.58..8,039.49 rows=1,243 width=39) (actual time=10.245..10.571 rows=1,235 loops=39)

  • Recheck Cond: ((area_id = 1) AND (billing_id = 1))
  • Heap Blocks: exact=7,761
  • Buffers: shared hit=28,603 read=374
20. 18.915 397.371 ↓ 0.0 0 39

BitmapAnd (cost=3,552.58..3,552.58 rows=1,243 width=0) (actual time=10.189..10.189 rows=0 loops=39)

  • Buffers: shared hit=20,842 read=374
21. 140.712 140.712 ↑ 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.608..3.608 rows=61,263 loops=39)

  • Index Cond: (area_id = 1)
  • Buffers: shared hit=6,630
22. 237.744 237.744 ↓ 1.1 135,870 39

Bitmap Index Scan on idx_billing_id (cost=0.00..2,362.02 rows=127,678 width=0) (actual time=6.096..6.096 rows=135,870 loops=39)

  • Index Cond: (billing_id = 1)
  • Buffers: shared hit=14,212 read=374
23. 0.351 0.351 ↑ 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.009..0.009 rows=1 loops=39)

  • Index Cond: ((area_id = 1) AND (billing_id = 1))
  • Buffers: shared hit=156
24. 0.585 0.585 ↑ 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.015..0.015 rows=1 loops=39)

  • Index Cond: ((billing_id = 1) AND (porche_id = hba.porche))
  • Buffers: shared hit=156
Planning time : 22.809 ms
Execution time : 428.905 ms