explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ef3F

Settings
# exclusive inclusive rows x rows loops node
1. 0.443 2,266.171 ↓ 2,786.0 2,786 1

Unique (cost=6,672.54..6,672.55 rows=1 width=4) (actual time=2,262.977..2,266.171 rows=2,786 loops=1)

2. 0.551 2,265.728 ↓ 2,786.0 2,786 1

Subquery Scan on leases (cost=6,672.54..6,672.55 rows=1 width=4) (actual time=2,262.976..2,265.728 rows=2,786 loops=1)

  • Filter: ((leases.deleted_at IS NULL) AND (leases.rank = 1))
  • Rows Removed by Filter: 183
3. 0.000 2,265.177 ↓ 2,969.0 2,969 1

Unique (cost=6,672.54..6,672.55 rows=1 width=108) (actual time=2,262.973..2,265.177 rows=2,969 loops=1)

4.          

Initplan (for Unique)

5. 0.009 297.582 ↑ 1.0 1 1

GroupAggregate (cost=3,719.91..3,721.25 rows=1 width=8) (actual time=297.570..297.582 rows=1 loops=1)

  • Group Key: spaces_3.id
  • Filter: (bool_and((deal_terms_5.lxd IS NOT NULL)) AND (max(deal_terms_5.lxd) >= '2020-01-01'::date) AND (max(deal_terms_5.lxd) < '2031-01-01'::date))
6. 6.293 297.573 ↑ 68.7 3 1

Sort (cost=3,719.91..3,720.01 rows=206 width=8) (actual time=297.560..297.573 rows=3 loops=1)

  • Sort Key: spaces_3.id
  • Sort Method: quicksort Memory: 1,540kB
7. 8.113 291.280 ↓ 79.9 16,464 1

Nested Loop (cost=1,537.80..3,718.32 rows=206 width=8) (actual time=62.723..291.280 rows=16,464 loops=1)

8. 6.863 217.311 ↓ 26.3 16,464 1

Nested Loop (cost=1,537.72..2,443.67 rows=626 width=8) (actual time=62.709..217.311 rows=16,464 loops=1)

9. 4.451 171.379 ↓ 255.4 13,023 1

Hash Join (cost=1,537.63..2,161.51 rows=51 width=12) (actual time=62.698..171.379 rows=13,023 loops=1)

  • Hash Cond: (user_spaces_2.asset_role_id = asset_roles_1.id)
  • Join Filter: (asset_roles_1.can_view_tenants OR ((spaces_4.availability_type)::text <> 'not available'::text) OR (NOT (alternatives: SubPlan 3 or hashed SubPlan 4)))
10. 7.562 166.790 ↓ 167.0 13,023 1

Nested Loop (cost=1,529.15..2,152.99 rows=78 width=28) (actual time=62.540..166.790 rows=13,023 loops=1)

11. 0.613 120.159 ↓ 116.3 13,023 1

Nested Loop (cost=1,529.06..1,956.44 rows=112 width=12) (actual time=62.528..120.159 rows=13,023 loops=1)

12. 10.519 66.942 ↓ 81.7 13,151 1

HashAggregate (cost=1,528.98..1,529.46 rows=161 width=12) (actual time=62.513..66.942 rows=13,151 loops=1)

  • Group Key: user_spaces_2.user_id, user_spaces_2.space_id, user_spaces_2.asset_role_id
13. 1.242 56.423 ↓ 81.7 13,151 1

Append (cost=0.08..1,528.74 rows=161 width=12) (actual time=0.094..56.423 rows=13,151 loops=1)

14. 0.014 0.014 ↓ 0.0 0 1

Index Scan using index_user_spaces_on_user_id on user_spaces user_spaces_2 (cost=0.08..68.90 rows=36 width=12) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: (user_id = 34,280)
15. 0.000 55.167 ↓ 105.2 13,151 1

Nested Loop Anti Join (cost=0.42..1,459.12 rows=125 width=12) (actual time=0.079..55.167 rows=13,151 loops=1)

  • Join Filter: (user_spaces_3.user_id = user_properties_1.user_id)
