explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M8cn

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 73,203.451 ↓ 0.0 0 1

Unique (cost=299,036.99..299,036.99 rows=1 width=72) (actual time=73,139.162..73,203.451 rows=0 loops=1)

  • JIT:
  • Functions: 209
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 33.664 ms, Inlining 0.000 ms, Optimization 22.249 ms, Emission 341.441 ms, Total 397.353 ms
2. 0.015 73,203.450 ↓ 0.0 0 1

Sort (cost=299,036.99..299,036.99 rows=1 width=72) (actual time=73,139.161..73,203.450 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 73,203.435 ↓ 0.0 0 1

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

4. 1,456.683 73,203.433 ↓ 0.0 0 1

Hash Semi Join (cost=5,113.27..299,035.92 rows=1 width=54) (actual time=73,139.146..73,203.433 rows=0 loops=1)

  • Hash Cond: (space_lease_terms.lease_term_id = "ANY_subquery".deal_term_id)
5. 2,255.646 71,578.942 ↓ 1,702.9 4,902,621 1

Merge Join (cost=1,000.81..294,921.95 rows=2,879 width=62) (actual time=263.450..71,578.942 rows=4,902,621 loops=1)

  • Merge Cond: (properties.submarket_id = submarkets.id)
6. 6,071.581 69,309.952 ↓ 326.0 4,908,761 1

Nested Loop (cost=1,000.60..1,537,262.94 rows=15,056 width=49) (actual time=263.434..69,309.952 rows=4,908,761 loops=1)

7. 23,828.960 53,405.749 ↓ 325.9 4,916,311 1

Gather Merge (cost=1,000.54..1,521,264.00 rows=15,086 width=34) (actual time=263.419..53,405.749 rows=4,916,311 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
8. 1,160.692 29,576.789 ↓ 277.0 2,458,310 2 / 2

Nested Loop (cost=0.54..1,518,657.34 rows=8,874 width=34) (actual time=185.773..29,576.789 rows=2,458,310 loops=2)

9. 732.104 16,117.622 ↓ 92.8 2,459,695 2 / 2

Nested Loop (cost=0.45..1,467,266.18 rows=26,519 width=30) (actual time=185.701..16,117.622 rows=2,459,695 loops=2)

10. 376.164 7,278.520 ↓ 53.8 2,026,750 2 / 2

Nested Loop (cost=0.37..1,295,653.57 rows=37,664 width=30) (actual time=183.051..7,278.520 rows=2,026,750 loops=2)

11. 141.846 1,720.204 ↓ 6.5 647,769 2 / 2

Nested Loop (cost=0.28..726,887.70 rows=100,162 width=30) (actual time=182.987..1,720.204 rows=647,769 loops=2)

12. 53.272 544.463 ↓ 3.0 103,390 2 / 2

Nested Loop (cost=0.20..414,963.94 rows=34,404 width=30) (actual time=182.912..544.463 rows=103,390 loops=2)

  • Join Filter: (cities.id = city_markets.city_id)
13. 18.655 353.053 ↑ 6.0 11,512 2 / 2

Nested Loop (cost=0.14..307,897.88 rows=68,910 width=30) (actual time=182.879..353.053 rows=11,512 loops=2)

14. 299.863 299.863 ↑ 6.0 11,512 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=182.803..299.863 rows=11,512 loops=2)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 618
15. 34.535 34.535 ↑ 1.0 1 23,023 / 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=23,023)

  • Index Cond: (id = properties.city_id)
16. 138.138 138.138 ↓ 4.5 9 23,023 / 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=23,023)

  • Index Cond: (city_id = properties.city_id)
17. 1,033.895 1,033.895 ↑ 8.8 6 206,779 / 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.010 rows=6 loops=206,779)

  • Index Cond: (property_id = properties.id)
  • Heap Fetches: 1,039,853
18. 5,182.152 5,182.152 ↑ 8.0 3 1,295,538 / 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.008 rows=3 loops=1,295,538)

  • Index Cond: (floor_id = floors.id)
  • Heap Fetches: 4,047,866
19. 8,106.998 8,106.998 ↑ 12.0 1 4,053,499 / 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,499)

  • Index Cond: (space_id = spaces.id)
20. 12,298.475 12,298.475 ↑ 1.0 1 4,919,390 / 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,390)

  • 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.344 13.344 ↓ 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.344 rows=11,223 loops=1)

23. 0.432 167.808 ↓ 445.6 3,119 1

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

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

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

25. 0.509 167.112 ↓ 445.6 3,119 1

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

  • Group Key: rights.id
26. 1.171 166.603 ↓ 445.6 3,119 1

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

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

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

28. 2.887 144.519 ↓ 384.8 7,311 1

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

29. 3.649 105.072 ↓ 130.6 7,312 1

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

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

Hash Join (cost=2,111.21..3,128.42 rows=80 width=12) (actual time=37.563..83.247 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. 0.944 81.562 ↓ 37.6 4,544 1

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

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

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

33. 4.484 39.422 ↓ 21.8 5,461 1

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

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

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

35. 0.007 0.007 ↓ 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.007..0.007 rows=0 loops=1)

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

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

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

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

38. 0.169 4.518 ↓ 2.7 1,076 1

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

39. 0.152 2.459 ↓ 2.3 315 1

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

40. 0.732 0.732 ↓ 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.014..0.732 rows=315 loops=1)

  • Index Cond: (user_id = 30,921)
41. 1.575 1.575 ↑ 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.005..0.005 rows=1 loops=315)

  • Index Cond: (id = user_properties.property_id)
  • Heap Fetches: 315
42. 1.890 1.890 ↑ 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.006 rows=3 loops=315)

  • Index Cond: (property_id = properties_1.id)
  • Heap Fetches: 1,071
43. 18.292 18.292 ↑ 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.005..0.017 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. 21.844 21.844 ↑ 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.004..0.004 rows=1 loops=5,461)

  • Index Cond: (id = user_spaces.space_id)
  • Heap Fetches: 4,544
46. 18.176 18.176 ↑ 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.004..0.004 rows=1 loops=4,544)

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

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

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

Seq Scan on asset_roles (cost=0.00..7.81 rows=192 width=5) (actual time=0.011..0.085 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. 18.176 18.176 ↑ 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.004 rows=2 loops=4,544)

  • Index Cond: (space_id = spaces_2.id)
58. 36.560 36.560 ↑ 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.005..0.005 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. 21.933 21.933 ↓ 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.003 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 : 13.399 ms
Execution time : 73,237.705 ms