explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KBAf

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 20,171.193 ↓ 4.0 4 1

Group (cost=6,567.80..6,567.80 rows=1 width=92) (actual time=20,171.159..20,171.193 rows=4 loops=1)

  • Group Key: leases.id
2. 0.060 20,171.176 ↓ 28.0 28 1

Sort (cost=6,567.80..6,567.80 rows=1 width=92) (actual time=20,171.156..20,171.176 rows=28 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 28kB
3. 0.031 20,171.116 ↓ 28.0 28 1

Nested Loop (cost=4,901.00..6,567.79 rows=1 width=92) (actual time=17,216.414..20,171.116 rows=28 loops=1)

4. 0.025 20,171.001 ↓ 28.0 28 1

Nested Loop (cost=4,900.92..6,565.82 rows=1 width=104) (actual time=17,216.398..20,171.001 rows=28 loops=1)

5. 0.018 20,170.920 ↓ 28.0 28 1

Nested Loop (cost=4,900.83..6,563.73 rows=1 width=100) (actual time=17,216.386..20,170.920 rows=28 loops=1)

6. 0.013 20,170.790 ↓ 28.0 28 1

Nested Loop (cost=4,900.75..6,560.87 rows=1 width=100) (actual time=17,216.365..20,170.790 rows=28 loops=1)

7. 11,634.281 20,170.637 ↓ 28.0 28 1

Nested Loop (cost=4,900.66..6,558.96 rows=1 width=100) (actual time=17,216.315..20,170.637 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 162,940,471
8. 2.764 60.065 ↓ 8.8 719 1

Subquery Scan on trm_tenant_updates (cost=3,220.55..3,340.30 rows=82 width=8) (actual time=44.831..60.065 rows=719 loops=1)

  • Filter: ((trm_tenant_updates.tenant_sentiment)::text = 'negative'::text)
  • Rows Removed by Filter: 19,773
9. 6.510 57.301 ↓ 1.2 20,492 1

Unique (cost=3,220.55..3,282.92 rows=16,394 width=28) (actual time=44.807..57.301 rows=20,492 loops=1)

10. 35.117 50.791 ↓ 1.0 41,632 1

Sort (cost=3,220.55..3,241.34 rows=41,586 width=28) (actual time=44.806..50.791 rows=41,632 loops=1)

  • Sort Key: trm_tenant_updates_1.property_id, trm_tenant_updates_1.tenant_id, trm_tenant_updates_1.date DESC, trm_tenant_updates_1.updated_at DESC
  • Sort Method: quicksort Memory: 4,607kB
11. 15.674 15.674 ↓ 1.0 41,632 1

Seq Scan on trm_tenant_updates trm_tenant_updates_1 (cost=0.00..2,582.46 rows=41,586 width=28) (actual time=0.021..15.674 rows=41,632 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1,854
12. 8,059.159 8,476.291 ↓ 75,540.3 226,621 719

Materialize (cost=1,680.12..3,217.68 rows=3 width=96) (actual time=0.041..11.789 rows=226,621 loops=719)

13. 36.490 417.132 ↓ 75,540.3 226,621 1

Nested Loop (cost=1,680.12..3,217.67 rows=3 width=96) (actual time=28.982..417.132 rows=226,621 loops=1)

14. 3.502 178.853 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.01 rows=2 width=96) (actual time=28.952..178.853 rows=9,609 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))))
15. 10.156 146.524 ↓ 3,203.0 9,609 1

Nested Loop (cost=1,679.92..3,156.08 rows=3 width=109) (actual time=28.932..146.524 rows=9,609 loops=1)

16. 1.790 107.541 ↓ 436.8 9,609 1

Nested Loop (cost=1,679.84..3,094.23 rows=22 width=17) (actual time=28.909..107.541 rows=9,609 loops=1)

17. 1.587 67.315 ↓ 137.3 9,609 1

Nested Loop (cost=1,679.75..2,889.64 rows=70 width=17) (actual time=28.881..67.315 rows=9,609 loops=1)

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
18. 1.948 47.773 ↓ 35.6 3,591 1

Nested Loop (cost=1,679.66..2,036.56 rows=101 width=9) (actual time=28.859..47.773 rows=3,591 loops=1)

19. 1.183 31.441 ↓ 24.8 3,596 1

Hash Join (cost=1,679.58..1,680.91 rows=145 width=5) (actual time=28.834..31.441 rows=3,596 loops=1)

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
20. 3.408 30.107 ↓ 17.6 3,596 1

HashAggregate (cost=1,671.10..1,671.71 rows=204 width=12) (actual time=28.664..30.107 rows=3,596 loops=1)

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
21. 0.337 26.699 ↓ 17.6 3,596 1

Append (cost=0.08..1,670.79 rows=204 width=12) (actual time=0.163..26.699 rows=3,596 loops=1)

22. 0.021 0.021 ↓ 0.0 0 1

Index Scan using index_user_spaces_on_user_id on user_spaces (cost=0.08..70.72 rows=37 width=12) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (user_id = 62,974)
23. 2.315 26.341 ↓ 21.5 3,596 1

