explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VuaF

Settings
# exclusive inclusive rows x rows loops node
1. 23.197 141.393 ↓ 13.8 2,760 1

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

  • Group Key: rw.id
2. 0.921 118.196 ↓ 13.3 2,773 1

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

3. 0.447 103.475 ↓ 13.8 2,760 1

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

4. 1.199 103.028 ↓ 1.3 2,760 1

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

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

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

6. 1.961 76.006 ↓ 1.2 2,789 1

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

  • Group Key: rw.id, rw.actualroadworkrequestdatesid
7. 0.180 74.045 ↓ 2.5 5,554 1

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

8. 0.273 62.869 ↓ 2.4 2,789 1

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

9. 0.720 62.596 ↓ 2.4 2,802 1

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

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

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

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

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

12. 0.000 51.752 ↓ 2.4 2,789 1

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

13. 0.916 49.110 ↓ 2.4 2,789 1

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

14. 0.066 45.405 ↓ 2.4 2,789 1

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

15. 0.224 28.605 ↓ 2.4 2,789 1

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

16. 0.009 0.009 ↑ 1.0 1 1

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

17. 28.372 28.372 ↓ 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.356..28.372 rows=2,789 loops=1)

  • Index Cond: (organisationid = uo.id)
  • Heap Fetches: 0
18. 16.734 16.734 ↑ 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.006..0.006 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. 8.367 8.367 ↑ 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.003..0.003 rows=1 loops=2,789)

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

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

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

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

24. 1.197 10.996 ↓ 2.6 2,765 1

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

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

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

26. 0.404 4.467 ↓ 2.6 2,765 1

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

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

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

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

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

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

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

30. 0.003 0.018 ↑ 1.0 1 1

Nested Loop (cost=0.28..5.31 rows=1 width=8) (actual time=0.016..0.018 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.007..0.007 rows=1 loops=1)

32. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (id = uo_1.id)
  • Heap Fetches: 0
33. 2.247 2.247 ↓ 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=0.016..2.247 rows=2,765 loops=1)

  • Index Cond: (roadworkrequestownerorganisationid = ownr_1.id)
34. 0.363 0.363 ↑ 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.015..0.363 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.016..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. 25.101 25.101 ↑ 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.009..0.009 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. 13.800 13.800 ↑ 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.005..0.005 rows=1 loops=2,760)

  • Index Cond: (entityid = rw.id)