explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aAjw

Settings
# exclusive inclusive rows x rows loops node
1. 20.470 1,701.389 ↓ 13.8 2,760 1

GroupAggregate (cost=40,673.28..42,852.26 rows=200 width=40) (actual time=1,670.236..1,701.389 rows=2,760 loops=1)

  • Group Key: rw.id
2. 1.649 1,680.919 ↓ 13.3 2,773 1

Nested Loop (cost=40,673.28..42,348.22 rows=208 width=50) (actual time=1,670.160..1,680.919 rows=2,773 loops=1)

3. 0.391 1,670.990 ↓ 13.8 2,760 1

Unique (cost=40,672.85..40,683.72 rows=200 width=4) (actual time=1,670.143..1,670.990 rows=2,760 loops=1)

4. 1.594 1,670.599 ↓ 1.3 2,760 1

Sort (cost=40,672.85..40,678.28 rows=2,173 width=4) (actual time=1,670.143..1,670.599 rows=2,760 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 226kB
5. 0.413 1,669.005 ↓ 1.3 2,760 1

Nested Loop (cost=23,255.49..40,552.41 rows=2,173 width=4) (actual time=231.892..1,669.005 rows=2,760 loops=1)

6. 2.501 215.523 ↓ 1.2 2,789 1

HashAggregate (cost=23,255.06..23,277.72 rows=2,266 width=8) (actual time=214.293..215.523 rows=2,789 loops=1)

  • Group Key: rw.id, rw.actualroadworkrequestdatesid
7. 0.189 213.022 ↓ 2.5 5,554 1

Append (cost=22,366.78..23,243.73 rows=2,266 width=8) (actual time=188.827..213.022 rows=5,554 loops=1)

8. 0.233 189.135 ↓ 2.4 2,789 1

Unique (cost=22,366.78..22,375.65 rows=1,183 width=8) (actual time=188.827..189.135 rows=2,789 loops=1)

9. 0.545 188.902 ↓ 2.4 2,802 1

Sort (cost=22,366.78..22,369.74 rows=1,183 width=8) (actual time=188.826..188.902 rows=2,802 loops=1)

  • Sort Key: rw.id, rw.actualroadworkrequestdatesid
  • Sort Method: quicksort Memory: 228kB
10. 0.597 188.357 ↓ 2.4 2,802 1

Hash Join (cost=3.36..22,306.40 rows=1,183 width=8) (actual time=29.366..188.357 rows=2,802 loops=1)

  • Hash Cond: (rw.roadworkstatustypeid = rs.id)
11. 0.440 187.725 ↓ 2.4 2,802 1

Nested Loop (cost=1.84..22,290.40 rows=1,183 width=12) (actual time=29.325..187.725 rows=2,802 loops=1)

12. 0.000 181.707 ↓ 2.4 2,789 1

Nested Loop (cost=1.42..21,744.36 rows=1,178 width=16) (actual time=29.306..181.707 rows=2,789 loops=1)

13. 0.450 179.197 ↓ 2.4 2,789 1

Nested Loop (cost=1.14..21,390.87 rows=1,178 width=20) (actual time=29.301..179.197 rows=2,789 loops=1)

14. 0.150 175.958 ↓ 2.4 2,789 1

Nested Loop (cost=0.85..21,037.39 rows=1,178 width=24) (actual time=29.283..175.958 rows=2,789 loops=1)

15. 0.217 19.624 ↓ 2.4 2,789 1

Nested Loop (cost=0.43..20,345.49 rows=1,178 width=4) (actual time=0.145..19.624 rows=2,789 loops=1)

16. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on userorgids uo (cost=0.00..1.01 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

17. 19.399 19.399 ↓ 2.4 2,789 1

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.43..20,332.70 rows=1,178 width=8) (actual time=0.135..19.399 rows=2,789 loops=1)

  • Index Cond: (organisationid = uo.id)
  • Heap Fetches: 0
18. 156.184 156.184 ↑ 1.0 1 2,789

Index Scan using pk_request on roadworkrequest rw (cost=0.42..0.59 rows=1 width=24) (actual time=0.056..0.056 rows=1 loops=2,789)

  • Index Cond: (id = c.roadworkrequestid)
19. 2.789 2.789 ↑ 1.0 1 2,789

Index Only Scan using pk_organisation on organisation psm (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,789)

  • Index Cond: (id = rw.roadworkrequestpsmorganisationid)
  • Heap Fetches: 0
20. 2.789 2.789 ↑ 1.0 1 2,789

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,789)

  • Index Cond: (id = rw.roadworkrequestownerorganisationid)
  • Heap Fetches: 0
