explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xlay

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 720.725 ↓ 8.0 8 1

Unique (cost=9,330.79..9,330.80 rows=1 width=69) (actual time=720.668..720.725 rows=8 loops=1)

2. 0.170 720.690 ↓ 120.0 120 1

Sort (cost=9,330.79..9,330.79 rows=1 width=69) (actual time=720.667..720.690 rows=120 loops=1)

  • Sort Key: property_meta_asset_managers.name, property_meta_clients.name, property_meta_funds.name, property_meta_jv_partners.name, property_meta_data.region
  • Sort Method: quicksort Memory: 41kB
3. 79.434 720.520 ↓ 120.0 120 1

Nested Loop Semi Join (cost=5,406.95..9,330.79 rows=1 width=69) (actual time=164.732..720.520 rows=120 loops=1)

  • Join Filter: (space_lease_terms.lease_term_id = rights.deal_term_id)
  • Rows Removed by Join Filter: 1,480,120
4. 6.575 188.932 ↓ 537.0 537 1

Nested Loop (cost=1,294.54..5,218.33 rows=1 width=77) (actual time=27.498..188.932 rows=537 loops=1)

  • Join Filter: (property_meta_asset_managers.property_meta_datum_id = property_meta_jv_partners.property_meta_datum_id)
5. 4.649 162.953 ↓ 9,702.0 9,702 1

Nested Loop (cost=1,294.46..5,215.47 rows=1 width=72) (actual time=15.459..162.953 rows=9,702 loops=1)

6. 2.947 138.900 ↓ 9,702.0 9,702 1

Nested Loop (cost=1,294.40..5,214.31 rows=1 width=55) (actual time=15.436..138.900 rows=9,702 loops=1)

7. 6.376 97.141 ↓ 2,425.8 9,703 1

Nested Loop (cost=1,294.31..5,206.53 rows=4 width=51) (actual time=15.419..97.141 rows=9,703 loops=1)

8. 1.667 71.940 ↓ 1,255.0 6,275 1

Nested Loop (cost=1,294.23..5,183.46 rows=5 width=51) (actual time=15.399..71.940 rows=6,275 loops=1)

9. 0.612 47.133 ↓ 136.9 1,780 1

Nested Loop (cost=1,294.14..5,108.93 rows=13 width=51) (actual time=15.364..47.133 rows=1,780 loops=1)

10. 0.978 40.503 ↓ 200.6 1,003 1

Nested Loop (cost=1,294.06..5,063.05 rows=5 width=55) (actual time=15.350..40.503 rows=1,003 loops=1)

11. 0.894 32.504 ↓ 200.6 1,003 1

Nested Loop (cost=1,293.97..5,054.33 rows=5 width=51) (actual time=15.335..32.504 rows=1,003 loops=1)

  • Join Filter: (property_meta_data.id = property_meta_asset_managers.property_meta_datum_id)
12. 1.650 25.472 ↓ 15.0 1,023 1

Hash Join (cost=1,293.89..4,862.83 rows=68 width=34) (actual time=15.107..25.472 rows=1,023 loops=1)

  • Hash Cond: (property_meta_data.id = property_meta_clients.property_meta_datum_id)
13. 8.950 9.405 ↓ 1.2 4,089 1

Bitmap Heap Scan on property_meta_data (cost=33.16..3,581.43 rows=3,274 width=17) (actual time=0.575..9.405 rows=4,089 loops=1)

  • Recheck Cond: ((employer_id = 1,996) AND (deleted_at IS NULL))
  • Heap Blocks: exact=965
14. 0.455 0.455 ↓ 1.2 4,089 1

Bitmap Index Scan on index_property_meta_data_composite (cost=0.00..33.00 rows=3,274 width=0) (actual time=0.455..0.455 rows=4,089 loops=1)

  • Index Cond: (employer_id = 1,996)
15. 1.105 14.417 ↑ 1.0 6,825 1

