explain.depesz.com

PostgreSQL's explain analyze made readable

Result: huD1

Settings
# exclusive inclusive rows x rows loops node
1. 1.371 3,609.657 ↓ 417.0 417 1

Sort (cost=1,821.64..1,821.64 rows=1 width=1,019) (actual time=3,609.432..3,609.657 rows=417 loops=1)

  • Sort Key: mr.work_order_id
  • Sort Method: quicksort Memory: 222kB
2.          

CTE maintenance_request_data

3. 374.944 2,706.725 ↓ 51,714.0 51,714 1

Sort (cost=1,761.44..1,761.45 rows=1 width=177) (actual time=2,655.717..2,706.725 rows=51,714 loops=1)

  • Sort Key: mr_1.id
  • Sort Method: quicksort Memory: 17746kB
4. 352.463 2,331.781 ↓ 51,714.0 51,714 1

Nested Loop (cost=82.34..1,761.43 rows=1 width=177) (actual time=32.039..2,331.781 rows=51,714 loops=1)

5. 98.309 623.368 ↓ 27,119.0 54,238 1

Nested Loop (cost=81.91..1,755.75 rows=2 width=76) (actual time=32.014..623.368 rows=54,238 loops=1)

6. 0.114 30.649 ↓ 35.0 35 1

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

7. 30.290 30.290 ↓ 35.0 35 1

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Rows Removed by Filter: 6
8. 0.245 0.245 ↑ 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.007..0.007 rows=1 loops=35)

  • Index Cond: (id = lp.property_id)
  • Filter: (cid = 224)
9. 469.140 494.410 ↓ 6.2 1,550 35

Bitmap Heap Scan on maintenance_requests mr_1 (cost=81.38..1,743.57 rows=250 width=52) (actual time=0.874..14.126 rows=1,550 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))
  • Rows Removed by Filter: 1447
  • Heap Blocks: exact=31436
10. 25.270 25.270 ↑ 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.722..0.722 rows=2,997 loops=35)

  • Index Cond: (property_id = p.id)
11. 1,355.950 1,355.950 ↑ 1.0 1 54,238

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..2.83 rows=1 width=105) (actual time=0.023..0.025 rows=1 loops=54,238)

  • Index Cond: (maintenance_request_id = mr_1.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
12. 11.614 3,608.286 ↓ 417.0 417 1

Nested Loop Left Join (cost=2.69..60.18 rows=1 width=1,019) (actual time=2,656.303..3,608.286 rows=417 loops=1)

13. 1.294 3,589.583 ↓ 417.0 417 1

Nested Loop Left Join (cost=2.41..59.36 rows=1 width=970) (actual time=2,656.051..3,589.583 rows=417 loops=1)

14. 0.956 3,585.787 ↓ 417.0 417 1

Nested Loop Left Join (cost=2.12..51.04 rows=1 width=970) (actual time=2,656.043..3,585.787 rows=417 loops=1)

15. 1.424 3,584.414 ↓ 417.0 417 1

Nested Loop Left Join (cost=1.84..42.72 rows=1 width=964) (actual time=2,656.036..3,584.414 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))
16. 1.222 3,581.322 ↓ 417.0 417 1

Nested Loop Left Join (cost=1.56..34.31 rows=1 width=954) (actual time=2,656.019..3,581.322 rows=417 loops=1)

17. 1.104 3,578.432 ↓ 417.0 417 1

Nested Loop (cost=1.27..29.09 rows=1 width=953) (actual time=2,656.012..3,578.432 rows=417 loops=1)

18. 45.738 3,574.826 ↓ 417.0 417 1

Nested Loop (cost=0.98..20.67 rows=1 width=940) (actual time=2,655.994..3,574.826 rows=417 loops=1)

19. 1.113 3,527.420 ↓ 417.0 417 1

Nested Loop (cost=0.70..10.92 rows=1 width=935) (actual time=2,655.980..3,527.420 rows=417 loops=1)

20. 2.867 3,525.056 ↓ 417.0 417 1

Nested Loop (cost=0.57..10.74 rows=1 width=931) (actual time=2,655.968..3,525.056 rows=417 loops=1)

21. 1.580 3,520.104 ↓ 417.0 417 1

Nested Loop (cost=0.29..9.52 rows=1 width=932) (actual time=2,655.952..3,520.104 rows=417 loops=1)

  • Join Filter: (mr.maintenance_request_type_id = mrt.id)
  • Rows Removed by Join Filter: 395
22. 109.299 3,517.273 ↓ 417.0 417 1

Nested Loop (cost=0.29..8.43 rows=1 width=936) (actual time=2,655.938..3,517.273 rows=417 loops=1)

23. 3,046.235 3,046.235 ↓ 51,677.0 51,677 1

CTE Scan on maintenance_request_data mr (cost=0.00..0.05 rows=1 width=830) (actual time=2,655.728..3,046.235 rows=51,677 loops=1)

  • Filter: ((cid = 224) 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: 37
24. 361.739 361.739 ↓ 0.0 0 51,677

Index Scan using idx_maintenance_request_labors_maintenance_request_id on maintenance_request_labors mrl (cost=0.29..8.37 rows=1 width=110) (actual time=0.007..0.007 rows=0 loops=51,677)

  • Index Cond: (maintenance_request_id = mr.work_order_id)
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-01-09 00:00:00-07'::timestamp with time zone)) AND (cid = 224) AND (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[])))
  • Rows Removed by Filter: 0
25. 1.251 1.251 ↑ 1.5 2 417

Seq Scan on maintenance_request_types mrt (cost=0.00..1.05 rows=3 width=4) (actual time=0.002..0.003 rows=2 loops=417)

  • Filter: (id = ANY ('{3,2,1}'::integer[]))
26. 2.085 2.085 ↑ 1.0 1 417

Index Scan using idx_company_employees_id on company_employees ce (cost=0.29..1.22 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=417)

  • Index Cond: (id = mrl.company_employee_id)
  • Filter: (cid = 224)
27. 1.251 1.251 ↑ 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.003..0.003 rows=1 loops=417)

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

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

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

Index Scan using pk_maintenance_problems on maintenance_problems mp (cost=0.29..8.37 rows=1 width=21) (actual time=0.006..0.006 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.668 1.668 ↑ 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.004..0.004 rows=1 loops=417)

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

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

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.location_id))
32. 0.417 0.417 ↓ 0.0 0 417

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

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

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

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.property_unit_id))
34. 7.089 7.089 ↑ 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.017..0.017 rows=1 loops=417)

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