explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6k8L

Settings
# exclusive inclusive rows x rows loops node
1. 0.809 47.819 ↓ 417.0 417 1

Sort (cost=1,294.96..1,294.97 rows=1 width=472) (actual time=47.616..47.819 rows=417 loops=1)

  • Sort Key: mr.id
  • Sort Method: quicksort Memory: 222kB
2. 5.405 47.010 ↓ 417.0 417 1

Nested Loop Left Join (cost=364.83..1,294.95 rows=1 width=472) (actual time=3.973..47.010 rows=417 loops=1)

3. 0.636 40.771 ↓ 417.0 417 1

Nested Loop Left Join (cost=364.55..1,294.13 rows=1 width=309) (actual time=3.926..40.771 rows=417 loops=1)

4. 0.915 38.884 ↓ 417.0 417 1

Nested Loop Left Join (cost=364.26..1,288.35 rows=1 width=309) (actual time=3.918..38.884 rows=417 loops=1)

5. 0.948 37.969 ↓ 417.0 417 1

Nested Loop Left Join (cost=363.98..1,287.71 rows=1 width=303) (actual time=3.914..37.969 rows=417 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. 0.713 36.187 ↓ 417.0 417 1

Nested Loop Left Join (cost=363.70..1,286.99 rows=1 width=289) (actual time=3.905..36.187 rows=417 loops=1)

7. 1.002 34.640 ↓ 417.0 417 1

Nested Loop Left Join (cost=363.41..1,281.78 rows=1 width=288) (actual time=3.902..34.640 rows=417 loops=1)

8. 0.690 32.387 ↓ 417.0 417 1

Nested Loop (cost=362.99..1,254.95 rows=1 width=296) (actual time=3.892..32.387 rows=417 loops=1)

9. 0.754 30.446 ↓ 417.0 417 1

Nested Loop (cost=362.70..1,250.32 rows=1 width=283) (actual time=3.882..30.446 rows=417 loops=1)

10. 0.892 28.858 ↓ 417.0 417 1

Nested Loop (cost=362.42..1,249.89 rows=1 width=278) (actual time=3.875..28.858 rows=417 loops=1)

11. 0.741 27.549 ↓ 417.0 417 1

Nested Loop (cost=362.29..1,249.71 rows=1 width=274) (actual time=3.870..27.549 rows=417 loops=1)

  • Join Filter: (mr.id = mrd.maintenance_request_id)
12. 1.022 24.294 ↓ 419.0 419 1

Nested Loop (cost=361.86..1,241.80 rows=1 width=181) (actual time=3.744..24.294 rows=419 loops=1)

13. 5.994 22.434 ↓ 419.0 419 1

Nested Loop (cost=361.73..1,241.17 rows=1 width=185) (actual time=3.732..22.434 rows=419 loops=1)

  • Join Filter: (p.id = lp.property_id)
  • Rows Removed by Join Filter: 14246
14. 0.802 6.384 ↓ 419.0 419 1

Nested Loop (cost=361.48..1,240.16 rows=1 width=189) (actual time=0.647..6.384 rows=419 loops=1)

15. 0.864 4.744 ↓ 419.0 419 1

Nested Loop (cost=361.21..1,239.42 rows=1 width=161) (actual time=0.640..4.744 rows=419 loops=1)

16. 0.525 1.780 ↓ 14.0 420 1

Hash Join (cost=360.78..1,003.32 rows=30 width=121) (actual time=0.623..1.780 rows=420 loops=1)

  • Hash Cond: (mrl.company_employee_id = ce.id)
17. 0.658 0.745 ↑ 2.2 420 1

Bitmap Heap Scan on maintenance_request_labors mrl (cost=164.64..804.74 rows=931 width=110) (actual time=0.106..0.745 rows=420 loops=1)

  • Recheck Cond: (company_employee_id = ANY ('{11196,204474,335400,325667,444283,325673,203706,194662,329592,444184,444274,444345,11195,325543,326422,325440,194671,197302,306439,196285,334944,325287,444355,194663,325538,13781,196790,326481,324982,444356,326446,444164,444248,326448,197828,195882,306436,11197,325470,306437,196684,444183,194268,444381,13948,325544}'::integer[]))
  • 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: 1
  • Heap Blocks: exact=135
18. 0.087 0.087 ↑ 4.8 421 1

Bitmap Index Scan on idx_maintenance_request_labors_company_employee_id (cost=0.00..164.41 rows=2,029 width=0) (actual time=0.086..0.087 rows=421 loops=1)

  • Index Cond: (company_employee_id = ANY ('{11196,204474,335400,325667,444283,325673,203706,194662,329592,444184,444274,444345,11195,325543,326422,325440,194671,197302,306439,196285,334944,325287,444355,194663,325538,13781,196790,326481,324982,444356,326446,444164,444248,326448,197828,195882,306436,11197,325470,306437,196684,444183,194268,444381,13948,325544}'::integer[]))
19. 0.196 0.510 ↑ 1.0 362 1

Hash (cost=191.62..191.62 rows=362 width=15) (actual time=0.510..0.510 rows=362 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
20. 0.292 0.314 ↑ 1.0 362 1

Bitmap Heap Scan on company_employees ce (cost=11.09..191.62 rows=362 width=15) (actual time=0.032..0.314 rows=362 loops=1)

  • Recheck Cond: (cid = 224)
  • Heap Blocks: exact=62
21. 0.022 0.022 ↑ 1.0 362 1

Bitmap Index Scan on idx_company_employees_cid (cost=0.00..11.00 rows=362 width=0) (actual time=0.022..0.022 rows=362 loops=1)

  • Index Cond: (cid = 224)
22. 2.100 2.100 ↑ 1.0 1 420

Index Scan using pk_maintenance_requests on maintenance_requests mr (cost=0.43..7.87 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=420)

  • Index Cond: ((cid = 224) AND (id = mrl.maintenance_request_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 (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: 0
23. 0.838 0.838 ↑ 1.0 1 419

Index Scan using idx_properties_id on properties p (cost=0.28..0.72 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=419)

  • Index Cond: (id = mr.property_id)
  • Filter: (cid = 224)
24. 10.056 10.056 ↓ 35.0 35 419

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Rows Removed by Filter: 6
25. 0.838 0.838 ↑ 1.0 1 419

Index Only Scan using pk_maintenance_request_types on maintenance_request_types mrt (cost=0.13..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=419)

  • Index Cond: ((id = mr.maintenance_request_type_id) AND (id = ANY ('{3,2,1}'::integer[])))
  • Heap Fetches: 419
26. 2.514 2.514 ↑ 1.0 1 419

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..7.90 rows=1 width=105) (actual time=0.005..0.006 rows=1 loops=419)

  • 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
27. 0.417 0.417 ↑ 1.0 1 417

Index Scan using pk_maintenance_request_labor_types on maintenance_request_labor_types mrlt (cost=0.13..0.16 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=417)

  • Index Cond: (id = mrl.maintenance_request_labor_type_id)
28. 0.834 0.834 ↑ 1.0 1 417

Index Scan using idx_maintenance_statuses_id on maintenance_statuses ms (cost=0.28..0.41 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=417)

  • Index Cond: (id = mr.maintenance_status_id)
  • Filter: (cid = 224)
29. 1.251 1.251 ↑ 1.0 1 417

Index Scan using pk_maintenance_problems on maintenance_problems mp (cost=0.29..4.61 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=417)

  • 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))
30. 1.251 1.251 ↑ 1.0 1 417

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=417)

  • Index Cond: (company_employee_id = ce.id)
  • Filter: ((cid = 224) AND (company_user_type_id = 2) AND (cid = ce.cid))
31. 0.834 0.834 ↑ 1.0 1 417

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=417)

  • Index Cond: ((cid = mp.cid) AND (cid = 224) AND (id = mp.maintenance_problem_id))
  • Filter: (maintenance_problem_type_id = 2)
32. 0.834 0.834 ↑ 1.0 1 417

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..0.61 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=417)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_location_id))
33. 0.000 0.000 ↓ 0.0 0 417

Index Scan using pk_assets on assets a (cost=0.28..0.63 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=417)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.asset_id))
34. 1.251 1.251 ↑ 1.0 1 417

Index Scan using pk_property_units on property_units pu (cost=0.29..5.78 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=417)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.property_unit_id))
35. 0.834 0.834 ↑ 1.0 1 417

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=417)

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