Nested Loop Anti Join (cost=0.42..1,599.15 rows=167 width=12) (actual time=0.141..26.341 rows=3,596 loops=1)

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
24. 1.211 20.430 ↓ 21.5 3,596 1

Nested Loop (cost=0.34..1,583.44 rows=167 width=12) (actual time=0.121..20.430 rows=3,596 loops=1)

25. 0.213 3.679 ↓ 4.2 1,295 1

Nested Loop (cost=0.25..548.88 rows=311 width=12) (actual time=0.095..3.679 rows=1,295 loops=1)

26. 0.109 0.496 ↓ 1.2 135 1

Nested Loop (cost=0.17..455.67 rows=109 width=16) (actual time=0.060..0.496 rows=135 loops=1)

27. 0.117 0.117 ↓ 1.1 135 1

Index Scan using index_user_properties_on_user_id on user_properties (cost=0.09..209.19 rows=119 width=12) (actual time=0.029..0.117 rows=135 loops=1)

  • Index Cond: (user_id = 62,974)
28. 0.270 0.270 ↑ 1.0 1 135

Index Only Scan using index_properties_on_id_no_deleted on properties properties_1 (cost=0.08..2.07 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=135)

  • Index Cond: (id = user_properties.property_id)
  • Heap Fetches: 0
29. 2.970 2.970 ↑ 5.3 10 135

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors floors_1 (cost=0.08..0.70 rows=53 width=8) (actual time=0.006..0.022 rows=10 loops=135)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,294
30. 15.540 15.540 ↑ 11.3 3 1,295

Index Scan using index_spaces_on_floor_id on spaces spaces_2 (cost=0.09..3.22 rows=34 width=8) (actual time=0.006..0.012 rows=3 loops=1,295)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
31. 3.596 3.596 ↓ 0.0 0 3,596

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=3,596)

  • Index Cond: ((space_id = spaces_2.id) AND (user_id = 62,974))
  • Heap Fetches: 0
32. 0.036 0.151 ↓ 1.0 194 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
33. 0.115 0.115 ↓ 1.0 194 1

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

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
34. 14.384 14.384 ↑ 1.0 1 3,596

Index Only Scan using index_spaces_on_id_no_deleted on spaces (cost=0.09..2.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=3,596)

  • Index Cond: (id = user_spaces.space_id)
  • Heap Fetches: 3,591
35. 17.955 17.955 ↑ 3.0 3 3,591

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms (cost=0.09..8.41 rows=9 width=8) (actual time=0.003..0.005 rows=3 loops=3,591)

  • Index Cond: (space_id = spaces.id)
36. 38.436 38.436 ↑ 1.0 1 9,609

Index Scan using deal_terms_pkey on deal_terms (cost=0.09..2.92 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=9,609)

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
37. 28.827 28.827 ↑ 1.0 1 9,609

Index Scan using index_leases_on_id_no_deleted on leases (cost=0.09..2.81 rows=1 width=92) (actual time=0.003..0.003 rows=1 loops=9,609)

  • Index Cond: (id = deal_terms.lease_id)
38. 28.827 28.827 ↑ 1.0 1 9,609

Index Scan using spaces_pkey on spaces spaces_1 (cost=0.09..2.86 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=9,609)

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

SubPlan (for Nested Loop)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.17..74.87 rows=5 width=0) (never executed)

41. 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..17.59 rows=14 width=4) (never executed)

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

Hash Join (cost=26,790.98..1,081,005.62 rows=585,877 width=4) (never executed)

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

Seq Scan on deal_terms deal_terms_2 (cost=0.00..1,049,679.72 rows=2,777,294 width=4) (never executed)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
45. 0.000 0.000 ↓ 0.0 0

Hash (cost=20,411.84..20,411.84 rows=1,822,612 width=8) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Seq Scan on space_lease_terms space_lease_terms_2 (cost=0.00..20,411.84 rows=1,822,612 width=8) (never executed)

47. 201.789 201.789 ↓ 12.0 24 9,609

Index Scan using index_deal_terms_on_lease_id_no_deleted on deal_terms lease_terms_leases_join (cost=0.11..3.82 rows=2 width=8) (actual time=0.003..0.021 rows=24 loops=9,609)

  • Index Cond: (lease_id = deal_terms.lease_id)
  • Filter: ((type)::text = 'LeaseTerm'::text)
48. 0.140 0.140 ↑ 1.0 1 28

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.91 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=28)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 1
49. 0.112 0.112 ↑ 1.0 1 28

Index Scan using spaces_pkey on spaces spaces_leases_join (cost=0.09..2.86 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=28)

  • Index Cond: (id = space_lease_terms_leases_join.space_id)
  • Filter: (deleted_at IS NULL)
50. 0.056 0.056 ↑ 1.0 1 28

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

  • Index Cond: (id = trm_tenant_updates.property_id)
  • Heap Fetches: 0
51. 0.084 0.084 ↑ 1.0 1 28

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors (cost=0.08..1.97 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=28)

  • Index Cond: ((property_id = properties.id) AND (id = spaces_leases_join.floor_id))
  • Heap Fetches: 28
Planning time : 16.856 ms
Execution time : 20,177.849 ms