16. 2.663 30.768 ↓ 105.2 13,151 1

Nested Loop (cost=0.34..1,447.44 rows=125 width=12) (actual time=0.060..30.768 rows=13,151 loops=1)

17. 0.342 3.345 ↓ 12.7 3,095 1

Nested Loop (cost=0.25..663.88 rows=243 width=12) (actual time=0.046..3.345 rows=3,095 loops=1)

18. 0.040 0.683 ↑ 1.2 116 1

Nested Loop (cost=0.17..588.43 rows=137 width=16) (actual time=0.033..0.683 rows=116 loops=1)

19. 0.179 0.179 ↑ 1.3 116 1

Index Scan using index_user_properties_on_user_id on user_properties user_properties_1 (cost=0.09..279.40 rows=148 width=12) (actual time=0.016..0.179 rows=116 loops=1)

  • Index Cond: (user_id = 34,280)
20. 0.464 0.464 ↑ 1.0 1 116

Index Only Scan using index_properties_on_id_no_deleted on properties properties_2 (cost=0.08..2.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=116)

  • Index Cond: (id = user_properties_1.property_id)
  • Heap Fetches: 116
21. 2.320 2.320 ↑ 2.0 27 116

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors floors_2 (cost=0.08..0.39 rows=55 width=8) (actual time=0.003..0.020 rows=27 loops=116)

  • Index Cond: (property_id = properties_2.id)
  • Heap Fetches: 1,552
22. 24.760 24.760 ↑ 8.5 4 3,095

Index Scan using index_spaces_on_floor_id on spaces spaces_5 (cost=0.09..3.12 rows=34 width=8) (actual time=0.003..0.008 rows=4 loops=3,095)

  • Index Cond: (floor_id = floors_2.id)
  • Filter: (NOT team_override)
23. 26.302 26.302 ↓ 0.0 0 13,151

Index Only Scan using index_user_spaces_on_space_id_and_user_id on user_spaces user_spaces_3 (cost=0.08..0.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=13,151)

  • Index Cond: ((space_id = spaces_5.id) AND (user_id = 34,280))
  • Heap Fetches: 0
24. 52.604 52.604 ↑ 1.0 1 13,151

Index Only Scan using index_spaces_on_id_no_deleted on spaces spaces_3 (cost=0.09..2.65 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=13,151)

  • Index Cond: (id = user_spaces_2.space_id)
  • Heap Fetches: 13,023
25. 39.069 39.069 ↑ 1.0 1 13,023

Index Scan using spaces_pkey on spaces spaces_4 (cost=0.09..1.75 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=13,023)

  • Index Cond: (id = spaces_3.id)
  • Filter: (deleted_at IS NULL)
26. 0.044 0.138 ↓ 1.0 194 1

Hash (cost=7.81..7.81 rows=192 width=5) (actual time=0.137..0.138 rows=194 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
27. 0.094 0.094 ↓ 1.0 194 1

Seq Scan on asset_roles asset_roles_1 (cost=0.00..7.81 rows=192 width=5) (actual time=0.011..0.094 rows=194 loops=1)

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
28.          

SubPlan (for Hash Join)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.17..101.78 rows=6 width=0) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using index_space_lease_terms_on_space_id on space_lease_terms space_lease_terms_3 (cost=0.09..24.04 rows=19 width=4) (never executed)

  • Index Cond: (space_id = spaces_4.id)
31. 0.000 0.000 ↓ 0.0 0

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

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

Hash Join (cost=1,063,605.32..1,113,287.41 rows=673,328 width=4) (never executed)

  • Hash Cond: (space_lease_terms_4.lease_term_id = deal_terms_4.id)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using unique_index_space_lease_terms_on_space_id_and_lease_term_id on space_lease_terms space_lease_terms_4 (cost=0.09..23,090.93 rows=2,040,459 width=8) (never executed)

  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,045,445.66..1,045,445.66 rows=2,451,878 width=4) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on deal_terms deal_terms_4 (cost=0.00..1,045,445.66 rows=2,451,878 width=4) (never executed)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