Hash (cost=1,235.88..1,235.88 rows=7,100 width=17) (actual time=14.416..14.417 rows=6,825 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 399kB
16. 13.312 13.312 ↑ 1.0 6,825 1

Seq Scan on property_meta_clients (cost=0.00..1,235.88 rows=7,100 width=17) (actual time=0.013..13.312 rows=6,825 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 78,135
17. 6.138 6.138 ↑ 1.0 1 1,023

Index Scan using index_property_meta_asset_managers_on_property_meta_datum_id on property_meta_asset_managers (cost=0.08..2.81 rows=1 width=17) (actual time=0.004..0.006 rows=1 loops=1,023)

  • Index Cond: (property_meta_datum_id = property_meta_clients.property_meta_datum_id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1
18. 7.021 7.021 ↑ 1.0 1 1,003

Index Only Scan using index_properties_on_id_no_deleted on properties (cost=0.08..1.74 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,003)

  • Index Cond: (id = property_meta_data.property_id)
  • Heap Fetches: 1,003
19. 6.018 6.018 ↑ 26.5 2 1,003

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors (cost=0.08..9.02 rows=53 width=8) (actual time=0.005..0.006 rows=2 loops=1,003)

  • Index Cond: (property_id = properties.id)
  • Heap Fetches: 1,773
20. 23.140 23.140 ↑ 6.0 4 1,780

Index Only Scan using index_spaces_on_floor_id_and_id_no_deleted on spaces (cost=0.09..5.66 rows=24 width=8) (actual time=0.006..0.013 rows=4 loops=1,780)

  • Index Cond: (floor_id = floors.id)
  • Heap Fetches: 6,275
21. 18.825 18.825 ↑ 6.0 2 6,275

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms (cost=0.09..4.58 rows=12 width=8) (actual time=0.003..0.003 rows=2 loops=6,275)

  • Index Cond: (space_id = spaces.id)
22. 38.812 38.812 ↑ 1.0 1 9,703

Index Scan using deal_terms_pkey on deal_terms (cost=0.09..1.94 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=9,703)

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
  • Rows Removed by Filter: 0
23. 19.404 19.404 ↑ 1.0 1 9,702

Index Scan using index_property_meta_funds_on_property_meta_datum_id on property_meta_funds (cost=0.06..1.16 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=9,702)

  • Index Cond: (property_meta_datum_id = property_meta_asset_managers.property_meta_datum_id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1
24. 19.404 19.404 ↓ 0.0 0 9,702

Index Scan using index_property_meta_jv_partners_on_property_meta_datum_id on property_meta_jv_partners (cost=0.08..2.85 rows=1 width=25) (actual time=0.002..0.002 rows=0 loops=9,702)

  • Index Cond: (property_meta_datum_id = property_meta_funds.property_meta_datum_id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2
25. 235.743 452.154 ↓ 393.7 2,756 537

Group (cost=4,112.41..4,112.41 rows=7 width=8) (actual time=0.253..0.842 rows=2,756 loops=537)

  • Group Key: rights.id
26. 81.702 216.411 ↓ 393.7 2,756 537

Sort (cost=4,112.41..4,112.41 rows=7 width=8) (actual time=0.253..0.403 rows=2,756 loops=537)

  • Sort Key: rights.id
  • Sort Method: quicksort Memory: 243kB
27. 4.005 134.709 ↓ 445.6 3,119 1

Nested Loop (cost=2,111.47..4,112.39 rows=7 width=8) (actual time=27.825..134.709 rows=3,119 loops=1)

28. 0.305 116.082 ↓ 384.8 7,311 1

Nested Loop (cost=2,111.38..3,758.12 rows=19 width=8) (actual time=27.731..116.082 rows=7,311 loops=1)

29. 4.088 86.529 ↓ 130.6 7,312 1

Nested Loop (cost=2,111.30..3,638.19 rows=56 width=4) (actual time=27.715..86.529 rows=7,312 loops=1)

  • Join Filter: (user_spaces.space_id = space_lease_terms_1.space_id)
30. 1.456 68.809 ↓ 56.8 4,544 1

Hash Join (cost=2,111.21..3,128.42 rows=80 width=12) (actual time=27.684..68.809 rows=4,544 loops=1)

  • Hash Cond: (user_spaces.asset_role_id = asset_roles.id)
  • Join Filter: (asset_roles.can_view_tenants OR ((spaces_2.availability_type)::text <> 'not available'::text) OR (NOT (alternatives: SubPlan 1 or hashed SubPlan 2)))
31. 3.166 67.213 ↓ 37.6 4,544 1

Nested Loop (cost=2,102.73..3,119.88 rows=121 width=28) (actual time=27.532..67.213 rows=4,544 loops=1)

  • Join Filter: (user_spaces.space_id = spaces_2.id)
32. 4.726 50.415 ↓ 26.1 4,544 1

Nested Loop (cost=2,102.64..2,788.56 rows=174 width=12) (actual time=27.518..50.415 rows=4,544 loops=1)

33. 4.339 29.306 ↓ 21.8 5,461 1

HashAggregate (cost=2,102.56..2,103.31 rows=250 width=12) (actual time=27.499..29.306 rows=5,461 loops=1)

  • Group Key: user_spaces.user_id, user_spaces.space_id, user_spaces.asset_role_id
34. 0.483 24.967 ↓ 21.8 5,461 1

Append (cost=0.08..2,102.18 rows=250 width=12) (actual time=0.075..24.967 rows=5,461 loops=1)

35. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_user_spaces_on_user_id on user_spaces (cost=0.08..68.90 rows=36 width=12) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (user_id = 30,921)
36. 0.000 24.476 ↓ 25.5 5,461 1

Nested Loop Anti Join (cost=0.42..2,032.16 rows=214 width=12) (actual time=0.066..24.476 rows=5,461 loops=1)

  • Join Filter: (user_spaces_1.user_id = user_properties.user_id)
37. 0.571 14.667 ↓ 25.5 5,461 1

Nested Loop (cost=0.34..2,012.18 rows=214 width=12) (actual time=0.056..14.667 rows=5,461 loops=1)

38. 0.189 3.336 ↓ 2.7 1,076 1

Nested Loop (cost=0.25..706.20 rows=396 width=12) (actual time=0.041..3.336 rows=1,076 loops=1)

39. 0.236 1.572 ↓ 2.3 315 1

Nested Loop (cost=0.17..596.55 rows=136 width=16) (actual time=0.028..1.572 rows=315 loops=1)

40. 0.391 0.391 ↓ 2.1 315 1

Index Scan using index_user_properties_on_user_id on user_properties (cost=0.09..279.40 rows=148 width=12) (actual time=0.016..0.391 rows=315 loops=1)

  • Index Cond: (user_id = 30,921)
41. 0.945 0.945 ↑ 1.0 1 315

Index Only Scan using index_properties_on_id_no_deleted on properties properties_1 (cost=0.08..2.14 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=315)

  • Index Cond: (id = user_properties.property_id)
  • Heap Fetches: 315
42. 1.575 1.575 ↑ 17.7 3 315

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors floors_1 (cost=0.08..0.65 rows=53 width=8) (actual time=0.003..0.005 rows=3 loops=315)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,071
43. 10.760 10.760 ↑ 7.0 5 1,076

Index Scan using index_spaces_on_floor_id on spaces spaces_3 (cost=0.09..3.19 rows=35 width=8) (actual time=0.003..0.010 rows=5 loops=1,076)

  • Index Cond: (floor_id = floors_1.id)
  • Filter: (NOT team_override)
  • Rows Removed by Filter: 0
44. 10.922 10.922 ↓ 0.0 0 5,461

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=5,461)

  • Index Cond: ((space_id = spaces_3.id) AND (user_id = 30,921))
  • Heap Fetches: 0
45. 16.383 16.383 ↑ 1.0 1 5,461

Index Only Scan using index_spaces_on_id_no_deleted on spaces spaces_1 (cost=0.09..2.74 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5,461)

  • Index Cond: (id = user_spaces.space_id)
  • Heap Fetches: 4,544
46. 13.632 13.632 ↑ 1.0 1 4,544

Index Scan using spaces_pkey on spaces spaces_2 (cost=0.09..1.90 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,544)

  • Index Cond: (id = spaces_1.id)
  • Filter: (deleted_at IS NULL)
47. 0.042 0.140 ↓ 1.0 194 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
48. 0.098 0.098 ↓ 1.0 194 1

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

  • Filter: can_view_tenants
  • Rows Removed by Filter: 76
49.          

SubPlan (for Hash Join)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.17..101.78 rows=6 width=0) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using index_space_lease_terms_on_space_id on space_lease_terms space_lease_terms_2 (cost=0.09..24.04 rows=19 width=4) (never executed)

  • Index Cond: (space_id = spaces_2.id)
52. 0.000 0.000 ↓ 0.0 0

Index Scan using deal_terms_pkey on deal_terms deal_terms_2 (cost=0.09..4.09 rows=1 width=4) (never executed)

  • Index Cond: (id = space_lease_terms_2.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
53. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,063,796.26..1,113,827.35 rows=682,790 width=4) (never executed)

  • Hash Cond: (space_lease_terms_3.lease_term_id = deal_terms_3.id)
54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using unique_index_space_lease_terms_on_space_id_and_lease_term_id on space_lease_terms space_lease_terms_3 (cost=0.09..23,318.93 rows=2,040,459 width=8) (never executed)

  • Heap Fetches: 0
55. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,045,407.72..1,045,407.72 rows=2,482,703 width=4) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on deal_terms deal_terms_3 (cost=0.00..1,045,407.72 rows=2,482,703 width=4) (never executed)

  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
57. 13.632 13.632 ↑ 6.0 2 4,544

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms space_lease_terms_1 (cost=0.09..6.33 rows=12 width=8) (actual time=0.003..0.003 rows=2 loops=4,544)

  • Index Cond: (space_id = spaces_2.id)
58. 29.248 29.248 ↑ 1.0 1 7,312

Index Scan using deal_terms_pkey on deal_terms deal_terms_1 (cost=0.09..2.14 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=7,312)

  • Index Cond: (id = space_lease_terms_1.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
  • Rows Removed by Filter: 0
59. 14.622 14.622 ↓ 0.0 0 7,311

Index Scan using index_rights_on_deal_term_id on rights (cost=0.09..18.56 rows=28 width=8) (actual time=0.002..0.002 rows=0 loops=7,311)

  • Index Cond: (deal_term_id = deal_terms_1.id)
Planning time : 14.139 ms
Execution time : 721.009 ms