explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ebai

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 2,285.957 ↓ 41.0 41 1

Sort (cost=1,657.17..1,657.17 rows=1 width=8) (actual time=2,285.955..2,285.957 rows=41 loops=1)

  • Sort Key: (max(deal_terms.lxd)), leases.id
  • Sort Method: quicksort Memory: 26kB
2. 0.507 2,285.933 ↓ 41.0 41 1

GroupAggregate (cost=1,657.17..1,657.17 rows=1 width=8) (actual time=2,285.200..2,285.933 rows=41 loops=1)

  • Group Key: leases.id
3. 1.525 2,285.426 ↓ 4,186.0 4,186 1

Sort (cost=1,657.17..1,657.17 rows=1 width=8) (actual time=2,285.194..2,285.426 rows=4,186 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 389kB
4. 525.466 2,283.901 ↓ 4,186.0 4,186 1

Nested Loop (cost=1,035.03..1,657.16 rows=1 width=8) (actual time=81.172..2,283.901 rows=4,186 loops=1)

5. 599.032 1,758.435 ↓ 231,419.0 231,419 1

Nested Loop (cost=1,034.94..1,656.56 rows=1 width=12) (actual time=63.276..1,758.435 rows=231,419 loops=1)

6. 553.878 1,159.403 ↓ 231,419.0 231,419 1

Nested Loop (cost=1,034.86..1,655.95 rows=1 width=12) (actual time=63.260..1,159.403 rows=231,419 loops=1)

7. 299.209 605.525 ↓ 230,827.0 230,827 1

Nested Loop (cost=1,034.77..1,655.44 rows=1 width=12) (actual time=63.250..605.525 rows=230,827 loops=1)

8. 38.849 306.316 ↓ 14,739.0 14,739 1

Nested Loop (cost=1,034.69..1,654.41 rows=1 width=12) (actual time=63.238..306.316 rows=14,739 loops=1)

  • Join Filter: ((user_spaces.space_id = spaces_1.id) AND (asset_roles.can_view_tenants OR ((spaces_1.availability_type)::text <> 'not available'::text) OR (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
9. 50.986 267.467 ↓ 14,739.0 14,739 1

Nested Loop (cost=1,034.60..1,640.08 rows=1 width=25) (actual time=63.230..267.467 rows=14,739 loops=1)

10. 60.170 216.481 ↓ 1,339.9 14,739 1

Nested Loop (cost=1,034.52..1,631.93 rows=11 width=21) (actual time=63.221..216.481 rows=14,739 loops=1)

11. 43.699 156.311 ↓ 300.8 14,739 1

Nested Loop (cost=1,034.43..1,587.41 rows=49 width=17) (actual time=63.207..156.311 rows=14,739 loops=1)

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
12. 41.804 112.612 ↓ 130.9 10,469 1

Nested Loop (cost=1,034.34..1,534.19 rows=80 width=9) (actual time=63.196..112.612 rows=10,469 loops=1)

13. 3.950 70.808 ↓ 86.0 10,584 1

Hash Join (cost=1,034.26..1,035.39 rows=123 width=5) (actual time=63.179..70.808 rows=10,584 loops=1)

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
14. 8.495 66.858 ↓ 61.7 10,674 1

HashAggregate (cost=1,027.78..1,028.30 rows=173 width=12) (actual time=63.047..66.858 rows=10,674 loops=1)

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
15. 0.897 58.363 ↓ 61.7 10,674 1

Append (cost=2.13..1,027.52 rows=173 width=12) (actual time=0.076..58.363 rows=10,674 loops=1)

16. 0.001 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on user_spaces (cost=2.13..57.41 rows=30 width=12) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: (user_id = 27712)
17. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on index_user_spaces_on_user_id (cost=0.00..2.13 rows=30 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (user_id = 27712)
18. 17.134 57.457 ↓ 74.6 10,674 1

Nested Loop Anti Join (cost=0.42..969.59 rows=143 width=12) (actual time=0.066..57.457 rows=10,674 loops=1)

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
19. 0.379 40.323 ↓ 74.6 10,674 1

Nested Loop (cost=0.34..956.16 rows=143 width=12) (actual time=0.054..40.323 rows=10,674 loops=1)

20. 0.477 20.594 ↓ 12.8 3,225 1

Nested Loop (cost=0.26..768.74 rows=251 width=12) (actual time=0.041..20.594 rows=3,225 loops=1)

21. 0.025 0.683 ↓ 1.4 123 1

Nested Loop (cost=0.17..538.30 rows=85 width=16) (actual time=0.030..0.683 rows=123 loops=1)

22. 0.166 0.166 ↓ 1.3 123 1

Index Scan using index_user_properties_on_user_id on user_properties (cost=0.09..178.03 rows=94 width=12) (actual time=0.016..0.166 rows=123 loops=1)

  • Index Cond: (user_id = 27712)
23. 0.492 0.492 ↑ 1.0 1 123

Index Scan using properties_pkey on properties (cost=0.08..3.83 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=123)

  • Index Cond: (id = user_properties.property_id)
  • Filter: (deleted_at IS NULL)
24. 19.434 19.434 ↑ 1.7 26 123

Index Scan using index_floors_on_property_id on floors floors_1 (cost=0.09..2.58 rows=45 width=8) (actual time=0.036..0.158 rows=26 loops=123)

  • Index Cond: (property_id = properties.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 197
25. 19.350 19.350 ↑ 8.7 3 3,225

Index Scan using index_spaces_on_floor_id on spaces spaces_2 (cost=0.09..0.67 rows=26 width=8) (actual time=0.003..0.006 rows=3 loops=3,225)