explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TBGP

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 20,413.965 ↓ 4.0 4 1

Sort (cost=6,962.73..6,962.73 rows=1 width=96) (actual time=20,413.946..20,413.965 rows=4 loops=1)

  • Sort Key: (max(deal_terms.lxd)), leases.id
  • Sort Method: quicksort Memory: 25kB
2. 0.022 20,413.942 ↓ 4.0 4 1

GroupAggregate (cost=6,962.72..6,962.73 rows=1 width=96) (actual time=20,413.914..20,413.942 rows=4 loops=1)

  • Group Key: leases.id
3. 0.025 20,413.920 ↓ 28.0 28 1

Sort (cost=6,962.72..6,962.72 rows=1 width=96) (actual time=20,413.901..20,413.920 rows=28 loops=1)

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

Nested Loop (cost=5,246.25..6,962.72 rows=1 width=96) (actual time=17,424.691..20,413.895 rows=28 loops=1)

5. 0.033 20,413.780 ↓ 28.0 28 1

Nested Loop (cost=5,246.17..6,960.75 rows=1 width=108) (actual time=17,424.675..20,413.780 rows=28 loops=1)

6. 0.032 20,413.691 ↓ 28.0 28 1

Nested Loop (cost=5,246.08..6,958.68 rows=1 width=104) (actual time=17,424.660..20,413.691 rows=28 loops=1)

7. 0.037 20,413.547 ↓ 28.0 28 1

Nested Loop (cost=5,246.00..6,955.82 rows=1 width=104) (actual time=17,424.641..20,413.547 rows=28 loops=1)

8. 11,836.183 20,413.398 ↓ 28.0 28 1

Nested Loop (cost=5,245.91..6,953.91 rows=1 width=104) (actual time=17,424.601..20,413.398 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 162,940,471
9. 3.206 57.065 ↓ 6.2 719 1

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

  • Filter: ((trm_tenant_updates.tenant_sentiment)::text = 'negative'::text)
  • Rows Removed by Filter: 19,773
10. 6.869 53.859 ↑ 1.1 20,492 1

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

11. 34.050 46.990 ↑ 1.4 41,632 1

Sort (cost=3,565.79..3,595.14 rows=58,689 width=28) (actual time=40.901..46.990 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
12. 12.940 12.940 ↑ 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.018..12.940 rows=41,632 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1,854
13. 8,115.310 8,520.150 ↓ 75,540.3 226,621 719

Materialize (cost=1,680.12..3,217.71 rows=3 width=100) (actual time=0.027..11.850 rows=226,621 loops=719)

14. 29.475 404.840 ↓ 75,540.3 226,621 1

Nested Loop (cost=1,680.12..3,217.71 rows=3 width=100) (actual time=19.341..404.840 rows=226,621 loops=1)

15. 3.841 163.967 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.03 rows=2 width=100) (actual time=19.322..163.967 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))))
16. 5.750 131.299 ↓ 3,203.0 9,609 1

Nested Loop (cost=1,679.92..3,156.11 rows=3 width=113) (actual time=19.309..131.299 rows=9,609 loops=1)

17. 8.909 96.722 ↓ 436.8 9,609 1

Nested Loop (cost=1,679.84..3,094.19 rows=22 width=21) (actual time=19.288..96.722 rows=9,609 loops=1)

18. 1.827 58.986 ↓ 137.3 9,609 1

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

  • Join Filter: (user_spaces.space_id = space_lease_terms.space_id)
19. 2.927 39.204 ↓ 35.6 3,591 1

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

20. 1.336 21.893 ↓ 24.8 3,596 1

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

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
21. 3.195 20.427 ↓ 17.6 3,596 1

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

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
22. 0.340 17.232 ↓ 17.6 3,596 1

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

23. 0.013 0.013 ↓ 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.013..0.013 rows=0 loops=1)

  • Index Cond: (user_id = 62,974)
24. 2.223 16.879 ↓ 21.5 3,596 1

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

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
25. 1.283 11.060 ↓ 21.5 3,596 1

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

26. 0.231 2.007 ↓ 4.2 1,295 1

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

27. 0.059 0.426 ↓ 1.2 135 1

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

28. 0.097 0.097 ↓ 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.023..0.097 rows=135 loops=1)

  • Index Cond: (user_id = 62,974)
29. 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
30. 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
31. 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)
32. 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
33. 0.030 0.130 ↓ 1.0 194 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
34. 0.100 0.100 ↓ 1.0 194 1

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

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
35. 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
36. 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)
37. 28.827 28.827 ↑ 1.0 1 9,609

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

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
38. 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)
39. 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)
40.          

SubPlan (for Nested Loop)

41. 0.000 0.000 ↓ 0.0 0

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

42. 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)
43. 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))
44. 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)
45. 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))
46. 0.000 0.000 ↓ 0.0 0

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

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

48. 211.398 211.398 ↓ 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.004..0.022 rows=24 loops=9,609)

  • Index Cond: (lease_id = deal_terms.lease_id)
  • Filter: ((type)::text = 'LeaseTerm'::text)
49. 0.112 0.112 ↑ 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.004 rows=1 loops=28)

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 1
50. 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)
51. 0.056 0.056 ↑ 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.002..0.002 rows=1 loops=28)

  • Index Cond: (id = trm_tenant_updates.property_id)
  • Heap Fetches: 0
52. 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 : 13.735 ms
Execution time : 20,418.565 ms