explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G54U

Settings
# exclusive inclusive rows x rows loops node
1. 20.706 14,067.709 ↑ 272.4 2,760 1

GroupAggregate (cost=24,133,536.21..26,091,374.21 rows=751,923 width=40) (actual time=13,837.207..14,067.709 rows=2,760 loops=1)

  • Group Key: rw.id
2. 57.907 14,047.003 ↑ 281.4 2,773 1

Merge Join (cost=24,133,536.21..24,196,385.91 rows=780,390 width=50) (actual time=13,837.046..14,047.003 rows=2,773 loops=1)

  • Merge Cond: (rw.id = rwgis.entityid)
3. 0.403 13,830.959 ↑ 272.4 2,760 1

Unique (cost=24,133,535.78..24,137,295.40 rows=751,923 width=4) (actual time=13,830.231..13,830.959 rows=2,760 loops=1)

4. 1.087 13,830.556 ↑ 271.2 2,773 1

Sort (cost=24,133,535.78..24,135,415.59 rows=751,923 width=4) (actual time=13,830.230..13,830.556 rows=2,773 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 226kB
5. 0.246 13,829.469 ↑ 271.2 2,773 1

Result (cost=117,551.01..24,049,864.25 rows=751,923 width=4) (actual time=6,241.651..13,829.469 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.531 13,829.223 ↑ 271.2 2,773 1

Hash Left Join (cost=117,551.01..24,049,864.25 rows=751,923 width=4) (actual time=6,241.596..13,829.223 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestownercontractororganisationid = c.id)
7. 0.952 13,827.355 ↑ 271.2 2,773 1

Hash Join (cost=117,306.30..24,040,127.61 rows=751,923 width=8) (actual time=6,240.226..13,827.355 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestpsmorganisationid = psm.id)
8. 270.939 13,825.070 ↑ 271.2 2,773 1

Hash Join (cost=117,061.61..24,030,390.98 rows=751,923 width=12) (actual time=6,238.852..13,825.070 rows=2,773 loops=1)

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

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

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

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

  • Merge Cond: (rw.id = gis.entityid)
11. 4,406.305 7,529.071 ↑ 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=37.225..7,529.071 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.015 0.015 ↑ 2,550.0 1 1

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

19. 75.827 75.827 ↑ 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.240..75.827 rows=1,045,497 loops=1)

  • Heap Fetches: 0
20. 23.977 39.142 ↑ 1.0 140,852 1

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

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

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

22. 0.522 0.522 ↑ 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.015..0.522 rows=6,124 loops=1)

  • Heap Fetches: 0
23. 6.114 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.010 0.010 ↑ 1.0 23 1

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

25. 354.402 5,856.551 ↓ 1.0 2,259,470 1

Hash (cost=75,493.05..75,493.05 rows=2,249,913 width=4) (actual time=5,856.551..5,856.551 rows=2,259,470 loops=1)

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

Seq Scan on roadworkrequestdates ad (cost=0.00..75,493.05 rows=2,249,913 width=4) (actual time=6.534..5,502.149 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.621 1.333 ↑ 1.0 6,124 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
28. 0.712 0.712 ↑ 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.221..0.712 rows=6,124 loops=1)

  • Heap Fetches: 0
29. 0.793 1.337 ↑ 1.0 6,124 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
30. 0.544 0.544 ↑ 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.013..0.544 rows=6,124 loops=1)

  • Heap Fetches: 0
31. 158.137 158.137 ↑ 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.021..158.137 rows=1,045,497 loops=1)