36. 39.069 39.069 ↑ 12.0 1 13,023

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms space_lease_terms_5 (cost=0.09..5.50 rows=12 width=8) (actual time=0.002..0.003 rows=1 loops=13,023)

  • Index Cond: (space_id = spaces_3.id)
37. 65.856 65.856 ↑ 1.0 1 16,464

Index Scan using deal_terms_pkey on deal_terms deal_terms_5 (cost=0.09..2.04 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=16,464)

  • Index Cond: (id = space_lease_terms_5.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
38. 5.832 2,263.394 ↓ 6,254.0 6,254 1

Sort (cost=2,951.29..2,951.29 rows=1 width=108) (actual time=2,262.972..2,263.394 rows=6,254 loops=1)

  • Sort Key: leases_1.id, leases_1.tenant_id, leases_1.created_at, leases_1.updated_at, leases_1.sourceid, leases_1.deleted_at, leases_1.rentable_area, leases_1.integration_enabled, leases_1.in_place_rent_cached, leases_1.activity_log_id, leases_1.in_place_total_rent_cached, leases_1.expiring_rent_cached, leases_1.retail_lease_type, leases_1.renewal_status, (rank() OVER (?))
  • Sort Method: quicksort Memory: 1,072kB
39. 4.694 2,257.562 ↓ 6,254.0 6,254 1

WindowAgg (cost=2,951.28..2,951.29 rows=1 width=108) (actual time=2,252.471..2,257.562 rows=6,254 loops=1)

40. 3.018 2,252.868 ↓ 6,254.0 6,254 1

Sort (cost=2,951.28..2,951.28 rows=1 width=100) (actual time=2,252.462..2,252.868 rows=6,254 loops=1)

  • Sort Key: space_lease_terms.space_id, deal_terms.lxd DESC NULLS LAST
  • Sort Method: quicksort Memory: 1,072kB
41. 12.200 2,249.850 ↓ 6,254.0 6,254 1

Group (cost=2,951.28..2,951.28 rows=1 width=100) (actual time=2,231.001..2,249.850 rows=6,254 loops=1)

  • Group Key: leases_1.id, space_lease_terms.space_id, deal_terms.lxd
42. 91.776 2,237.650 ↓ 110,598.0 110,598 1

Sort (cost=2,951.28..2,951.28 rows=1 width=100) (actual time=2,230.997..2,237.650 rows=110,598 loops=1)

  • Sort Key: leases_1.id, space_lease_terms.space_id, deal_terms.lxd
  • Sort Method: quicksort Memory: 18,625kB
43. 320.686 2,145.874 ↓ 110,598.0 110,598 1

Result (cost=1,538.34..2,951.28 rows=1 width=100) (actual time=358.927..2,145.874 rows=110,598 loops=1)

  • One-Time Filter: $14
44. 44.286 1,825.188 ↓ 110,598.0 110,598 1

Nested Loop (cost=1,538.34..2,951.28 rows=1 width=100) (actual time=61.353..1,825.188 rows=110,598 loops=1)

45. 80.193 1,559.706 ↓ 110,598.0 110,598 1

Nested Loop (cost=1,538.26..2,949.83 rows=1 width=104) (actual time=61.345..1,559.706 rows=110,598 loops=1)

46. 60.088 1,258.317 ↓ 110,598.0 110,598 1

Nested Loop (cost=1,538.17..2,947.03 rows=1 width=104) (actual time=61.329..1,258.317 rows=110,598 loops=1)

47. 66.965 866.435 ↓ 110,598.0 110,598 1

Nested Loop (cost=1,538.09..2,944.17 rows=1 width=104) (actual time=61.322..866.435 rows=110,598 loops=1)

48. 30.850 578.138 ↓ 55,333.0 110,666 1

Nested Loop (cost=1,538.00..2,941.28 rows=2 width=104) (actual time=61.312..578.138 rows=110,666 loops=1)

  • Join Filter: (deal_terms.lease_id = lease_terms_leases_join.lease_id)
49. 3.264 359.098 ↓ 6,273.0 6,273 1

Nested Loop (cost=1,537.89..2,937.31 rows=1 width=104) (actual time=61.299..359.098 rows=6,273 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))))
50. 5.076 330.742 ↓ 6,273.0 6,273 1

