explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PF5Z

Settings
# exclusive inclusive rows x rows loops node
1. 6.279 1,105.309 ↓ 1,108.0 1,108 1

Sort (cost=10,814.33..10,814.33 rows=1 width=1,268) (actual time=1,105.250..1,105.309 rows=1,108 loops=1)

  • Sort Key: p.property_name, (CASE WHEN (mr.property_building_id IS NOT NULL) THEN mr.property_building_id ELSE NULL::integer END), (CASE WHEN ((COALESCE(mrd.unit_number, ''::character varying))::text <> ''::text) THEN mrd.unit_number ELSE NULL::character varying END), pu.unit_number, us.unit_number_cache
  • Sort Method: quicksort Memory: 685kB
2. 138.196 1,099.030 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,422.82..10,814.32 rows=1 width=1,268) (actual time=40.136..1,099.030 rows=1,108 loops=1)

3. 0.449 958.618 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,422.53..10,806.27 rows=1 width=4,851) (actual time=39.821..958.618 rows=1,108 loops=1)

4. 1.213 954.845 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,414.21..10,797.93 rows=1 width=4,843) (actual time=39.798..954.845 rows=1,108 loops=1)

5. 1.381 953.632 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,413.92..10,794.17 rows=1 width=4,828) (actual time=39.793..953.632 rows=1,108 loops=1)

6. 0.828 945.603 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,404.13..10,784.35 rows=1 width=4,599) (actual time=39.769..945.603 rows=1,108 loops=1)

7. 1.533 941.451 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,403.71..10,776.54 rows=1 width=4,370) (actual time=39.747..941.451 rows=1,108 loops=1)

8. 1.133 937.702 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,403.43..10,775.94 rows=1 width=4,175) (actual time=39.733..937.702 rows=1,108 loops=1)

9. 1.120 935.461 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,403.30..10,775.78 rows=1 width=4,061) (actual time=39.713..935.461 rows=1,108 loops=1)

10. 1.499 929.909 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,403.00..10,770.19 rows=1 width=4,048) (actual time=39.692..929.909 rows=1,108 loops=1)

11. 1.382 926.194 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,402.72..10,769.44 rows=1 width=3,854) (actual time=39.670..926.194 rows=1,108 loops=1)

12. 1.204 921.488 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,402.43..10,764.05 rows=1 width=3,656) (actual time=39.647..921.488 rows=1,108 loops=1)

13. 2.754 918.068 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,402.15..10,763.23 rows=1 width=3,630) (actual time=39.635..918.068 rows=1,108 loops=1)

  • Join Filter: ((work_order_labors_cost.cid = mr.cid) AND (work_order_labors_cost.maintenance_request_id = mr.id))
  • Rows Removed by Join Filter: 12178
14. 101.295 914.206 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,402.15..10,761.92 rows=1 width=3,598) (actual time=39.628..914.206 rows=1,108 loops=1)

  • Join Filter: ((work_order_materials_cost.cid = mr.cid) AND (work_order_materials_cost.maintenance_request_id = mr.id))
  • Rows Removed by Join Filter: 1731804
15. 0.467 648.927 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,402.15..10,709.94 rows=1 width=3,566) (actual time=39.373..648.927 rows=1,108 loops=1)

16. 1.087 646.244 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,401.73..10,676.66 rows=1 width=3,553) (actual time=39.368..646.244 rows=1,108 loops=1)

17. 0.754 644.049 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,401.45..10,675.69 rows=1 width=3,536) (actual time=39.363..644.049 rows=1,108 loops=1)

18. 0.791 641.079 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,401.17..10,675.16 rows=1 width=2,870) (actual time=39.347..641.079 rows=1,108 loops=1)

19. 1.112 638.072 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,400.90..10,674.63 rows=1 width=2,377) (actual time=39.315..638.072 rows=1,108 loops=1)

20. 1.570 634.744 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,400.62..10,673.15 rows=1 width=1,671) (actual time=39.292..634.744 rows=1,108 loops=1)

  • Join Filter: (mp2.cid = mr.cid)
