explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JIZO

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

Unique (cost=135,102.79..135,102.80 rows=1 width=36) (actual time=1,022.110..1,022.123 rows=39 loops=1)

  • Buffers: shared hit=486,094
2. 0.355 1,022.111 ↓ 39.0 39 1

Sort (cost=135,102.79..135,102.79 rows=1 width=36) (actual time=1,022.109..1,022.111 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=486,094
3. 0.506 1,021.756 ↓ 39.0 39 1

Nested Loop (cost=15,930.67..135,102.78 rows=1 width=36) (actual time=52.327..1,021.756 rows=39 loops=1)

  • Buffers: shared hit=486,094
4. 0.134 1,020.860 ↓ 39.0 39 1

Nested Loop (cost=15,930.25..135,099.25 rows=1 width=159) (actual time=52.298..1,020.860 rows=39 loops=1)

  • Buffers: shared hit=485,938
5. 4.784 1,020.258 ↓ 39.0 39 1

Nested Loop (cost=15,929.82..135,090.79 rows=1 width=83) (actual time=52.282..1,020.258 rows=39 loops=1)

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

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

  • Buffers: shared hit=81,781
7. 0.000 47.803 ↓ 39.0 39 1

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

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=81,702
8. 0.529 140.683 ↓ 10.0 10 4 / 4

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

  • Hash Cond: (gha.unified_house_id = ghlc.unified_house_id)
  • Buffers: shared hit=81,702
9. 0.819 105.369 ↓ 3.8 1,456 4 / 4

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

  • Buffers: shared hit=71,632
10. 1.147 94.354 ↓ 2.9 1,456 4 / 4

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

  • Buffers: shared hit=42,490
11. 1.699 87.381 ↑ 1.8 1,456 4 / 4

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

  • Hash Cond: (bfla.box_feature_id = bfa.box_feature_id)
  • Buffers: shared hit=19,183
12. 81.870 81.870 ↑ 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..81.870 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.016 3.812 ↓ 1.1 33 4 / 4

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

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

Seq Scan on box_features_all bfa (cost=0.00..301.48 rows=30 width=37) (actual time=0.253..3.796 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. 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))
  • Buffers: shared hit=23,307
16. 10.196 10.196 ↑ 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.007..0.007 rows=1 loops=5,826)

  • Index Cond: ((house_id = hba.house_id) AND (billing_id = 1))
  • Buffers: shared hit=29,142
17. 0.088 34.785 ↓ 27.0 27 4 / 4

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 136kB
  • Buffers: shared hit=9,938
18. 34.697 34.697 ↓ 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=15.108..34.697 rows=27 loops=4)

  • Filter: ((campus_id = 5) AND (billing_id = 1))
  • Rows Removed by Filter: 180,903
  • Buffers: shared hit=9,938
19. 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.011..0.014 rows=1 loops=39)

  • Index Cond: ((campus_id = 5) AND (billing_id = 1))
  • Heap Fetches: 0
  • Buffers: shared hit=79
20. 823.914 967.395 ↑ 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.145..24.805 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
21. 143.481 143.481 ↑ 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.679..3.679 rows=61,263 loops=39)

  • Index Cond: (area_id = 1)
  • Buffers: shared hit=6,630
22. 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
23. 0.390 0.390 ↑ 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.010..0.010 rows=1 loops=39)

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