Nested Loop (cost=1,537.80..2,917.33 rows=1 width=113) (actual time=61.287..330.742 rows=6,273 loops=1)

51. 9.270 294.301 ↓ 697.0 6,273 1

Nested Loop (cost=1,537.72..2,891.82 rows=9 width=21) (actual time=61.275..294.301 rows=6,273 loops=1)

52. 9.661 202.711 ↓ 294.0 16,464 1

Nested Loop (cost=1,537.63..2,728.17 rows=56 width=17) (actual time=61.252..202.711 rows=16,464 loops=1)

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
53. 4.725 140.958 ↓ 162.8 13,023 1

Nested Loop (cost=1,537.55..1,840.50 rows=80 width=9) (actual time=61.241..140.958 rows=13,023 loops=1)

54. 4.477 70.478 ↓ 115.4 13,151 1

Hash Join (cost=1,537.46..1,538.51 rows=114 width=5) (actual time=61.226..70.478 rows=13,151 loops=1)

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
55. 10.527 65.874 ↓ 81.7 13,151 1

HashAggregate (cost=1,528.98..1,529.46 rows=161 width=12) (actual time=61.085..65.874 rows=13,151 loops=1)

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
56. 1.226 55.347 ↓ 81.7 13,151 1

Append (cost=0.08..1,528.74 rows=161 width=12) (actual time=0.071..55.347 rows=13,151 loops=1)

57. 0.012 0.012 ↓ 0.0 0 1

Index Scan using index_user_spaces_on_user_id on user_spaces (cost=0.08..68.90 rows=36 width=12) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (user_id = 34,280)
58. 0.000 54.109 ↓ 105.2 13,151 1

Nested Loop Anti Join (cost=0.42..1,459.12 rows=125 width=12) (actual time=0.058..54.109 rows=13,151 loops=1)

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
59. 1.622 29.586 ↓ 105.2 13,151 1

Nested Loop (cost=0.34..1,447.44 rows=125 width=12) (actual time=0.049..29.586 rows=13,151 loops=1)

60. 0.324 3.204 ↓ 12.7 3,095 1

Nested Loop (cost=0.25..663.88 rows=243 width=12) (actual time=0.038..3.204 rows=3,095 loops=1)

61. 0.034 0.676 ↑ 1.2 116 1

Nested Loop (cost=0.17..588.43 rows=137 width=16) (actual time=0.027..0.676 rows=116 loops=1)

62. 0.178 0.178 ↑ 1.3 116 1

Index Scan using index_user_properties_on_user_id on user_properties (cost=0.09..279.40 rows=148 width=12) (actual time=0.014..0.178 rows=116 loops=1)

  • Index Cond: (user_id = 34,280)
63. 0.464 0.464 ↑ 1.0 1 116

Index Only Scan using index_properties_on_id_no_deleted on properties properties_1 (cost=0.08..2.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=116)

  • Index Cond: (id = user_properties.property_id)
  • Heap Fetches: 116
64. 2.204 2.204 ↑ 2.0 27 116

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors floors_1 (cost=0.08..0.39 rows=55 width=8) (actual time=0.003..0.019 rows=27 loops=116)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,552
65. 24.760 24.760 ↑ 8.5 4 3,095

Index Scan using index_spaces_on_floor_id on spaces spaces_2 (cost=0.09..3.12 rows=34 width=8) (actual time=0.003..0.008 rows=4 loops=3,095)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
66. 26.302 26.302 ↓ 0.0 0 13,151

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.002..0.002 rows=0 loops=13,151)

  • Index Cond: ((space_id = spaces_2.id) AND (user_id = 34,280))
  • Heap Fetches: 0
67. 0.030 0.127 ↓ 1.0 194 1

