explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4b4d

Settings
# exclusive inclusive rows x rows loops node
1. 20.486 954.794 ↓ 10,339.0 10,339 1

Sort (cost=1,951.81..1,951.82 rows=1 width=472) (actual time=950.021..954.794 rows=10,339 loops=1)

  • Sort Key: mr.id
  • Sort Method: quicksort Memory: 5482kB
2. 130.209 934.308 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=85.39..1,951.80 rows=1 width=472) (actual time=4.629..934.308 rows=10,339 loops=1)

3. 13.927 783.421 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=85.11..1,950.98 rows=1 width=309) (actual time=4.581..783.421 rows=10,339 loops=1)

4. 22.855 738.477 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=84.82..1,944.81 rows=1 width=309) (actual time=4.577..738.477 rows=10,339 loops=1)

5. 24.963 715.622 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=84.54..1,944.08 rows=1 width=303) (actual time=4.574..715.622 rows=10,339 loops=1)

  • Filter: ((ml.id = ANY ('{17254,17537,17446,1476,17366,41195,41194,17444,17255,17365,41196,41197,41198,41199,17443,17252,39987,39988,39989,39990,17256,17463,41193,17257,40040,17251,17258,41202,1305,17250,41200,108,17445,1134,279,792,17280,1989,450,963,1818,41201,17468,40128,40075,40169,40074,17471,17465,17464,17466,17253,17470,40299,17467,17469,39741,1647,17279,39991,17106,621,17282,17283,17284,17285,17286,17287,17288,17289,17290,17291,17292,17293,17294,17295}'::integer[])) OR (mr.maintenance_location_id IS NULL))
6. 15.992 669.981 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=84.26..1,943.27 rows=1 width=289) (actual time=4.570..669.981 rows=10,339 loops=1)

7. 24.324 633.311 ↓ 10,339.0 10,339 1

Nested Loop Left Join (cost=83.97..1,938.05 rows=1 width=288) (actual time=4.566..633.311 rows=10,339 loops=1)

8. 18.201 577.970 ↓ 10,339.0 10,339 1

Nested Loop (cost=83.55..1,911.23 rows=1 width=296) (actual time=4.554..577.970 rows=10,339 loops=1)

9. 17.676 528.749 ↓ 10,340.0 10,340 1

Nested Loop (cost=83.26..1,906.06 rows=1 width=283) (actual time=4.544..528.749 rows=10,340 loops=1)

10. 22.358 490.393 ↓ 10,340.0 10,340 1

Nested Loop (cost=82.98..1,905.59 rows=1 width=278) (actual time=4.536..490.393 rows=10,340 loops=1)

11. 21.974 457.695 ↓ 10,340.0 10,340 1

Nested Loop (cost=82.85..1,905.44 rows=1 width=274) (actual time=4.530..457.695 rows=10,340 loops=1)

12. 19.034 415.041 ↓ 10,340.0 10,340 1

Nested Loop (cost=82.57..1,905.05 rows=1 width=267) (actual time=4.520..415.041 rows=10,340 loops=1)

  • Join Filter: (mr.id = mrd.maintenance_request_id)
13. 55.356 333.943 ↓ 10,344.0 10,344 1

Nested Loop (cost=82.14..1,899.83 rows=1 width=174) (actual time=4.505..333.943 rows=10,344 loops=1)

14. 43.038 170.717 ↓ 53,935.0 53,935 1

Nested Loop (cost=81.85..1,895.75 rows=1 width=64) (actual time=4.138..170.717 rows=53,935 loops=1)

15. 0.078 3.289 ↓ 35.0 35 1

Nested Loop (cost=0.53..9.68 rows=1 width=36) (actual time=3.043..3.289 rows=35 loops=1)

16. 3.071 3.071 ↓ 35.0 35 1

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=4) (actual time=3.033..3.071 rows=35 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Rows Removed by Filter: 6
17. 0.140 0.140 ↑ 1.0 1 35

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=35)

  • Index Cond: (id = lp.property_id)
  • Filter: (cid = 224)
18. 116.970 124.390 ↓ 1,541.0 1,541 35

Bitmap Heap Scan on maintenance_requests mr (cost=81.32..1,886.06 rows=1 width=40) (actual time=0.324..3.554 rows=1,541 loops=35)

  • Recheck Cond: (property_id = p.id)
  • Filter: ((parent_maintenance_request_id IS NULL) AND ((deleted_on IS NULL) OR (deleted_on > '2019-01-09 00:00:00-07'::timestamp with time zone)) AND (cid = 224) AND (maintenance_request_type_id = ANY ('{3,2,1}'::integer[])) AND (maintenance_priority_id = ANY ('{2242,2241,2243,2244,2245,2246}'::integer[])) AND (ps_product_id = ANY ('{7,1,21981,56,14,46,3,34}'::integer[])) AND (maintenance_status_id = ANY ('{2241,2242,2243,114134,2244,2245,18346,18516,114242}'::integer[])))
  • Rows Removed by Filter: 1456
  • Heap Blocks: exact=31436
