explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rRdx

Settings
# exclusive inclusive rows x rows loops node
1. 20.361 108.928 ↓ 13.8 2,760 1

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

  • Group Key: rw.id
2. 1.422 88.567 ↓ 13.3 2,773 1

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

3. 0.386 78.865 ↓ 13.8 2,760 1

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

4. 1.222 78.479 ↓ 1.3 2,760 1

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

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

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

6. 2.357 57.248 ↓ 1.2 2,789 1

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

  • Group Key: rw.id, rw.actualroadworkrequestdatesid
7. 0.228 54.891 ↓ 2.5 5,554 1

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

8. 0.276 43.456 ↓ 2.4 2,789 1

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

9. 0.680 43.180 ↓ 2.4 2,802 1

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

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

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

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

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

12. 0.000 35.646 ↓ 2.4 2,789 1

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

13. 0.520 33.042 ↓ 2.4 2,789 1

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

14. 0.919 29.733 ↓ 2.4 2,789 1

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

15. 0.154 20.447 ↓ 2.4 2,789 1

Nested Loop (cost=0.43..20,345.49 rows=1,178 width=4) (actual time=0.122..20.447 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. 20.284 20.284 ↓ 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.111..20.284 rows=2,789 loops=1)

  • Index Cond: (organisationid = uo.id)
  • Heap Fetches: 0
18. 8.367 8.367 ↑ 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.003..0.003 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.006 0.033 ↑ 1.0 23 1

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

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

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

24. 1.250 11.207 ↓ 2.6 2,765 1

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

  • Group Key: rw_1.id, rw_1.actualroadworkrequestdatesid
25. 0.416 9.957 ↓ 2.6 2,778 1

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

26. 0.424 4.011 ↓ 2.6 2,765 1

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

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

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

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

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

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

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

30. 0.011 0.029 ↑ 1.0 1 1

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

31. 0.009 0.009 ↑ 1.0 1 1

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

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

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

  • Index Cond: (roadworkrequestownerorganisationid = ownr_1.id)
34. 0.358 0.358 ↑ 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.011..0.358 rows=5,452 loops=1)

  • Heap Fetches: 0
35. 0.007 0.030 ↑ 1.0 23 1

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

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

Seq Scan on roadworkstatustype rs_1 (cost=0.00..1.23 rows=23 width=4) (actual time=0.020..0.023 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. 19.523 19.523 ↑ 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.007..0.007 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)