explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J59h

Settings
# exclusive inclusive rows x rows loops node
1. 20.931 13,320.526 ↑ 272.7 2,760 1

GroupAggregate (cost=24,133,666.06..26,093,129.72 rows=752,561 width=40) (actual time=13,123.928..13,320.526 rows=2,760 loops=1)

  • Group Key: rw.id
2. 57.737 13,299.595 ↑ 281.7 2,773 1

Merge Join (cost=24,133,666.06..24,196,533.54 rows=781,052 width=50) (actual time=13,123.840..13,299.595 rows=2,773 loops=1)

  • Merge Cond: (rw.id = rwgis.entityid)
3. 0.383 13,117.204 ↑ 272.7 2,760 1

Unique (cost=24,133,665.63..24,137,428.44 rows=752,561 width=4) (actual time=13,116.454..13,117.204 rows=2,760 loops=1)

4. 1.135 13,116.821 ↑ 271.4 2,773 1

Sort (cost=24,133,665.63..24,135,547.03 rows=752,561 width=4) (actual time=13,116.453..13,116.821 rows=2,773 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 226kB
5. 0.214 13,115.686 ↑ 271.4 2,773 1

Result (cost=117,582.87..24,049,920.22 rows=752,561 width=4) (actual time=6,160.513..13,115.686 rows=2,773 loops=1)

  • One-Time Filter: ((to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
6. 0.542 13,115.472 ↑ 271.4 2,773 1

Hash Left Join (cost=117,582.87..24,049,920.22 rows=752,561 width=4) (actual time=6,160.500..13,115.472 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestownercontractororganisationid = c.id)
7. 0.997 13,112.692 ↑ 271.4 2,773 1

Hash Join (cost=117,338.17..24,040,175.53 rows=752,561 width=8) (actual time=6,158.231..13,112.692 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestpsmorganisationid = psm.id)
8. 229.575 13,110.562 ↑ 271.4 2,773 1

Hash Join (cost=117,093.47..24,030,430.85 rows=752,561 width=12) (actual time=6,157.068..13,110.562 rows=2,773 loops=1)

  • Hash Cond: (rw.actualroadworkrequestdatesid = ad.id)
9. 11.114 7,105.601 ↑ 279.9 2,802 1

Hash Join (cost=4,655.65..23,891,638.21 rows=784,143 width=16) (actual time=77.263..7,105.601 rows=2,802 loops=1)

  • Hash Cond: ((rw.roadworkstatustypeid = rs.id) AND (rw.roadworkrequestownerorganisationid = ownr.id))
10. 41.226 7,053.376 ↑ 279.9 2,802 1

Merge Join (cost=61.79..23,820,452.99 rows=784,143 width=24) (actual time=35.785..7,053.376 rows=2,802 loops=1)

  • Merge Cond: (rw.id = gis.entityid)
11. 3,822.228 6,944.982 ↑ 279.9 2,789 1

Index Scan using pk_request on roadworkrequest rw (cost=0.42..23,780,884.45 rows=780,535 width=24) (actual time=35.252..6,944.982 rows=2,789 loops=1)

  • Filter: ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
  • Rows Removed by Filter: 1038128
12.          

SubPlan (forIndex Scan)

13. 1,040.917 3,122.751 ↓ 0.0 0 1,040,917

Hash Join (cost=4.46..49.55 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1,040,917)

  • Hash Cond: (uo.id = c_1.organisationid)
14. 0.000 0.000 ↑ 2,550.0 1 1,039,350

Seq Scan on userorgids uo (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.000..0.000 rows=1 loops=1,039,350)

15. 1,040.917 2,081.834 ↑ 1.0 1 1,040,917

Hash (cost=4.45..4.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,040,917)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 1,040.917 1,040.917 ↑ 1.0 1 1,040,917

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,040,917)

  • Index Cond: (roadworkrequestid = rw.id)
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Seq Scan on userorgids uo_1 (cost=0.00..41.88 rows=13 width=0) (never executed)

  • Filter: (id = rw.roadworkrequestownerorganisationid)
18. 0.003 0.003 ↑ 2,550.0 1 1

Seq Scan on userorgids uo_2 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.003..0.003 rows=1 loops=1)

19. 67.168 67.168 ↑ 1.0 1,045,497 1

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis (cost=0.42..27,163.27 rows=1,045,523 width=4) (actual time=0.013..67.168 rows=1,045,497 loops=1)

  • Heap Fetches: 0
20. 25.994 41.111 ↑ 1.0 140,852 1

Hash (cost=1,930.08..1,930.08 rows=140,852 width=8) (actual time=41.111..41.111 rows=140,852 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3773kB
21. 8.435 15.117 ↑ 1.0 140,852 1

Nested Loop (cost=0.28..1,930.08 rows=140,852 width=8) (actual time=0.022..15.117 rows=140,852 loops=1)

22. 0.558 0.558 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.010..0.558 rows=6,124 loops=1)

  • Heap Fetches: 0
23. 6.112 6.124 ↑ 1.0 23 6,124

Materialize (cost=0.00..1.35 rows=23 width=4) (actual time=0.000..0.001 rows=23 loops=6,124)

24. 0.012 0.012 ↑ 1.0 23 1

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

25. 349.090 5,775.386 ↓ 1.0 2,259,470 1

Hash (cost=75,493.05..75,493.05 rows=2,251,822 width=4) (actual time=5,775.386..5,775.386 rows=2,259,470 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3506kB
26. 5,426.296 5,426.296 ↓ 1.0 2,259,470 1

Seq Scan on roadworkrequestdates ad (cost=0.00..75,493.05 rows=2,251,822 width=4) (actual time=6.488..5,426.296 rows=2,259,470 loops=1)

  • 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: 86852
27. 0.656 1.133 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=1.133..1.133 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
28. 0.477 0.477 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation psm (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.019..0.477 rows=6,124 loops=1)

  • Heap Fetches: 0
29. 0.963 2.238 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=2.238..2.238 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
30. 1.275 1.275 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation c (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.012..1.275 rows=6,124 loops=1)

  • Heap Fetches: 0
31. 124.654 124.654 ↑ 1.0 1,045,497 1

Index Scan using ix_geometryroadworkid on geometryroadwork rwgis (cost=0.42..39,273.77 rows=1,045,523 width=50) (actual time=0.017..124.654 rows=1,045,497 loops=1)