21. 0.983 630.958 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,400.20..10,669.67 rows=1 width=1,449) (actual time=39.278..630.958 rows=1,108 loops=1)

22. 1.201 629.975 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,399.78..10,663.46 rows=1 width=1,227) (actual time=39.274..629.975 rows=1,108 loops=1)

23. 1.481 625.450 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,399.36..10,657.26 rows=1 width=1,001) (actual time=39.250..625.450 rows=1,108 loops=1)

  • Join Filter: (c.primary_phone_number_type_id IS NULL)
  • Rows Removed by Join Filter: 220
24. 1.927 621.753 ↓ 1,108.0 1,108 1

Nested Loop Left Join (cost=7,399.07..10,653.39 rows=1 width=990) (actual time=39.227..621.753 rows=1,108 loops=1)

25. 35.320 614.286 ↓ 1,108.0 1,108 1

Nested Loop (cost=7,398.65..10,645.48 rows=1 width=918) (actual time=39.197..614.286 rows=1,108 loops=1)

26. 31.632 216.962 ↓ 2,661.8 90,501 1

Hash Join (cost=7,398.22..10,367.61 rows=34 width=222) (actual time=28.248..216.962 rows=90,501 loops=1)

  • Hash Cond: (mr.maintenance_request_type_id = mrt.id)
  • Join Filter: CASE WHEN (mr.parent_maintenance_request_id IS NOT NULL) THEN (mrt.id = ANY ('{2,3,1}'::integer[])) ELSE (mrt.id = ANY ('{3,2,1}'::integer[])) END
27. 26.295 185.310 ↓ 1,350.8 90,501 1

Hash Join (cost=7,397.11..10,366.19 rows=67 width=104) (actual time=28.210..185.310 rows=90,501 loops=1)

  • Hash Cond: (mr.property_id = lp.property_id)
28. 133.360 158.997 ↓ 134.1 90,648 1

Bitmap Heap Scan on maintenance_requests mr (cost=7,395.32..10,361.20 rows=676 width=88) (actual time=28.173..158.997 rows=90,648 loops=1)

  • Recheck Cond: ((maintenance_priority_id = ANY ('{2242,2241,2243,2244,2245,2246}'::integer[])) AND (maintenance_status_id = ANY ('{2241,2242,2243,114134,2244,2245,18346,18516,114242}'::integer[])) AND (cid = 224))
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2020-04-30 00:00:00+00'::timestamp with time zone)) AND ((created_by = 18) OR (ps_product_id = ANY ('{7,36,1,21981,20612,56,14,46,31693,3,34}'::integer[]))))
  • Rows Removed by Filter: 39674
  • Heap Blocks: exact=16764
29. 1.051 25.637 ↓ 0.0 0 1

BitmapAnd (cost=7,395.32..7,395.32 rows=854 width=0) (actual time=25.637..25.637 rows=0 loops=1)

30. 8.720 8.720 ↑ 1.0 130,322 1

Bitmap Index Scan on idx_maintenance_requests_maintenance_priority_id (cost=0.00..2,439.82 rows=134,034 width=0) (actual time=8.720..8.720 rows=130,322 loops=1)

  • Index Cond: (maintenance_priority_id = ANY ('{2242,2241,2243,2244,2245,2246}'::integer[]))
31. 7.727 7.727 ↑ 1.0 130,322 1

Bitmap Index Scan on idx_maintenance_requests_maintenance_status_id (cost=0.00..2,471.94 rows=134,943 width=0) (actual time=7.727..7.727 rows=130,322 loops=1)

  • Index Cond: (maintenance_status_id = ANY ('{2241,2242,2243,114134,2244,2245,18346,18516,114242}'::integer[]))
32. 8.139 8.139 ↑ 1.0 130,322 1

Bitmap Index Scan on pk_maintenance_requests (cost=0.00..2,482.55 rows=134,150 width=0) (actual time=8.139..8.139 rows=130,322 loops=1)

  • Index Cond: (cid = 224)
