explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AliU

Settings
# exclusive inclusive rows x rows loops node
1. 92.100 18,829.541 ↓ 189,926.0 189,926 1

Subquery Scan on sub_query (cost=3,727.10..3,727.12 rows=1 width=886) (actual time=18,693.744..18,829.541 rows=189,926 loops=1)

  • Filter: ((sub_query.count >= 1) AND (sub_query.cid = 235))
2. 6,811.810 18,737.441 ↓ 189,926.0 189,926 1

Sort (cost=3,727.10..3,727.11 rows=1 width=2,288) (actual time=18,693.735..18,737.441 rows=189,926 loops=1)

  • Sort Key: mrdt.property_name, mrdt.lookup_code, (count(*) OVER (?)), pb.building_name, ((SubPlan 1)), mrdt.id, mrdt.deleted_on
  • Sort Method: quicksort Memory: 104153kB
3. 895.428 11,925.631 ↓ 189,926.0 189,926 1

WindowAgg (cost=3,726.84..3,727.09 rows=1 width=2,288) (actual time=7,757.734..11,925.631 rows=189,926 loops=1)

4. 173.444 7,801.461 ↓ 189,926.0 189,926 1

Sort (cost=3,726.84..3,726.84 rows=1 width=2,216) (actual time=7,757.571..7,801.461 rows=189,926 loops=1)

  • Sort Key: mrdt.id, ((COALESCE(((pb.id)::text || '-'::text), '-'::text) || (mrdt.unit_number)::text))
  • Sort Method: quicksort Memory: 73406kB
5. 169.683 7,628.017 ↓ 189,926.0 189,926 1

Group (cost=3,726.75..3,726.83 rows=1 width=2,216) (actual time=7,438.516..7,628.017 rows=189,926 loops=1)

  • Group Key: mrdt.property_id, mrdt.property_name, mrdt.lookup_code, mrdt.id, pb.id, ut.name, ut.details, mrdt.cid, mp1.name, mp1.details, pb.details, mrdt.name, mp2.name, mp2.details, mrdt.problem_description, mrdt.details, ml.name, mrdt.created_on, mrdt.completed_datetime, ms.name, ms.details, pb.building_name, mrdt.unit_number, mrdt.deleted_on
6. 2,647.539 7,458.334 ↓ 189,926.0 189,926 1

Sort (cost=3,726.75..3,726.76 rows=1 width=2,184) (actual time=7,438.473..7,458.334 rows=189,926 loops=1)

  • Sort Key: mrdt.property_id, mrdt.property_name, mrdt.lookup_code, mrdt.id, pb.id, ut.name, ut.details, mp1.name, mp1.details, pb.details, mrdt.name, mp2.name, mp2.details, mrdt.problem_description, mrdt.details, ml.name, mrdt.created_on, mrdt.completed_datetime, ms.name, ms.details, pb.building_name, mrdt.unit_number, mrdt.deleted_on
  • Sort Method: quicksort Memory: 72531kB
7. 246.787 4,810.795 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=2.13..3,726.74 rows=1 width=2,184) (actual time=0.192..4,810.795 rows=189,926 loops=1)

8. 270.803 4,184.156 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=1.85..3,724.43 rows=1 width=1,848) (actual time=0.172..4,184.156 rows=189,926 loops=1)

9. 275.556 3,533.501 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=1.57..3,722.11 rows=1 width=1,840) (actual time=0.150..3,533.501 rows=189,926 loops=1)

10. 280.863 2,878.093 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=1.28..3,719.79 rows=1 width=1,572) (actual time=0.123..2,878.093 rows=189,926 loops=1)

11. 92.016 2,217.378 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=1.00..3,717.45 rows=1 width=1,351) (actual time=0.101..2,217.378 rows=189,926 loops=1)

12. 273.703 1,745.510 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=0.71..3,717.13 rows=1 width=1,157) (actual time=0.086..1,745.510 rows=189,926 loops=1)

  • Join Filter: (ut.cid = mrdt.cid)
13. 200.728 1,091.955 ↓ 189,926.0 189,926 1

Nested Loop Left Join (cost=0.42..3,716.77 rows=1 width=972) (actual time=0.065..1,091.955 rows=189,926 loops=1)

14. 131.523 131.523 ↓ 189,926.0 189,926 1

Seq Scan on maintenace_request_data_temp mrdt (cost=0.00..3,714.32 rows=1 width=964) (actual time=0.040..131.523 rows=189,926 loops=1)

  • Filter: (((deleted_on IS NULL) OR (deleted_on > '2019-01-31 00:00:00-07'::timestamp with time zone)) AND (created_on >= '2019-01-01'::date) AND (created_on <= '2019-01-31'::date) AND (cid = 235))
15. 759.704 759.704 ↑ 1.0 1 189,926

Index Scan using pk_property_units on property_units pu (cost=0.42..2.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=189,926)

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.property_unit_id))
  • Filter: (deleted_on IS NULL)
16. 379.852 379.852 ↑ 1.0 1 189,926

Index Scan using idx_unit_types_id on unit_types ut (cost=0.29..0.35 rows=1 width=197) (actual time=0.002..0.002 rows=1 loops=189,926)

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = 235)
  • Rows Removed by Filter: 0
17. 379.852 379.852 ↑ 1.0 1 189,926

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=189,926)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 235) AND (cid = pu.cid))
18. 379.852 379.852 ↑ 1.0 1 189,926

Index Scan using pk_maintenance_priorities on maintenance_priorities mp1 (cost=0.28..2.30 rows=1 width=233) (actual time=0.002..0.002 rows=1 loops=189,926)

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.maintenance_priority_id))
19. 379.852 379.852 ↑ 1.0 1 189,926

Index Scan using pk_maintenance_problems on maintenance_problems mp2 (cost=0.29..2.32 rows=1 width=280) (actual time=0.002..0.002 rows=1 loops=189,926)

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.maintenance_problem_id))
  • Filter: (is_published = 1)
20. 379.852 379.852 ↑ 1.0 1 189,926

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..2.30 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=189,926)

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.maintenance_location_id))
21. 379.852 379.852 ↑ 1.0 1 189,926

Index Scan using idx_maintenance_statuses_id on maintenance_statuses ms (cost=0.28..2.30 rows=1 width=348) (actual time=0.002..0.002 rows=1 loops=189,926)

  • Index Cond: (id = mrdt.maintenance_status_id)
  • Filter: ((cid = 235) AND (is_published = 1) AND (cid = mrdt.cid))
  • Rows Removed by Filter: 0
22.          

SubPlan (forWindowAgg)

23. 949.630 3,228.742 ↑ 1.0 1 189,926

Aggregate (cost=0.21..0.22 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=189,926)

24. 2,279.112 2,279.112 ↑ 5.0 2 189,926

Function Scan on regexp_matches matches (cost=0.00..0.10 rows=10 width=32) (actual time=0.012..0.012 rows=2 loops=189,926)

Planning time : 10.518 ms