19. 7.420 7.420 ↑ 1.5 2,997 35

Bitmap Index Scan on idx_maintenance_requests_property_id (cost=0.00..81.32 rows=4,386 width=0) (actual time=0.212..0.212 rows=2,997 loops=35)

  • Index Cond: (property_id = p.id)
20. 107.870 107.870 ↓ 0.0 0 53,935

Index Scan using idx_maintenance_request_labors_maintenance_request_id on maintenance_request_labors mrl (cost=0.29..4.07 rows=1 width=110) (actual time=0.002..0.002 rows=0 loops=53,935)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-01-09 00:00:00-07'::timestamp with time zone)) AND (cid = 224))
  • Rows Removed by Filter: 0
21. 62.064 62.064 ↑ 1.0 1 10,344

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..5.21 rows=1 width=105) (actual time=0.005..0.006 rows=1 loops=10,344)

  • Index Cond: (maintenance_request_id = mrl.maintenance_request_id)
  • Filter: ((cid = 224) AND (date(actual_start_datetime) >= '2013-01-01'::date) AND (date(actual_start_datetime) <= '2019-01-09'::date))
  • Rows Removed by Filter: 0
22. 20.680 20.680 ↑ 1.0 1 10,340

Index Scan using idx_company_employees_id on company_employees ce (cost=0.29..0.39 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=10,340)

  • Index Cond: (id = mrl.company_employee_id)
  • Filter: (cid = 224)
  • Rows Removed by Filter: 0
23. 10.340 10.340 ↑ 1.0 1 10,340

Index Scan using pk_maintenance_request_labor_types on maintenance_request_labor_types mrlt (cost=0.13..0.15 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=10,340)

  • Index Cond: (id = mrl.maintenance_request_labor_type_id)
24. 20.680 20.680 ↑ 1.0 1 10,340

Index Scan using idx_maintenance_statuses_id on maintenance_statuses ms (cost=0.28..0.44 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=10,340)

  • Index Cond: (id = mr.maintenance_status_id)
  • Filter: (cid = 224)
25. 31.020 31.020 ↑ 1.0 1 10,340

Index Scan using pk_maintenance_problems on maintenance_problems mp (cost=0.29..5.14 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=10,340)

  • Index Cond: ((cid = 224) AND (id = mr.maintenance_problem_id))
  • Filter: ((maintenance_problem_id = ANY ('{98715,98718,121422,121360,464175,439218,98766,439222,98811,121373,121366,121363,98816,98826,108,98857,98862,98873,98884,121368,98915,98921,98734,498006,98928,497816,122733,499067,121364,121375,98946,482548,121350,98962,121367,95235,110965,121378,121365,279,99075,99079,132165,122434,99084,497997,99090,116185,121352,99101,99095}'::integer[])) OR (maintenance_problem_id IS NULL))
26. 31.017 31.017 ↑ 1.0 1 10,339

Index Scan using idx_company_users_cid_company_employee_id on company_users cu (cost=0.42..26.81 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10,339)

  • Index Cond: (company_employee_id = ce.id)
  • Filter: ((cid = 224) AND (company_user_type_id = 2) AND (cid = ce.cid))
27. 20.678 20.678 ↑ 1.0 1 10,339

Index Scan using pk_maintenance_problems on maintenance_problems map (cost=0.29..5.08 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=10,339)

  • Index Cond: ((cid = mp.cid) AND (cid = 224) AND (id = mp.maintenance_problem_id))
  • Filter: (maintenance_problem_type_id = 2)
28. 20.678 20.678 ↑ 1.0 1 10,339

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..0.71 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=10,339)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_location_id))
29. 0.000 0.000 ↓ 0.0 0 10,339

Index Scan using pk_assets on assets a (cost=0.28..0.73 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=10,339)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.asset_id))
30. 31.017 31.017 ↑ 1.0 1 10,339

Index Scan using pk_property_units on property_units pu (cost=0.29..6.17 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=10,339)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.property_unit_id))
31. 20.678 20.678 ↑ 1.0 1 10,339

Index Scan using idx_property_buildings_id on property_buildings pb (cost=0.28..0.37 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=10,339)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 224) AND (cid = pu.cid))