33. 0.009 0.018 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=20) (actual time=0.018..0.018 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.009 0.009 ↑ 1.0 35 1

Seq Scan on lp (cost=0.00..1.35 rows=35 width=20) (actual time=0.004..0.009 rows=35 loops=1)

35. 0.005 0.020 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=122) (actual time=0.020..0.020 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.015 0.015 ↑ 1.0 5 1

Seq Scan on maintenance_request_types mrt (cost=0.00..1.05 rows=5 width=122) (actual time=0.014..0.015 rows=5 loops=1)

37. 362.004 362.004 ↓ 0.0 0 90,501

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..8.16 rows=1 width=704) (actual time=0.004..0.004 rows=0 loops=90,501)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: ((actual_start_datetime >= '2020-04-01 00:00:00+00'::timestamp with time zone) AND (actual_start_datetime < '2020-05-01 00:00:00'::timestamp without time zone) AND (cid = 224))
  • Rows Removed by Filter: 1
38. 5.540 5.540 ↑ 1.0 1 1,108

Index Scan using pk_customers on customers c (cost=0.42..7.91 rows=1 width=80) (actual time=0.005..0.005 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.customer_id))
39. 2.216 2.216 ↓ 0.0 0 1,108

Index Scan using uk_customer_phone_numbers_cid_customer_id_phone_number_type_id on customer_phone_numbers cpn (cost=0.29..3.85 rows=1 width=19) (actual time=0.002..0.002 rows=0 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (customer_id = mr.customer_id))
  • Filter: (phone_number_type_id <> 1)
  • Rows Removed by Filter: 0
40. 3.324 3.324 ↑ 1.0 1 1,108

Index Scan using pk_maintenance_problems on maintenance_problems mp (cost=0.42..6.20 rows=1 width=242) (actual time=0.003..0.003 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_problem_id))
41. 0.000 0.000 ↓ 0.0 0 1,108

Index Scan using pk_maintenance_problems on maintenance_problems mp1 (cost=0.42..6.20 rows=1 width=234) (actual time=0.000..0.000 rows=0 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.sub_maintenance_problem_id))
42. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_maintenance_problems on maintenance_problems mp2 (cost=0.42..3.47 rows=1 width=234) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = 224) AND (id = mp.maintenance_problem_id))
43. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..1.44 rows=1 width=714) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_location_id))
44. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_maintenance_priorities on maintenance_priorities mpr (cost=0.28..0.48 rows=1 width=501) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_priority_id))
45. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_maintenance_statuses on maintenance_statuses mst (cost=0.28..0.49 rows=1 width=678) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.maintenance_status_id))
46. 1.108 1.108 ↓ 0.0 0 1,108

Index Scan using pk_company_employees on company_employees ce (cost=0.28..0.96 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.company_employee_id))
47. 2.216 2.216 ↓ 0.0 0 1,108

Index Scan using idx_company_users_cid_company_employee_id on company_users cu (cost=0.42..33.27 rows=1 width=29) (actual time=0.001..0.002 rows=0 loops=1,108)

  • Index Cond: (company_employee_id = ce.id)
  • Filter: ((cid = 224) AND (company_user_type_id = 2) AND (cid = ce.cid))
48. 163.984 163.984 ↑ 1.0 1,563 1,108

Seq Scan on work_order_materials_cost (cost=0.00..28.54 rows=1,563 width=40) (actual time=0.003..0.148 rows=1,563 loops=1,108)

  • Filter: (cid = 224)
49. 1.108 1.108 ↑ 1.0 11 1,108

Seq Scan on work_order_labors_cost (cost=0.00..1.14 rows=11 width=40) (actual time=0.001..0.001 rows=11 loops=1,108)

  • Filter: (cid = 224)
50. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_properties on properties p (cost=0.28..0.80 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.property_id))
51. 3.324 3.324 ↑ 1.0 1 1,108

