explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XhHu

Settings
# exclusive inclusive rows x rows loops node
1. 0.282 2,330.362 ↓ 41.0 41 1

Group (cost=1,657.17..1,657.17 rows=1 width=4) (actual time=2,329.875..2,330.362 rows=41 loops=1)

  • Group Key: leases.id
2. 1.537 2,330.080 ↓ 4,186.0 4,186 1

Sort (cost=1,657.17..1,657.17 rows=1 width=4) (actual time=2,329.872..2,330.080 rows=4,186 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 389kB
3. 62.675 2,328.543 ↓ 4,186.0 4,186 1

Nested Loop (cost=1,035.03..1,657.16 rows=1 width=4) (actual time=93.821..2,328.543 rows=4,186 loops=1)

4. 143.349 1,803.030 ↓ 231,419.0 231,419 1

Nested Loop (cost=1,034.94..1,656.56 rows=1 width=8) (actual time=72.286..1,803.030 rows=231,419 loops=1)

5. 91.387 1,196.843 ↓ 231,419.0 231,419 1

Nested Loop (cost=1,034.86..1,655.95 rows=1 width=8) (actual time=72.263..1,196.843 rows=231,419 loops=1)

6. 38.374 643.802 ↓ 230,827.0 230,827 1

Nested Loop (cost=1,034.77..1,655.44 rows=1 width=8) (actual time=72.228..643.802 rows=230,827 loops=1)

7. 9.919 325.387 ↓ 14,739.0 14,739 1

Nested Loop (cost=1,034.69..1,654.41 rows=1 width=8) (actual time=72.184..325.387 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))))
8. 7.621 285.990 ↓ 14,739.0 14,739 1

Nested Loop (cost=1,034.60..1,640.08 rows=1 width=21) (actual time=72.177..285.990 rows=14,739 loops=1)

9. 7.809 234.152 ↓ 1,339.9 14,739 1

Nested Loop (cost=1,034.52..1,631.93 rows=11 width=17) (actual time=72.154..234.152 rows=14,739 loops=1)

10. 3.170 167.387 ↓ 300.8 14,739 1

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

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
11. 10.244 122.341 ↓ 130.9 10,469 1

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

12. 3.985 80.345 ↓ 86.0 10,584 1

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

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
13. 9.006 76.244 ↓ 61.7 10,674 1

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

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
14. 0.919 67.238 ↓ 61.7 10,674 1

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

15. 0.001 0.027 ↓ 0.0 0 1

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

  • Recheck Cond: (user_id = 27712)
16. 0.026 0.026 ↓ 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.026..0.026 rows=0 loops=1)

  • Index Cond: (user_id = 27712)
17. 6.894 66.292 ↓ 74.6 10,674 1

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

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
18. 0.504 48.724 ↓ 74.6 10,674 1

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

19. 0.459 25.645 ↓ 12.8 3,225 1

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

20. 0.121 1.201 ↓ 1.4 123 1

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

21. 0.342 0.342 ↓ 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.045..0.342 rows=123 loops=1)

  • Index Cond: (user_id = 27712)
22. 0.738 0.738 ↑ 1.0 1 123

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

  • Index Cond: (id = user_properties.property_id)
  • Filter: (deleted_at IS NULL)
23. 23.985 23.985 ↑ 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.045..0.195 rows=26 loops=123)

  • Index Cond: (property_id = properties.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 197
24. 22.575 22.575 ↑ 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.007 rows=3 loops=3,225)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
25. 10.674 10.674 ↓ 0.0 0 10,674

Index Only Scan using index_user_spaces_on_space_id_and_user_id on user_spaces user_spaces_1 (cost=0.08..0.09 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=10,674)

  • Index Cond: ((space_id = spaces_2.id) AND (user_id = 27712))
  • Heap Fetches: 0
26. 0.033 0.116 ↓ 1.0 194 1

