explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4mBb

Settings
# exclusive inclusive rows x rows loops node
1. 109.319 21,238.410 ↓ 189,910.0 189,910 1

Subquery Scan on sub_query (cost=19,555.46..19,567.13 rows=1 width=886) (actual time=21,091.077..21,238.410 rows=189,910 loops=1)

  • Filter: ((sub_query.count >= 1) AND (sub_query.cid = 235))
2. 7,956.397 21,129.091 ↓ 284.7 189,910 1

Sort (cost=19,555.46..19,557.13 rows=667 width=2,288) (actual time=21,091.068..21,129.091 rows=189,910 loops=1)

  • Sort Key: mrdt.property_name, mrdt.lookup_code, (count(*) OVER (?)), pb.building_name, (array_append((array_agg(ROW(mrdt.matches[1], (NULLIF(mrdt.matches[2], ''::text))::numeric))), ROW((mrdt.unit_number)::text, NULL::numeric))), mrdt.id, mrdt.deleted_on
  • Sort Method: quicksort Memory: 104180kB
3. 800.380 13,172.694 ↓ 284.7 189,910 1

WindowAgg (cost=19,499.16..19,524.17 rows=667 width=2,288) (actual time=12,343.312..13,172.694 rows=189,910 loops=1)

4. 246.549 12,372.314 ↓ 284.7 189,910 1

Sort (cost=19,499.16..19,500.83 rows=667 width=2,248) (actual time=12,343.259..12,372.314 rows=189,910 loops=1)

  • Sort Key: mrdt.id, ((COALESCE(((pb.id)::text || '-'::text), '-'::text) || (mrdt.unit_number)::text))
  • Sort Method: quicksort Memory: 89661kB
5. 1,380.964 12,125.765 ↓ 284.7 189,910 1

GroupAggregate (cost=19,404.51..19,467.87 rows=667 width=2,248) (actual time=10,687.652..12,125.765 rows=189,910 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. 1,654.327 10,744.801 ↓ 593.9 396,158 1

Sort (cost=19,404.51..19,406.17 rows=667 width=2,216) (actual time=10,687.593..10,744.801 rows=396,158 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: 152619kB
7. 318.825 9,090.474 ↓ 593.9 396,158 1

Hash Left Join (cost=252.31..19,373.22 rows=667 width=2,216) (actual time=1.961..9,090.474 rows=396,158 loops=1)

  • Hash Cond: ((mrdt.cid = ms.cid) AND (mrdt.maintenance_status_id = ms.id))
8. 317.850 8,771.347 ↓ 593.9 396,158 1

Hash Left Join (cost=203.18..19,320.60 rows=667 width=1,880) (actual time=1.647..8,771.347 rows=396,158 loops=1)

  • Hash Cond: ((mrdt.cid = ml.cid) AND (mrdt.maintenance_location_id = ml.id))
9. 714.310 8,452.430 ↓ 593.9 396,158 1

Nested Loop Left Join (cost=65.48..19,179.39 rows=667 width=1,872) (actual time=0.568..8,452.430 rows=396,158 loops=1)

10. 354.746 6,549.646 ↓ 593.9 396,158 1

Hash Left Join (cost=65.19..18,240.62 rows=667 width=1,604) (actual time=0.548..6,549.646 rows=396,158 loops=1)

  • Hash Cond: ((mrdt.cid = mp1.cid) AND (mrdt.maintenance_priority_id = mp1.id))
11. 589.291 6,194.497 ↓ 593.9 396,158 1

Nested Loop Left Join (cost=1.00..18,172.93 rows=667 width=1,383) (actual time=0.132..6,194.497 rows=396,158 loops=1)

12. 665.380 4,812.890 ↓ 593.9 396,158 1

Nested Loop Left Join (cost=0.71..17,955.49 rows=667 width=1,189) (actual time=0.099..4,812.890 rows=396,158 loops=1)

  • Join Filter: (ut.cid = mrdt.cid)
13. 644.801 2,959.036 ↓ 593.9 396,158 1

Nested Loop Left Join (cost=0.42..17,715.64 rows=667 width=1,004) (actual time=0.065..2,959.036 rows=396,158 loops=1)

14. 333.445 333.445 ↓ 593.9 396,158 1

Seq Scan on maintenace_request_data_temp mrdt (cost=0.00..16,215.16 rows=667 width=996) (actual time=0.039..333.445 rows=396,158 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,980.790 1,980.790 ↑ 1.0 1 396,158

Index Scan using pk_property_units on property_units pu (cost=0.42..2.25 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=396,158)

  • Index Cond: ((cid = mrdt.cid) AND (cid = 235) AND (id = mrdt.property_unit_id))
  • Filter: (deleted_on IS NULL)
16. 1,188.474 1,188.474 ↑ 1.0 1 396,158

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=396,158)

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = 235)
  • Rows Removed by Filter: 0
17. 792.316 792.316 ↑ 1.0 1 396,158

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=396,158)

  • Index Cond: (id = pu.property_building_id)
  • Filter: ((cid = 235) AND (cid = pu.cid))
18. 0.079 0.403 ↑ 1.1 100 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.324 0.324 ↑ 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.037..0.324 rows=100 loops=1)

  • Index Cond: (cid = 235)
20. 1,188.474 1,188.474 ↑ 1.0 1 396,158

Index Scan using pk_maintenance_problems on maintenance_problems mp2 (cost=0.29..1.41 rows=1 width=280) (actual time=0.003..0.003 rows=1 loops=396,158)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
22. 0.880 0.880 ↑ 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.027..0.880 rows=288 loops=1)

  • Index Cond: (cid = 235)
23. 0.062 0.302 ↑ 1.2 73 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
24. 0.240 0.240 ↑ 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.043..0.240 rows=73 loops=1)

  • Index Cond: (cid = 235)
  • Filter: (is_published = 1)
  • Rows Removed by Filter: 3
Planning time : 9.517 ms