Hash (cost=7.81..7.81 rows=192 width=5) (actual time=0.126..0.127 rows=194 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
68. 0.097 0.097 ↓ 1.0 194 1

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.010..0.097 rows=194 loops=1)

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
69. 65.755 65.755 ↑ 1.0 1 13,151

Index Only Scan using index_spaces_on_id_no_deleted on spaces (cost=0.09..2.65 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=13,151)

  • Index Cond: (id = user_spaces.space_id)
  • Heap Fetches: 13,023
70. 52.092 52.092 ↑ 12.0 1 13,023

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms (cost=0.09..11.05 rows=12 width=8) (actual time=0.003..0.004 rows=1 loops=13,023)

  • Index Cond: (space_id = spaces.id)
71. 82.320 82.320 ↓ 0.0 0 16,464

Index Scan using deal_terms_pkey on deal_terms (cost=0.09..2.92 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=16,464)

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text) AND ((lxd > '2020-09-01'::date) OR (lxd IS NULL) OR (is_month_to_month AND ((move_out_date >= '2020-09-01'::date) OR (move_out_date IS NULL)))))
  • Rows Removed by Filter: 1
72. 31.365 31.365 ↑ 1.0 1 6,273

Index Scan using index_leases_on_id_no_deleted on leases leases_1 (cost=0.09..2.84 rows=1 width=92) (actual time=0.005..0.005 rows=1 loops=6,273)

  • Index Cond: (id = deal_terms.lease_id)
73. 25.092 25.092 ↑ 1.0 1 6,273

Index Scan using spaces_pkey on spaces spaces_1 (cost=0.09..2.86 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=6,273)

  • Index Cond: (id = space_lease_terms.space_id)
  • Filter: (deleted_at IS NULL)
74.          

SubPlan (for Nested Loop)

75. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.17..101.78 rows=6 width=0) (never executed)

76. 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.09..24.04 rows=19 width=4) (never executed)

  • Index Cond: (space_id = spaces_1.id)
77. 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))
78. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,063,605.32..1,113,287.41 rows=673,328 width=4) (never executed)

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

Index Only Scan using unique_index_space_lease_terms_on_space_id_and_lease_term_id on space_lease_terms space_lease_terms_2 (cost=0.09..23,090.93 rows=2,040,459 width=8) (never executed)

  • Heap Fetches: 0
80. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,045,445.66..1,045,445.66 rows=2,451,878 width=4) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Seq Scan on deal_terms deal_terms_2 (cost=0.00..1,045,445.66 rows=2,451,878 width=4) (never executed)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
82. 188.190 188.190 ↓ 9.0 18 6,273

Index Scan using index_deal_terms_on_lease_id_no_deleted on deal_terms lease_terms_leases_join (cost=0.11..3.97 rows=2 width=8) (actual time=0.005..0.030 rows=18 loops=6,273)

  • Index Cond: (lease_id = leases_1.id)
  • Filter: ((type)::text = 'LeaseTerm'::text)
83. 221.332 221.332 ↑ 1.0 1 110,666

Index Only Scan using partial_index_space_lease_terms_on_lease_term_id_and_space_id on space_lease_terms space_lease_terms_leases_join (cost=0.09..1.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=110,666)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 4,104
84. 331.794 331.794 ↑ 1.0 1 110,598

Index Scan using spaces_pkey on spaces spaces_leases_join (cost=0.09..2.86 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=110,598)

  • Index Cond: (id = space_lease_terms_leases_join.space_id)
  • Filter: (deleted_at IS NULL)
85. 221.196 221.196 ↑ 1.0 1 110,598

Index Scan using index_floors_on_deleted_at_no_deleted on floors (cost=0.08..2.80 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=110,598)

  • Index Cond: (id = spaces_leases_join.floor_id)
86. 221.196 221.196 ↑ 1.0 1 110,598

Index Only Scan using index_properties_on_id_no_deleted on properties (cost=0.08..1.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=110,598)

  • Index Cond: (id = floors.property_id)
  • Heap Fetches: 110,598
Planning time : 47.622 ms
Execution time : 2,267.134 ms