explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Uvc

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 3,502.243 ↓ 4.0 4 1

Group (cost=3,254.82..3,254.82 rows=1 width=92) (actual time=3,502.224..3,502.243 rows=4 loops=1)

  • Group Key: leases.id
2. 0.024 3,502.235 ↓ 28.0 28 1

Sort (cost=3,254.82..3,254.82 rows=1 width=92) (actual time=3,502.221..3,502.235 rows=28 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 28kB
3. 94.361 3,502.211 ↓ 28.0 28 1

Nested Loop (cost=1,704.77..3,254.82 rows=1 width=92) (actual time=1,019.938..3,502.211 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 5,270
4. 200.257 2,727.825 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.46..3,230.50 rows=1 width=96) (actual time=18.086..2,727.825 rows=226,675 loops=1)

5. 107.876 2,300.893 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.37..3,229.06 rows=1 width=96) (actual time=18.080..2,300.893 rows=226,675 loops=1)

6. 43.110 1,739.667 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.29..3,226.26 rows=1 width=96) (actual time=18.060..1,739.667 rows=226,675 loops=1)

7. 92.903 1,016.532 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.20..3,223.40 rows=1 width=96) (actual time=18.052..1,016.532 rows=226,675 loops=1)

8. 46.020 470.387 ↓ 75,540.3 226,621 1

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

9. 6.515 184.142 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.01 rows=2 width=96) (actual time=18.022..184.142 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))))
10. 2.661 148.800 ↓ 3,203.0 9,609 1

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

11. 5.481 107.703 ↓ 436.8 9,609 1

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

12. 5.318 63.786 ↓ 137.3 9,609 1

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

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
13. 1.571 40.513 ↓ 35.6 3,591 1

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

14. 1.434 20.962 ↓ 24.8 3,596 1

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

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
15. 3.467 19.389 ↓ 17.6 3,596 1

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

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
16. 0.334 15.922 ↓ 17.6 3,596 1

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

17. 0.014 0.014 ↓ 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.014..0.014 rows=0 loops=1)

  • Index Cond: (user_id = 62,974)
18. 2.236 15.574 ↓ 21.5 3,596 1

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

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
19. 0.097 9.742 ↓ 21.5 3,596 1

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

20. 0.127 1.875 ↓ 4.2 1,295 1

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

21. 0.045 0.398 ↓ 1.2 135 1

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

22. 0.083 0.083 ↓ 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.018..0.083 rows=135 loops=1)

  • Index Cond: (user_id = 62,974)
23. 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
24. 1.350 1.350 ↑ 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.003..0.010 rows=10 loops=135)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,294
25. 7.770 7.770 ↑ 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.003..0.006 rows=3 loops=1,295)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
26. 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
27. 0.043 0.139 ↓ 1.0 194 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
28. 0.096 0.096 ↓ 1.0 194 1

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

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
29. 17.980 17.980 ↑ 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.005..0.005 rows=1 loops=3,596)

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

  • Index Cond: (space_id = spaces.id)
31. 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))
32. 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)
33. 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)
34.          

SubPlan (for Nested Loop)

35. 0.000 0.000 ↓ 0.0 0

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

36. 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)
37. 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))
38. 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)
39. 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))
40. 0.000 0.000 ↓ 0.0 0

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

41. 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)

42. 240.225 240.225 ↓ 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.004..0.025 rows=24 loops=9,609)

  • Index Cond: (lease_id = deal_terms.lease_id)
  • Filter: ((type)::text = 'LeaseTerm'::text)
43. 453.242 453.242 ↑ 1.0 1 226,621

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.002..0.002 rows=1 loops=226,621)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 39,165
44. 680.025 680.025 ↑ 1.0 1 226,675

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=226,675)

  • Index Cond: (id = space_lease_terms_leases_join.space_id)
  • Filter: (deleted_at IS NULL)
45. 453.350 453.350 ↑ 1.0 1 226,675

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=226,675)

  • Index Cond: (id = spaces_leases_join.floor_id)
46. 226.675 226.675 ↑ 1.0 1 226,675

Index Only Scan using index_properties_on_id_no_deleted on properties (cost=0.08..1.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=226,675)

  • Index Cond: (id = floors.property_id)
  • Heap Fetches: 0
47. 0.000 680.025 ↓ 0.0 0 226,675

Unique (cost=24.31..24.31 rows=1 width=52) (actual time=0.003..0.003 rows=0 loops=226,675)

48. 226.675 680.025 ↓ 0.0 0 226,675

Sort (cost=24.31..24.31 rows=1 width=52) (actual time=0.003..0.003 rows=0 loops=226,675)

  • Sort Key: trm_tenant_updates.tenant_id, trm_tenant_updates.date DESC, trm_tenant_updates.updated_at DESC
  • Sort Method: quicksort Memory: 25kB
49. 453.350 453.350 ↓ 0.0 0 226,675

Index Scan using trm_tenant_updates_property_id_idx on trm_tenant_updates (cost=0.06..24.31 rows=1 width=52) (actual time=0.002..0.002 rows=0 loops=226,675)

  • Index Cond: (property_id = properties.id)
  • Filter: ((deleted_at IS NULL) AND ((tenant_sentiment)::text = 'negative'::text))
  • Rows Removed by Filter: 1
Planning time : 9.778 ms
Execution time : 3,502.484 ms