21. 5.578 5.578 ↑ 1.0 1 2,789

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis (cost=0.42..0.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,789)

  • Index Cond: (entityid = rw.id)
  • Heap Fetches: 0
22. 0.009 0.035 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.035..0.035 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.026 0.026 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rs (cost=0.00..1.23 rows=23 width=4) (actual time=0.022..0.026 rows=23 loops=1)

24. 1.192 23.698 ↓ 2.6 2,765 1

HashAggregate (cost=834.59..845.42 rows=1,083 width=8) (actual time=23.303..23.698 rows=2,765 loops=1)

  • Group Key: rw_1.id, rw_1.actualroadworkrequestdatesid
25. 0.000 22.506 ↓ 2.6 2,778 1

Nested Loop (cost=117.95..829.17 rows=1,083 width=8) (actual time=16.023..22.506 rows=2,778 loops=1)

26. 0.364 17.425 ↓ 2.6 2,765 1

Hash Join (cost=117.52..329.49 rows=1,078 width=8) (actual time=16.012..17.425 rows=2,765 loops=1)

  • Hash Cond: (rw_1.roadworkstatustypeid = rs_1.id)
27. 0.592 17.037 ↓ 2.6 2,765 1

Merge Join (cost=116.01..314.78 rows=1,078 width=12) (actual time=15.978..17.037 rows=2,765 loops=1)

  • Merge Cond: (rw_1.roadworkrequestpsmorganisationid = psm_1.id)
28. 0.505 16.088 ↓ 2.6 2,765 1

Sort (cost=115.69..118.39 rows=1,078 width=16) (actual time=15.948..16.088 rows=2,765 loops=1)

  • Sort Key: rw_1.roadworkrequestpsmorganisationid
  • Sort Method: quicksort Memory: 226kB
29. 0.241 15.583 ↓ 2.6 2,765 1

Nested Loop (cost=0.71..61.39 rows=1,078 width=16) (actual time=12.746..15.583 rows=2,765 loops=1)

30. 0.001 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.28..5.31 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1)

31. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on userorgids uo_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

32. 0.007 0.007 ↑ 1.0 1 1

Index Only Scan using pk_organisation on organisation ownr_1 (cost=0.28..4.30 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = uo_1.id)
  • Heap Fetches: 0
33. 15.327 15.327 ↓ 2.6 2,765 1

Index Scan using ix_roadworkrequest_ownerorganisationid on roadworkrequest rw_1 (cost=0.42..45.30 rows=1,078 width=24) (actual time=12.729..15.327 rows=2,765 loops=1)

  • Index Cond: (roadworkrequestownerorganisationid = ownr_1.id)
34. 0.357 0.357 ↑ 1.1 5,452 1

Index Only Scan using pk_organisation on organisation psm_1 (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.016..0.357 rows=5,452 loops=1)

  • Heap Fetches: 0
35. 0.005 0.024 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.024..0.024 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.019 0.019 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rs_1 (cost=0.00..1.23 rows=23 width=4) (actual time=0.017..0.019 rows=23 loops=1)

37. 5.530 5.530 ↑ 1.0 1 2,765

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis_1 (cost=0.42..0.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,765)

  • Index Cond: (entityid = rw_1.id)
  • Heap Fetches: 0
38. 1,453.069 1,453.069 ↑ 1.0 1 2,789

Index Scan using pk_roadworkrequestdates on roadworkrequestdates ad (cost=0.43..7.61 rows=1 width=4) (actual time=0.521..0.521 rows=1 loops=2,789)

  • Index Cond: (id = rw.actualroadworkrequestdatesid)
  • Filter: ((completeddate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (startdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 0
39. 8.280 8.280 ↑ 1.0 1 2,760

Index Scan using ix_geometryroadworkid on geometryroadwork rwgis (cost=0.42..8.30 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=2,760)

  • Index Cond: (entityid = rw.id)