explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ige1

Settings
# exclusive inclusive rows x rows loops node
1. 86.058 16,652.267 ↓ 189,926.0 189,926 1

Subquery Scan on sub_query (cost=8,935.70..8,941.30 rows=1 width=886) (actual time=16,534.080..16,652.267 rows=189,926 loops=1)

  • Filter: ((sub_query.count >= 1) AND (sub_query.cid = 235))
2. 6,195.130 16,566.209 ↓ 593.5 189,926 1

Sort (cost=8,935.70..8,936.50 rows=320 width=2,288) (actual time=16,534.069..16,566.209 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. 838.056 10,371.079 ↓ 593.5 189,926 1

WindowAgg (cost=8,841.58..8,922.38 rows=320 width=2,288) (actual time=6,077.402..10,371.079 rows=189,926 loops=1)

4. 327.917 6,114.355 ↓ 593.5 189,926 1

Sort (cost=8,841.58..8,842.38 rows=320 width=2,216) (actual time=6,077.192..6,114.355 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. 418.423 5,786.438 ↓ 593.5 189,926 1

Group (cost=8,805.07..8,828.27 rows=320 width=2,216) (actual time=5,324.377..5,786.438 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. 501.037 5,368.015 ↓ 593.5 189,926 1

Sort (cost=8,805.07..8,805.87 rows=320 width=2,184) (actual time=5,324.354..5,368.015 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. 180.828 4,866.978 ↓ 593.5 189,926 1

Hash Left Join (cost=252.31..8,791.75 rows=320 width=2,184) (actual time=0.947..4,866.978 rows=189,926 loops=1)

  • Hash Cond: ((mrdt.cid = ms.cid) AND (mrdt.maintenance_status_id = ms.id))
8. 170.578 4,686.000 ↓ 593.5 189,926 1

Hash Left Join (cost=203.18..8,740.95 rows=320 width=1,848) (actual time=0.790..4,686.000 rows=189,926 loops=1)

  • Hash Cond: ((mrdt.cid = ml.cid) AND (mrdt.maintenance_location_id = ml.id))
9. 233.309 4,514.908 ↓ 593.5 189,926 1

Nested Loop Left Join (cost=65.48..8,601.56 rows=320 width=1,840) (actual time=0.269..4,514.908 rows=189,926 loops=1)

10. 197.599 3,521.895 ↓ 593.5 189,926 1

Hash Left Join (cost=65.19..8,038.96 rows=320 width=1,572) (actual time=0.246..3,521.895 rows=189,926 loops=1)

  • Hash Cond: ((mrdt.cid = mp1.cid) AND (mrdt.maintenance_priority_id = mp1.id))
11. 319.804 3,324.099 ↓ 593.5 189,926 1

Nested Loop Left Join (cost=1.00..7,973.10 rows=320 width=1,351) (actual time=0.039..3,324.099 rows=189,926 loops=1)

12. 218.241 2,624.443 ↓ 593.5 189,926 1

Nested Loop Left Join (cost=0.71..7,868.78 rows=320 width=1,157) (actual time=0.036..2,624.443 rows=189,926 loops=1)

  • Join Filter: (ut.cid = mrdt.cid)
13. 325.801 1,646.498 ↓ 593.5 189,926 1

Nested Loop Left Join (cost=0.42..7,753.72 rows=320 width=972) (actual time=0.034..1,646.498 rows=189,926 loops=1)

14. 181.141 181.141 ↓ 593.5 189,926 1

Seq Scan on maintenace_request_data_temp mrdt (cost=0.00..7,002.52 rows=320 width=964) (actual time=0.029..181.141 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. 1,139.556 1,139.556 ↑ 1.0 1 189,926

Index Scan using pk_property_units on property_units pu (cost=0.42..2.35 rows=1 width=16) (actual time=0.006..0.006 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. 759.704 759.704 ↑ 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.004..0.004 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. 0.032 0.197 ↑ 1.1 100 1

Hash (cost=62.60..62.60 rows=106 width=233) (actual time=0.197..0.197 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.165 0.165 ↑ 1.1 100 1

Index Scan using pk_maintenance_priorities on maintenance_priorities mp1 (cost=0.28..62.60 rows=106 width=233) (actual time=0.023..0.165 rows=100 loops=1)

  • Index Cond: (cid = 235)
20. 759.704 759.704 ↑ 1.0 1 189,926

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

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.maintenance_problem_id))
  • Filter: (is_published = 1)
21. 0.083 0.514 ↑ 1.1 288 1

Hash (cost=132.89..132.89 rows=321 width=20) (actual time=0.514..0.514 rows=288 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
22. 0.431 0.431 ↑ 1.1 288 1

Index Scan using pk_maintenance_locations on maintenance_locations ml (cost=0.28..132.89 rows=321 width=20) (actual time=0.023..0.431 rows=288 loops=1)

  • Index Cond: (cid = 235)
23. 0.033 0.150 ↑ 1.2 73 1

Hash (cost=47.86..47.86 rows=84 width=348) (actual time=0.150..0.150 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
24. 0.117 0.117 ↑ 1.2 73 1

Index Scan using pk_maintenance_statuses on maintenance_statuses ms (cost=0.28..47.86 rows=84 width=348) (actual time=0.020..0.117 rows=73 loops=1)

  • Index Cond: (cid = 235)
  • Filter: (is_published = 1)
  • Rows Removed by Filter: 3
25.          

SubPlan (forWindowAgg)

26. 949.630 3,418.668 ↑ 1.0 1 189,926

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

27. 2,469.038 2,469.038 ↑ 5.0 2 189,926

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

Planning time : 12.080 ms