explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PXIs

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 41,157.484 ↓ 4.0 4 1

Group (cost=5,917.26..5,917.27 rows=1 width=92) (actual time=41,157.455..41,157.484 rows=4 loops=1)

  • Group Key: leases.id
2. 0.035 41,157.473 ↓ 28.0 28 1

Sort (cost=5,917.26..5,917.27 rows=1 width=92) (actual time=41,157.451..41,157.473 rows=28 loops=1)

  • Sort Key: leases.id
  • Sort Method: quicksort Memory: 28kB
3. 0.048 41,157.438 ↓ 28.0 28 1

Nested Loop (cost=4,363.51..5,917.26 rows=1 width=92) (actual time=36,071.573..41,157.438 rows=28 loops=1)

4. 0.036 41,157.278 ↓ 28.0 28 1

Nested Loop (cost=4,363.43..5,915.29 rows=1 width=104) (actual time=36,071.553..41,157.278 rows=28 loops=1)

5. 0.029 41,157.158 ↓ 28.0 28 1

Nested Loop (cost=4,363.35..5,913.20 rows=1 width=100) (actual time=36,071.535..41,157.158 rows=28 loops=1)

6. 0.045 41,156.961 ↓ 28.0 28 1

Nested Loop (cost=4,363.26..5,910.34 rows=1 width=100) (actual time=36,071.506..41,156.961 rows=28 loops=1)

7. 24,136.515 41,155.992 ↓ 28.0 28 1

Nested Loop (cost=4,363.17..5,908.43 rows=1 width=100) (actual time=36,071.069..41,155.992 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 212,797,091
8. 2.321 19.821 ↓ 117.4 939 1

Subquery Scan on trm_tenant_updates (cost=2,683.06..2,690.63 rows=8 width=8) (actual time=14.529..19.821 rows=939 loops=1)

  • Filter: ((trm_tenant_updates.tenant_sentiment)::text = 'negative'::text)
9. 1.903 17.500 ↑ 1.6 939 1

Unique (cost=2,683.06..2,685.36 rows=1,504 width=28) (actual time=14.526..17.500 rows=939 loops=1)

10. 1.662 15.597 ↑ 1.4 1,113 1

Sort (cost=2,683.06..2,683.83 rows=1,536 width=28) (actual time=14.525..15.597 rows=1,113 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: 135kB
11. 13.935 13.935 ↑ 1.4 1,113 1

Seq Scan on trm_tenant_updates trm_tenant_updates_1 (cost=0.00..2,666.80 rows=1,536 width=28) (actual time=0.619..13.935 rows=1,113 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((tenant_sentiment)::text = 'negative'::text))
  • Rows Removed by Filter: 42,373
12. 16,329.974 16,999.656 ↓ 75,540.3 226,621 939

Materialize (cost=1,680.12..3,217.71 rows=3 width=96) (actual time=0.035..18.104 rows=226,621 loops=939)

13. 38.661 669.682 ↓ 75,540.3 226,621 1

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

14. 10.758 313.924 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.03 rows=2 width=96) (actual time=32.461..313.924 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. 11.163 264.730 ↓ 3,203.0 9,609 1

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

16. 9.193 186.304 ↓ 436.8 9,609 1

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

17. 4.610 90.630 ↓ 137.3 9,609 1

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

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

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

19. 2.004 36.391 ↓ 24.8 3,596 1

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

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

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

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

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

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

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

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

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

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

25. 0.191 10.123 ↓ 4.2 1,295 1

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

26. 0.085 5.612 ↓ 1.2 135 1

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

27. 5.257 5.257 ↓ 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=1.075..5.257 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. 4.320 4.320 ↑ 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.009..0.032 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.049 1.245 ↓ 1.0 194 1

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

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

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.574..1.196 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. 25.137 25.137 ↑ 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.005..0.007 rows=3 loops=3,591)

  • Index Cond: (space_id = spaces.id)
36. 86.481 86.481 ↑ 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.009..0.009 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. 67.263 67.263 ↑ 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.007..0.007 rows=1 loops=9,609)

  • Index Cond: (id = deal_terms.lease_id)
38. 38.436 38.436 ↑ 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.004..0.004 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.005..0.033 rows=24 loops=9,609)

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

  • Index Cond: (lease_term_id = lease_terms_leases_join.id)
  • Heap Fetches: 1
49. 0.168 0.168 ↑ 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.006 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.09 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.112 0.112 ↑ 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.004..0.004 rows=1 loops=28)

  • Index Cond: ((property_id = properties.id) AND (id = spaces_leases_join.floor_id))
  • Heap Fetches: 28
Planning time : 12.687 ms
Execution time : 41,163.969 ms