Hash (cost=5.81..5.81 rows=192 width=5) (actual time=0.116..0.116 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
27. 0.083 0.083 ↓ 1.0 194 1

Seq Scan on asset_roles (cost=0.00..5.81 rows=192 width=5) (actual time=0.007..0.083 rows=194 loops=1)

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
28. 31.752 31.752 ↑ 1.0 1 10,584

Index Scan using spaces_pkey on spaces (cost=0.09..4.06 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=10,584)

  • Index Cond: (id = user_spaces.space_id)
  • Filter: ((deleted_at IS NULL) AND (NOT inactive))
  • Rows Removed by Filter: 0
29. 41.876 41.876 ↑ 2.0 1 10,469

Index Scan using index_space_lease_terms_on_space_id on space_lease_terms (cost=0.08..0.66 rows=2 width=8) (actual time=0.003..0.004 rows=1 loops=10,469)

  • Index Cond: (space_id = spaces.id)
30. 58.956 58.956 ↑ 1.0 1 14,739

Index Scan using deal_terms_pkey on deal_terms (cost=0.09..0.91 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=14,739)

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
31. 44.217 44.217 ↑ 1.0 1 14,739

Index Scan using leases_pkey on leases (cost=0.09..0.74 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=14,739)

  • Index Cond: (id = deal_terms.lease_id)
  • Filter: (deleted_at IS NULL)
32. 29.478 29.478 ↑ 1.0 1 14,739

Index Scan using spaces_pkey on spaces spaces_1 (cost=0.09..0.61 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=14,739)

  • Index Cond: (id = spaces.id)
  • Filter: (deleted_at IS NULL)
33.          

SubPlan (forNested Loop)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.17..13.71 rows=1 width=0) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using index_space_lease_terms_on_space_id on space_lease_terms space_lease_terms_1 (cost=0.08..5.53 rows=2 width=4) (never executed)

  • Index Cond: (space_id = spaces_1.id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using deal_terms_pkey on deal_terms deal_terms_1 (cost=0.09..4.09 rows=1 width=4) (never executed)

  • Index Cond: (id = space_lease_terms_1.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
37. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=244,504.61..267,212.63 rows=212,199 width=4) (never executed)

  • Hash Cond: (space_lease_terms_2.lease_term_id = deal_terms_2.id)
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on space_lease_terms space_lease_terms_2 (cost=0.00..9,668.72 rows=933,908 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=234,549.34..234,549.34 rows=1,344,076 width=4) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on deal_terms deal_terms_2 (cost=10,807.71..234,549.34 rows=1,344,076 width=4) (never executed)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: ((type)::text = 'LeaseTerm'::text)
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_deal_terms_on_deleted_at (cost=0.00..10,740.50 rows=1,537,610 width=0) (never executed)

  • Index Cond: (deleted_at IS NULL)
42. 280.041 280.041 ↓ 16.0 16 14,739

Index Scan using index_deal_terms_on_lease_id on deal_terms lease_terms_leases_join (cost=0.09..1.03 rows=1 width=8) (actual time=0.004..0.019 rows=16 loops=14,739)

  • Index Cond: (lease_id = deal_terms.lease_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
  • Rows Removed by Filter: 0
43. 461.654 461.654 ↑ 1.0 1 230,827

Index Only Scan using unique_index_space_lease_terms_on_space_id_and_lease_term_id on space_lease_terms space_lease_terms_leases_join (cost=0.08..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=230,827)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 231419
44. 462.838 462.838 ↑ 1.0 1 231,419

Index Scan using spaces_pkey on spaces spaces_leases (cost=0.09..0.62 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=231,419)

  • Index Cond: (id = space_lease_terms_leases_join.space_id)
  • Filter: ((deleted_at IS NULL) AND (NOT inactive))
45. 462.838 462.838 ↓ 0.0 0 231,419

Index Scan using floors_pkey on floors (cost=0.09..0.57 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=231,419)

  • Index Cond: (id = spaces_leases.floor_id)
  • Filter: ((deleted_at IS NULL) AND (property_id = 75090))
  • Rows Removed by Filter: 1
Planning time : 11.329 ms
Execution time : 2,330.868 ms