explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HiUK

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 72,082.092 ↓ 0.0 0 1

Unique (cost=299,036.99..299,036.99 rows=1 width=72) (actual time=72,020.838..72,082.092 rows=0 loops=1)

  • JIT:
  • Functions: 209
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 30.072 ms, Inlining 0.000 ms, Optimization 21.913 ms, Emission 333.251 ms, Total 385.236 ms
2. 0.009 72,082.091 ↓ 0.0 0 1

Sort (cost=299,036.99..299,036.99 rows=1 width=72) (actual time=72,020.838..72,082.091 rows=0 loops=1)

  • Sort Key: cities.id, (concat_ws(', '::text, cities.name, states.abbr)), submarkets.id, submarkets.name, markets.id, markets.name
  • Sort Method: quicksort Memory: 25kB
3. 0.002 72,082.082 ↓ 0.0 0 1

Nested Loop (cost=5,113.32..299,036.99 rows=1 width=72) (actual time=72,020.829..72,082.082 rows=0 loops=1)

4. 1,486.676 72,082.080 ↓ 0.0 0 1

Hash Semi Join (cost=5,113.27..299,035.92 rows=1 width=54) (actual time=72,020.828..72,082.080 rows=0 loops=1)

  • Hash Cond: (space_lease_terms.lease_term_id = "ANY_subquery".deal_term_id)
5. 2,149.479 70,454.304 ↓ 1,702.9 4,902,621 1

Merge Join (cost=1,000.81..294,921.95 rows=2,879 width=62) (actual time=258.553..70,454.304 rows=4,902,621 loops=1)

  • Merge Cond: (properties.submarket_id = submarkets.id)
6. 6,225.937 68,291.704 ↓ 326.0 4,908,761 1

Nested Loop (cost=1,000.60..1,537,262.94 rows=15,056 width=49) (actual time=258.538..68,291.704 rows=4,908,761 loops=1)

7. 23,068.303 52,233.145 ↓ 325.9 4,916,311 1

Gather Merge (cost=1,000.54..1,521,264.00 rows=15,086 width=34) (actual time=258.528..52,233.145 rows=4,916,311 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
8. 920.081 29,164.842 ↓ 277.0 2,458,276 2 / 2

Nested Loop (cost=0.54..1,518,657.34 rows=8,874 width=34) (actual time=181.488..29,164.842 rows=2,458,276 loops=2)

9. 773.303 15,946.454 ↓ 92.8 2,459,662 2 / 2

Nested Loop (cost=0.45..1,467,266.18 rows=26,519 width=30) (actual time=181.451..15,946.454 rows=2,459,662 loops=2)

10. 931.014 7,066.503 ↓ 53.8 2,026,662 2 / 2

Nested Loop (cost=0.37..1,295,653.57 rows=37,664 width=30) (actual time=179.458..7,066.503 rows=2,026,662 loops=2)

11. 199.017 1,601.456 ↓ 6.5 647,719 2 / 2

Nested Loop (cost=0.28..726,887.70 rows=100,162 width=30) (actual time=179.413..1,601.456 rows=647,719 loops=2)

12. 55.592 474.467 ↓ 3.0 103,108 2 / 2

Nested Loop (cost=0.20..414,963.94 rows=34,404 width=30) (actual time=179.363..474.467 rows=103,108 loops=2)

  • Join Filter: (cities.id = city_markets.city_id)
13. 17.607 280.941 ↑ 6.0 11,494 2 / 2

Nested Loop (cost=0.14..307,897.88 rows=68,910 width=30) (actual time=179.332..280.941 rows=11,494 loops=2)

14. 228.850 228.850 ↑ 6.0 11,494 2 / 2

Parallel Index Scan using index_properties_on_submarket_id on properties (cost=0.08..230,160.36 rows=68,910 width=12) (actual time=179.261..228.850 rows=11,494 loops=2)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 618
15. 34.483 34.483 ↑ 1.0 1 22,989 / 2

Index Scan using cities_pkey on cities (cost=0.06..1.13 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=22,989)

  • Index Cond: (id = properties.city_id)
16. 137.934 137.934 ↓ 4.5 9 22,989 / 2

Index Scan using index_city_markets_on_city_id on city_markets (cost=0.06..1.55 rows=2 width=8) (actual time=0.003..0.012 rows=9 loops=22,989)

  • Index Cond: (city_id = properties.city_id)
17. 927.972 927.972 ↑ 8.8 6 206,216 / 2

Index Only Scan using index_floors_on_property_id_and_id_no_deleted on floors (cost=0.08..8.91 rows=53 width=8) (actual time=0.004..0.009 rows=6 loops=206,216)

  • Index Cond: (property_id = properties.id)
  • Heap Fetches: 1,039,753
18. 4,534.033 4,534.033 ↑ 8.0 3 1,295,438 / 2

Index Only Scan using index_spaces_on_floor_id_and_id_no_deleted on spaces (cost=0.09..5.61 rows=24 width=8) (actual time=0.004..0.007 rows=3 loops=1,295,438)

  • Index Cond: (floor_id = floors.id)
  • Heap Fetches: 4,047,691
19. 8,106.648 8,106.648 ↑ 12.0 1 4,053,324 / 2

Index Scan using partial_index_space_lease_terms_on_space_id on space_lease_terms (cost=0.09..4.52 rows=12 width=8) (actual time=0.003..0.004 rows=1 loops=4,053,324)

  • Index Cond: (space_id = spaces.id)
20. 12,298.308 12,298.308 ↑ 1.0 1 4,919,323 / 2

Index Scan using deal_terms_pkey on deal_terms (cost=0.09..1.94 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4,919,323)

  • Index Cond: (id = space_lease_terms.lease_term_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'LeaseTerm'::text))
  • Rows Removed by Filter: 0