Index Scan using pk_property_units on property_units pu (cost=0.29..5.39 rows=1 width=210) (actual time=0.003..0.003 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.property_unit_id))
52. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_property_buildings on property_buildings pb (cost=0.28..0.74 rows=1 width=206) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = COALESCE(mr.property_building_id, pu.property_building_id)))
53. 4.432 4.432 ↑ 1.0 1 1,108

Index Scan using pk_unit_spaces on unit_spaces us (cost=0.29..5.58 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.unit_space_id))
  • Filter: ((deleted_on IS NULL) OR (deleted_on > '2020-04-30 00:00:00+00'::timestamp with time zone))
54. 1.108 1.108 ↑ 1.0 1 1,108

Index Scan using pk_unit_space_status_types on unit_space_status_types usst (cost=0.13..0.16 rows=1 width=122) (actual time=0.001..0.001 rows=1 loops=1,108)

  • Index Cond: (id = us.unit_space_status_type_id)
55. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using pk_unit_types on unit_types ut (cost=0.28..0.60 rows=1 width=215) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((cid = us.cid) AND (cid = 224) AND (id = COALESCE(pu.unit_type_id, us.unit_type_id)))
56. 3.324 3.324 ↓ 0.0 0 1,108

Index Scan using unq_idx_maintenance_request_notes_is_closing_note on maintenance_request_notes (cost=0.42..7.80 rows=1 width=237) (actual time=0.003..0.003 rows=0 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = mr.cid) AND (cid = 224) AND (maintenance_request_id = mr.id) AND (maintenance_request_id = mr.id))
57. 0.000 6.648 ↓ 0.0 0 1,108

Subquery Scan on mrn_internal (cost=9.79..9.81 rows=1 width=237) (actual time=0.006..0.006 rows=0 loops=1,108)

  • Filter: ((mrn_internal.cid = 224) AND (mrn_internal.cid = mr.cid) AND (mrn_internal.maintenance_request_id = mr.id))
58. 1.108 6.648 ↓ 0.0 0 1,108

Limit (cost=9.79..9.79 rows=1 width=241) (actual time=0.006..0.006 rows=0 loops=1,108)

59. 1.108 5.540 ↓ 0.0 0 1,108

Sort (cost=9.79..9.79 rows=1 width=241) (actual time=0.005..0.005 rows=0 loops=1,108)

  • Sort Key: maintenance_request_notes_1.id DESC
  • Sort Method: quicksort Memory: 25kB
60. 4.432 4.432 ↓ 0.0 0 1,108

Index Scan using idx_maintenance_request_notes_maintenance_request_id on maintenance_request_notes maintenance_request_notes_1 (cost=0.42..9.78 rows=1 width=241) (actual time=0.004..0.004 rows=0 loops=1,108)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: ((NOT is_closing_note) AND (is_resident_visible <> 1) AND (cid = mr.cid))
  • Rows Removed by Filter: 0
61. 0.000 0.000 ↓ 0.0 0 1,108

Index Scan using pk_ap_payees on ap_payees ap (cost=0.29..3.76 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=1,108)

  • Index Cond: ((cid = mr.cid) AND (cid = 224) AND (id = mr.ap_payee_id))
62. 1.108 3.324 ↓ 0.0 0 1,108

Limit (cost=8.32..8.32 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1,108)

63. 0.000 2.216 ↓ 0.0 0 1,108

Sort (cost=8.32..8.32 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,108)

  • Sort Key: maintenance_request_labors.id DESC
  • Sort Method: quicksort Memory: 25kB
64. 2.216 2.216 ↓ 0.0 0 1,108

Index Scan using idx_maintenance_request_labors_maintenance_request_id on maintenance_request_labors (cost=0.29..8.31 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,108)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: (mr.cid = cid)
65. 2.216 2.216 ↑ 1.0 1 1,108

Index Scan using uk_property_maintenance_priorities_property_id_maintenance_prio on property_maintenance_priorities pmp (cost=0.29..1.28 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=1,108)

  • Index Cond: ((property_id = mr.property_id) AND (maintenance_priority_id = mr.maintenance_priority_id) AND (cid = mr.cid) AND (cid = mr.cid) AND (cid = 224))