explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7E28

Settings
# exclusive inclusive rows x rows loops node
1. 383.587 31,807.280 ↓ 18,382.2 238,969 1

WindowAgg (cost=600.45..600.97 rows=13 width=1,413) (actual time=31,384.582..31,807.280 rows=238,969 loops=1)

2. 1,727.281 31,423.693 ↓ 18,382.2 238,969 1

Sort (cost=600.45..600.49 rows=13 width=1,397) (actual time=31,384.236..31,423.693 rows=238,969 loops=1)

  • Sort Key: p.id, ((COALESCE(((pb.id)::text || '-'::text), '-'::text) || (mrd.unit_number)::text))
  • Sort Method: quicksort Memory: 93569kB
3. 229.863 29,696.412 ↓ 18,382.2 238,969 1

Group (cost=599.30..600.21 rows=13 width=1,397) (actual time=29,433.332..29,696.412 rows=238,969 loops=1)

  • Group Key: p.property_name, p.lookup_code, p.id, pb.id, ut.name, ut.details, mr.id, mr.cid, mp1.name, mp1.details, pb.details, mrt.name, mp2.name, mp2.details, mrd.problem_description, mrd.details, ml.name, mrd.actual_start_datetime, mrd.completed_datetime, ms.name, ms.details, pb.building_name, mrd.unit_number
4. 3,873.312 29,466.549 ↓ 18,382.2 238,969 1

Sort (cost=599.30..599.34 rows=13 width=1,365) (actual time=29,433.306..29,466.549 rows=238,969 loops=1)

  • Sort Key: p.property_name, p.lookup_code, p.id, pb.id, ut.name, ut.details, mr.id, mp1.name, mp1.details, pb.details, mrt.name, mp2.name, mp2.details, mrd.problem_description, mrd.details, ml.name, mrd.actual_start_datetime, mrd.completed_datetime, ms.name, ms.details, pb.building_name, mrd.unit_number
  • Sort Method: quicksort Memory: 93569kB
5. 347.307 25,593.237 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=3.54..599.06 rows=13 width=1,365) (actual time=19.238..25,593.237 rows=238,969 loops=1)

6. 363.942 24,529.023 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=3.26..595.00 rows=13 width=1,029) (actual time=19.224..24,529.023 rows=238,969 loops=1)

7. 403.906 23,448.174 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=2.98..591.03 rows=13 width=1,021) (actual time=19.209..23,448.174 rows=238,969 loops=1)

8. 432.111 22,327.361 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=2.68..586.90 rows=13 width=753) (actual time=19.175..22,327.361 rows=238,969 loops=1)

9. 317.601 21,178.343 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=2.40..582.97 rows=13 width=532) (actual time=19.162..21,178.343 rows=238,969 loops=1)

10. 398.460 20,382.804 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=2.12..578.73 rows=13 width=338) (actual time=19.147..20,382.804 rows=238,969 loops=1)

  • Join Filter: (ut.cid = mr.cid)
11. 334.188 19,267.437 ↓ 18,382.2 238,969 1

Nested Loop Left Join (cost=1.83..574.06 rows=13 width=153) (actual time=19.134..19,267.437 rows=238,969 loops=1)

12. 365.134 17,738.404 ↓ 18,382.2 238,969 1

Nested Loop (cost=1.40..568.20 rows=13 width=145) (actual time=19.115..17,738.404 rows=238,969 loops=1)

  • Join Filter: (mr.maintenance_request_type_id = mrt.id)
  • Rows Removed by Join Filter: 716816
13. 1,820.628 17,373.270 ↓ 18,382.2 238,969 1

Nested Loop (cost=1.40..566.48 rows=13 width=136) (actual time=19.102..17,373.270 rows=238,969 loops=1)

14. 1,163.348 3,788.398 ↓ 6,051.6 2,941,061 1