21. 9,832.622 9,832.622 ↑ 1.0 1 4,916,311

Index Scan using markets_pkey on markets (cost=0.06..1.06 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=4,916,311)

  • Index Cond: (id = city_markets.market_id)
22. 13.121 13.121 ↓ 1.0 11,223 1

Index Scan using submarkets_pkey on submarkets (cost=0.06..856.69 rows=11,222 width=17) (actual time=0.010..13.121 rows=11,223 loops=1)

23. 0.433 141.100 ↓ 445.6 3,119 1

Hash (cost=4,112.43..4,112.43 rows=7 width=4) (actual time=141.059..141.100 rows=3,119 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 142kB
24. 0.267 140.667 ↓ 445.6 3,119 1

Subquery Scan on ANY_subquery (cost=4,112.41..4,112.43 rows=7 width=4) (actual time=139.639..140.667 rows=3,119 loops=1)

25. 0.505 140.400 ↓ 445.6 3,119 1

Group (cost=4,112.41..4,112.41 rows=7 width=8) (actual time=139.637..140.400 rows=3,119 loops=1)

  • Group Key: rights.id
26. 1.150 139.895 ↓ 445.6 3,119 1

Sort (cost=4,112.41..4,112.41 rows=7 width=8) (actual time=139.624..139.895 rows=3,119 loops=1)

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

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

28. 2.041 119.742 ↓ 384.8 7,311 1

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

29. 4.451 88.453 ↓ 130.6 7,312 1

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

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

Hash Join (cost=2,111.21..3,128.42 rows=80 width=12) (actual time=28.002..70.370 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. 4.182 68.781 ↓ 37.6 4,544 1

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

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

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

33. 4.113 29.801 ↓ 21.8 5,461 1

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

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

Append (cost=0.08..2,102.18 rows=250 width=12) (actual time=0.080..25.688 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 25.130 ↓ 25.5 5,461 1

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

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

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

38. 0.242 3.450 ↓ 2.7 1,076 1

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

39. 0.297 1.633 ↓ 2.3 315 1

Nested Loop (cost=0.17..596.55 rows=136 width=16) (actual time=0.030..1.633 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.015..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.004..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.031 0.117 ↓ 1.0 194 1

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

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

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.011..0.086 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)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using states_pkey on states (cost=0.06..1.06 rows=1 width=10) (never executed)

  • Index Cond: (id = cities.state_id)
Planning time : 12.030 ms
Execution time : 72,101.377 ms