explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WiiU

Settings
# exclusive inclusive rows x rows loops node
1. 231.982 29,897.168 ↓ 60,935.7 182,807 1

Subquery Scan on sub_query (cost=593.64..593.79 rows=3 width=200) (actual time=29,631.316..29,897.168 rows=182,807 loops=1)

  • Filter: (sub_query.count >= 1)
2. 3,378.369 29,665.186 ↓ 18,280.7 182,807 1

Sort (cost=593.64..593.66 rows=10 width=1,441) (actual time=29,631.265..29,665.186 rows=182,807 loops=1)

  • Sort Key: p.property_name, p.lookup_code, (count(*) OVER (?)), pb.building_name, mrd.unit_number, mr.id, mr.deleted_on
  • Sort Method: quicksort Memory: 70544kB
3. 291.083 26,286.817 ↓ 18,280.7 182,807 1

WindowAgg (cost=593.02..593.47 rows=10 width=1,441) (actual time=25,965.453..26,286.817 rows=182,807 loops=1)

4. 1,264.070 25,995.734 ↓ 18,280.7 182,807 1

Sort (cost=593.02..593.05 rows=10 width=1,393) (actual time=25,965.168..25,995.734 rows=182,807 loops=1)

  • Sort Key: p.id, ((COALESCE(((pb.id)::text || '-'::text), '-'::text) || (mrd.unit_number)::text))
  • Sort Method: quicksort Memory: 70543kB
5. 161.693 24,731.664 ↓ 18,280.7 182,807 1

Group (cost=592.15..592.85 rows=10 width=1,393) (actual time=24,548.538..24,731.664 rows=182,807 loops=1)

  • Group 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, mr.deleted_on
6. 2,589.179 24,569.971 ↓ 18,280.7 182,807 1

Sort (cost=592.15..592.18 rows=10 width=1,361) (actual time=24,548.518..24,569.971 rows=182,807 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, mr.deleted_on
  • Sort Method: quicksort Memory: 69613kB
7. 177.436 21,980.792 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=3.54..591.99 rows=10 width=1,361) (actual time=20.838..21,980.792 rows=182,807 loops=1)

8. 211.427 21,254.935 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=3.26..588.86 rows=10 width=1,029) (actual time=20.818..21,254.935 rows=182,807 loops=1)

9. 187.561 20,495.087 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=2.98..585.81 rows=10 width=1,021) (actual time=20.801..20,495.087 rows=182,807 loops=1)

10. 219.090 19,759.105 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=2.68..582.63 rows=10 width=753) (actual time=20.762..19,759.105 rows=182,807 loops=1)

11. 188.424 18,991.594 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=2.40..579.61 rows=10 width=532) (actual time=20.743..18,991.594 rows=182,807 loops=1)

12. 210.069 18,437.556 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=2.12..576.35 rows=10 width=338) (actual time=20.725..18,437.556 rows=182,807 loops=1)

  • Join Filter: (ut.cid = mr.cid)
13. 152.556 17,679.066 ↓ 18,280.7 182,807 1

Nested Loop Left Join (cost=1.83..572.75 rows=10 width=153) (actual time=20.706..17,679.066 rows=182,807 loops=1)

14. 265.203 16,612.475 ↓ 18,280.7 182,807 1

Nested Loop (cost=1.40..568.24 rows=10 width=145) (actual time=20.680..16,612.475 rows=182,807 loops=1)

  • Join Filter: (mr.maintenance_request_type_id = mrt.id)
  • Rows Removed by Join Filter: 548330
15. 881.067 16,347.272 ↓ 18,280.7 182,807 1

Nested Loop (cost=1.40..566.66 rows=10 width=136) (actual time=20.665..16,347.272 rows=182,807 loops=1)

16. 1,072.282 3,701.961 ↓ 6,051.6 2,941,061 1

Nested Loop (cost=0.97..319.87 rows=486 width=65) (actual time=20.456..3,701.961 rows=2,941,061 loops=1)

  • Join Filter: (load_prop.property_id = mr.property_id)
17. 0.007 20.390 ↑ 1.0 1 1

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

18. 20.376 20.376 ↑ 1.0 1 1

Function Scan on load_properties load_prop (cost=0.25..1.00 rows=1 width=4) (actual time=20.373..20.376 rows=1 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
19. 0.007 0.007 ↑ 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.007 rows=1 loops=1)

  • Index Cond: (id = load_prop.property_id)
  • Filter: (cid = 235)
20. 2,609.289 2,609.289 ↓ 665.1 2,941,061 1

Index Scan using idx_maintenance_requests_property_id on maintenance_requests mr (cost=0.43..261.28 rows=4,422 width=44) (actual time=0.069..2,609.289 rows=2,941,061 loops=1)

  • Index Cond: (property_id = p.id)
  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-01-30 00:00:00-07'::timestamp with time zone)) AND (cid = 235))
  • Rows Removed by Filter: 1135
21. 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-01-30 23:59:59-07'::timestamp with time zone) AND (cid = 235))
  • Rows Removed by Filter: 1
22. 0.000 0.000 ↑ 1.0 4 182,807

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

23. 0.007 0.007 ↑ 1.0 4 1

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

24. 914.035 914.035 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.property_unit_id))
  • Filter: (deleted_on IS NULL)
25. 548.421 548.421 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = 235)
  • Rows Removed by Filter: 0
26. 365.614 365.614 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 235) AND (cid = pu.cid))
27. 548.421 548.421 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_priority_id))
28. 548.421 548.421 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_problem_id))
  • Filter: (is_published = 1)
29. 548.421 548.421 ↑ 1.0 1 182,807

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=182,807)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_location_id))
30. 548.421 548.421 ↑ 1.0 1 182,807

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=182,807)

  • 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 : 61.948 ms