explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tjr8

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

Group (cost=3,254.86..3,254.86 rows=1 width=92) (actual time=3,566.873..3,566.891 rows=4 loops=1)

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

Sort (cost=3,254.86..3,254.86 rows=1 width=92) (actual time=3,566.870..3,566.883 rows=28 loops=1)

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

Nested Loop (cost=1,704.77..3,254.86 rows=1 width=92) (actual time=1,047.898..3,566.859 rows=28 loops=1)

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

Nested Loop (cost=1,680.46..3,230.54 rows=1 width=96) (actual time=17.925..2,789.476 rows=226,675 loops=1)

5. 118.155 2,352.788 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.37..3,229.10 rows=1 width=96) (actual time=17.919..2,352.788 rows=226,675 loops=1)

6. 60.481 1,781.283 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.29..3,226.30 rows=1 width=96) (actual time=17.910..1,781.283 rows=226,675 loops=1)

7. 98.696 1,040.777 ↓ 226,675.0 226,675 1

Nested Loop (cost=1,680.20..3,223.44 rows=1 width=96) (actual time=17.902..1,040.777 rows=226,675 loops=1)

8. 40.973 488.839 ↓ 75,540.3 226,621 1

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

9. 7.178 188.423 ↓ 4,804.5 9,609 1

Nested Loop (cost=1,680.01..3,210.03 rows=2 width=96) (actual time=17.878..188.423 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. 4.108 152.418 ↓ 3,203.0 9,609 1

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

11. 6.295 109.874 ↓ 436.8 9,609 1

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

12. 2.297 65.143 ↓ 137.3 9,609 1

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

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

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

14. 1.569 21.039 ↓ 24.8 3,596 1

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

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

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

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

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

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

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

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

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

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

20. 0.243 1.831 ↓ 4.2 1,295 1

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

21. 0.025 0.373 ↓ 1.2 135 1

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

22. 0.078 0.078 ↓ 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.078 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.215 1.215 ↑ 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.009 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.061 0.171 ↓ 1.0 194 1

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

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

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.011..0.110 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. 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)
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,075,924.04 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,045,025.58 rows=2,349,846 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. 259.443 259.443 ↓ 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.027 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.949 ms
Execution time : 3,567.141 ms