explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uakr

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 31,866.164 ↓ 4.0 4 1

Group (cost=6,962.72..6,962.72 rows=1 width=92) (actual time=31,866.124..31,866.164 rows=4 loops=1)

  • Group Key: leases.id
2. 0.051 31,866.143 ↓ 28.0 28 1

Sort (cost=6,962.72..6,962.72 rows=1 width=92) (actual time=31,866.119..31,866.143 rows=28 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 28kB
3. 0.033 31,866.092 ↓ 28.0 28 1

Nested Loop (cost=5,246.25..6,962.72 rows=1 width=92) (actual time=27,351.015..31,866.092 rows=28 loops=1)

4. 0.049 31,865.919 ↓ 28.0 28 1

Nested Loop (cost=5,246.17..6,960.75 rows=1 width=104) (actual time=27,350.996..31,865.919 rows=28 loops=1)

5. 0.021 31,865.786 ↓ 28.0 28 1

Nested Loop (cost=5,246.08..6,958.68 rows=1 width=100) (actual time=27,350.978..31,865.786 rows=28 loops=1)

6. 0.046 31,865.569 ↓ 28.0 28 1

Nested Loop (cost=5,246.00..6,955.82 rows=1 width=100) (actual time=27,350.942..31,865.569 rows=28 loops=1)

7. 18,509.380 31,865.383 ↓ 28.0 28 1

Nested Loop (cost=5,245.91..6,953.91 rows=1 width=100) (actual time=27,350.914..31,865.383 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 162,940,471
8. 4.256 78.230 ↓ 6.2 719 1

Subquery Scan on trm_tenant_updates (cost=3,565.79..3,734.80 rows=116 width=8) (actual time=57.543..78.230 rows=719 loops=1)

  • Filter: ((trm_tenant_updates.tenant_sentiment)::text = 'negative'::text)
  • Rows Removed by Filter: 19,773
9. 8.909 73.974 ↑ 1.1 20,492 1

Unique (cost=3,565.79..3,653.83 rows=23,136 width=28) (actual time=57.505..73.974 rows=20,492 loops=1)

10. 44.699 65.065 ↑ 1.4 41,632 1

Sort (cost=3,565.79..3,595.14 rows=58,689 width=28) (actual time=57.503..65.065 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. 20.366 20.366 ↑ 1.4 41,632 1

Seq Scan on trm_tenant_updates trm_tenant_updates_1 (cost=0.00..2,636.11 rows=58,689 width=28) (actual time=0.014..20.366 rows=41,632 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1,854
12. 12,660.640 13,277.773 ↓ 75,540.3 226,621 719

Materialize (cost=1,680.12..3,217.71 rows=3 width=96) (actual time=0.042..18.467 rows=226,621 loops=719)

13. 50.780 617.133 ↓ 75,540.3 226,621 1

Nested Loop (cost=1,680.12..3,217.71 rows=3 width=96) (actual time=29.604..617.133 rows=226,621 loops=1)

14. 5.762 249.256 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.03 rows=2 width=96) (actual time=29.585..249.256 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. 14.051 195.449 ↓ 3,203.0 9,609 1

Nested Loop (cost=1,679.92..3,156.11 rows=3 width=109) (actual time=29.568..195.449 rows=9,609 loops=1)

16. 8.249 142.962 ↓ 436.8 9,609 1

Nested Loop (cost=1,679.84..3,094.19 rows=22 width=17) (actual time=29.550..142.962 rows=9,609 loops=1)

17. 4.706 86.668 ↓ 137.3 9,609 1

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

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

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

19. 2.273 34.016 ↓ 24.8 3,596 1

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

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

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

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

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

22. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

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

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

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

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

25. 0.260 2.907 ↓ 4.2 1,295 1

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

26. 0.094 0.622 ↓ 1.2 135 1

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

27. 0.123 0.123 ↓ 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.014..0.123 rows=135 loops=1)

  • Index Cond: (user_id = 62,974)
28. 0.405 0.405 ↑ 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.003..0.003 rows=1 loops=135)

  • Index Cond: (id = user_properties.property_id)
  • Heap Fetches: 0
29. 2.025 2.025 ↑ 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.005..0.015 rows=10 loops=135)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,294
30. 10.360 10.360 ↑ 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.005..0.008 rows=3 loops=1,295)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
31. 7.192 7.192 ↓ 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.002..0.002 rows=0 loops=3,596)

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

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

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

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

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
34. 21.576 21.576 ↑ 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.006..0.006 rows=1 loops=3,596)

  • Index Cond: (id = user_spaces.space_id)
  • Heap Fetches: 3,591
35. 21.546 21.546 ↑ 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.004..0.006 rows=3 loops=3,591)

  • Index Cond: (space_id = spaces.id)
36. 48.045 48.045 ↑ 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.005..0.005 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. 38.436 38.436 ↑ 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.004..0.004 rows=1 loops=9,609)

  • Index Cond: (id = deal_terms.lease_id)
38. 48.045 48.045 ↑ 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.005..0.005 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,075,924.04 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,045,025.58 rows=2,349,846 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. 317.097 317.097 ↓ 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.83 rows=2 width=8) (actual time=0.006..0.033 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.005..0.005 rows=1 loops=28)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 1
49. 0.196 0.196 ↑ 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.006..0.007 rows=1 loops=28)

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

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

  • Index Cond: (id = trm_tenant_updates.property_id)
  • Heap Fetches: 0
51. 0.140 0.140 ↑ 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.005..0.005 rows=1 loops=28)

  • Index Cond: ((property_id = properties.id) AND (id = spaces_leases_join.floor_id))
  • Heap Fetches: 28
Planning time : 18.167 ms
Execution time : 31,871.705 ms