explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XmCH

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 28,245.999 ↓ 4.0 4 1

Group (cost=4,537.53..4,537.53 rows=1 width=92) (actual time=28,245.968..28,245.999 rows=4 loops=1)

  • Group Key: leases.id
2. 0.030 28,245.987 ↓ 28.0 28 1

Sort (cost=4,537.53..4,537.53 rows=1 width=92) (actual time=28,245.962..28,245.987 rows=28 loops=1)

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

Nested Loop (cost=2,986.02..4,537.53 rows=1 width=92) (actual time=24,300.838..28,245.957 rows=28 loops=1)

4. 0.034 28,245.836 ↓ 28.0 28 1

Nested Loop (cost=2,985.93..4,535.56 rows=1 width=104) (actual time=24,300.824..28,245.836 rows=28 loops=1)

5. 0.034 28,245.746 ↓ 28.0 28 1

Nested Loop (cost=2,985.85..4,533.47 rows=1 width=100) (actual time=24,300.813..28,245.746 rows=28 loops=1)

6. 0.024 28,245.600 ↓ 28.0 28 1

Nested Loop (cost=2,985.77..4,530.61 rows=1 width=100) (actual time=24,300.796..28,245.600 rows=28 loops=1)

7. 16,565.583 28,245.464 ↓ 28.0 28 1

Nested Loop (cost=2,985.68..4,528.70 rows=1 width=100) (actual time=24,300.776..28,245.464 rows=28 loops=1)

  • Join Filter: (leases.tenant_id = trm_tenant_updates.tenant_id)
  • Rows Removed by Join Filter: 212,797,091
8. 2.198 6.233 ↓ 187.8 939 1

Subquery Scan on trm_tenant_updates (cost=1,305.56..1,310.93 rows=5 width=8) (actual time=1.381..6.233 rows=939 loops=1)

  • Filter: ((trm_tenant_updates.tenant_sentiment)::text = 'negative'::text)
9. 1.719 4.035 ↑ 1.1 939 1

Unique (cost=1,305.56..1,307.19 rows=1,066 width=28) (actual time=1.379..4.035 rows=939 loops=1)

10. 1.357 2.316 ↓ 1.0 1,113 1

Sort (cost=1,305.56..1,306.11 rows=1,088 width=28) (actual time=1.378..2.316 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. 0.815 0.959 ↓ 1.0 1,113 1

Bitmap Heap Scan on trm_tenant_updates trm_tenant_updates_1 (cost=9.82..1,294.59 rows=1,088 width=28) (actual time=0.182..0.959 rows=1,113 loops=1)

  • Recheck Cond: ((tenant_sentiment)::text = 'negative'::text)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 62
  • Heap Blocks: exact=331
12. 0.144 0.144 ↓ 1.0 1,175 1

Bitmap Index Scan on test_index_for_perf_improv (cost=0.00..9.77 rows=1,138 width=0) (actual time=0.143..0.144 rows=1,175 loops=1)

  • Index Cond: ((tenant_sentiment)::text = 'negative'::text)
13. 11,276.661 11,673.648 ↓ 75,540.3 226,621 939

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

14. 35.394 396.987 ↓ 75,540.3 226,621 1

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

15. 3.537 159.804 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.03 rows=2 width=96) (actual time=18.694..159.804 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.294 127.440 ↓ 3,203.0 9,609 1

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

17. 8.429 93.319 ↓ 436.8 9,609 1

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

18. 4.595 56.063 ↓ 137.3 9,609 1

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

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

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

20. 1.182 21.108 ↓ 24.8 3,596 1

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

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

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

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

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

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

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

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

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

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

26. 0.163 2.300 ↓ 4.2 1,295 1

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

27. 0.033 0.382 ↓ 1.2 135 1

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

28. 0.079 0.079 ↓ 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.013..0.079 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.755 1.755 ↑ 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.007..0.013 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.029 0.123 ↓ 1.0 194 1

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

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

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.007..0.094 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. 14.364 14.364 ↑ 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.004 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=8) (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. 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.83 rows=2 width=8) (actual time=0.004..0.021 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.003..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.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
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 : 10.699 ms
Execution time : 28,251.186 ms