Nested Loop (cost=0.97..319.68 rows=486 width=65) (actual time=18.928..3,788.398 rows=2,941,061 loops=1)

  • Join Filter: (lp.property_id = mr.property_id)
15. 0.005 18.877 ↑ 1.0 1 1

Nested Loop (cost=0.54..3.31 rows=1 width=33) (actual time=18.873..18.877 rows=1 loops=1)

16. 18.866 18.866 ↑ 1.0 1 1

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=4) (actual time=18.864..18.866 rows=1 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
17. 0.006 0.006 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.29..2.31 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = lp.property_id)
  • Filter: (cid = 235)
18. 2,606.173 2,606.173 ↓ 665.5 2,941,061 1

Index Scan using idx_maintenance_requests_property_id on maintenance_requests mr (cost=0.43..261.13 rows=4,419 width=44) (actual time=0.052..2,606.173 rows=2,941,061 loops=1)

  • Index Cond: (property_id = p.id)
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-02-01 00:00:00-07'::timestamp with time zone)) AND (cid = 235))
  • Rows Removed by Filter: 1135
19. 11,764.244 11,764.244 ↓ 0.0 0 2,941,061

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..0.50 rows=1 width=79) (actual time=0.004..0.004 rows=0 loops=2,941,061)

  • Index Cond: (maintenance_request_id = mr.id)
  • Filter: ((actual_start_datetime >= '2019-01-01 00:00:00-07'::timestamp with time zone) AND (actual_start_datetime <= '2019-02-04 23:59:59-07'::timestamp with time zone) AND (cid = 235))
  • Rows Removed by Filter: 1
20. 0.000 0.000 ↑ 1.0 4 238,969

Materialize (cost=0.00..1.06 rows=4 width=17) (actual time=0.000..0.000 rows=4 loops=238,969)

21. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on maintenance_request_types mrt (cost=0.00..1.04 rows=4 width=17) (actual time=0.005..0.006 rows=4 loops=1)

22. 1,194.845 1,194.845 ↑ 1.0 1 238,969

Index Scan using pk_property_units on property_units pu (cost=0.42..0.45 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=238,969)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.property_unit_id))
  • Filter: (deleted_on IS NULL)
23. 716.907 716.907 ↑ 1.0 1 238,969

Index Scan using idx_unit_types_id on unit_types ut (cost=0.29..0.35 rows=1 width=197) (actual time=0.003..0.003 rows=1 loops=238,969)

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = 235)
  • Rows Removed by Filter: 0
24. 477.938 477.938 ↑ 1.0 1 238,969

Index Scan using idx_property_buildings_id on property_buildings pb (cost=0.29..0.32 rows=1 width=206) (actual time=0.002..0.002 rows=1 loops=238,969)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 235) AND (cid = pu.cid))
25. 716.907 716.907 ↑ 1.0 1 238,969

Index Scan using pk_maintenance_priorities on maintenance_priorities mp1 (cost=0.28..0.30 rows=1 width=233) (actual time=0.003..0.003 rows=1 loops=238,969)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_priority_id))
26. 716.907 716.907 ↑ 1.0 1 238,969

Index Scan using pk_maintenance_problems on maintenance_problems mp2 (cost=0.29..0.32 rows=1 width=280) (actual time=0.003..0.003 rows=1 loops=238,969)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_problem_id))
  • Filter: (is_published = 1)
27. 716.907 716.907 ↑ 1.0 1 238,969

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..0.31 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=238,969)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_location_id))
28. 716.907 716.907 ↑ 1.0 1 238,969

Index Scan using idx_maintenance_statuses_id on maintenance_statuses ms (cost=0.28..0.30 rows=1 width=348) (actual time=0.003..0.003 rows=1 loops=238,969)

  • Index Cond: (id = mr.maintenance_status_id)
  • Filter: ((cid = 235) AND (is_published = 1) AND (cid = mr.cid))
  • Rows Removed by Filter: 0
Planning time : 33.116 ms