explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KhRp

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 125,754.763 ↑ 4.3 47 1

Merge Left Join (cost=24,131,890.39..24,131,912.39 rows=200 width=242) (actual time=125,754.709..125,754.763 rows=47 loops=1)

  • Merge Cond: ((roads.street_name)::text = (roadcosts.street_name)::text)
2.          

CTE boundaries

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..1,218.40 rows=3,404 width=78,632) (never executed)

4.          

CTE segments

5. 0.000 125,731.315 ↑ 1,689.5 3,167 1

Nested Loop Left Join (cost=143,930.16..21,511,441.95 rows=5,350,541 width=53) (actual time=4,033.345..125,731.315 rows=3,167 loops=1)

  • Join Filter: NULL::boolean
6. 294.182 125,787.410 ↑ 1,689.5 3,167 1

Gather (cost=143,930.16..20,936,258.79 rows=5,350,541 width=57) (actual time=4,033.333..125,787.410 rows=3,167 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 5.291 125,493.228 ↑ 2,113.2 633 5 / 5

Parallel Hash Join (cost=142,930.16..20,400,204.69 rows=1,337,635 width=57) (actual time=13,009.700..125,493.228 rows=633 loops=5)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
8. 1.874 124,306.017 ↑ 2,113.2 633 5 / 5

Parallel Hash Join (cost=70,557.29..20,324,320.33 rows=1,337,635 width=32) (actual time=11,825.468..124,306.017 rows=633 loops=5)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 14,480.062 122,857.884 ↑ 42,843.2 633 5 / 5

Parallel Hash Join (cost=5,730.17..20,188,299.04 rows=27,119,777 width=24) (actual time=10,378.087..122,857.884 rows=633 loops=5)

  • Hash Cond: (road_segments.trip_id = road_segments_1.trip_id)
10. 108,377.486 108,377.486 ↑ 1.3 118,754,299 5 / 5

Parallel Seq Scan on road_segments (cost=0.00..19,792,384.00 rows=148,442,880 width=16) (actual time=3.410..108,377.486 rows=118,754,299 loops=5)

11. 0.140 0.336 ↑ 2,188.0 1 5 / 5

Parallel Hash (cost=5,505.90..5,505.90 rows=2,188 width=8) (actual time=0.335..0.336 rows=1 loops=5)

  • Buckets: 8192 Batches: 1 Memory Usage: 128kB
12. 0.170 0.196 ↑ 2,188.0 1 5 / 5

Parallel Bitmap Heap Scan on road_segments road_segments_1 (cost=32.64..5,505.90 rows=2,188 width=8) (actual time=0.193..0.196 rows=1 loops=5)

  • Recheck Cond: (link_id = 1201710493)
  • Heap Blocks: exact=2
13. 0.026 0.026 ↑ 750.1 7 1 / 5

Bitmap Index Scan on idx_road_segments_link_id (cost=0.00..31.33 rows=5,251 width=0) (actual time=0.130..0.130 rows=7 loops=1)

  • Index Cond: (link_id = 1201710493)
14. 67.277 1,446.259 ↑ 1.6 116,545 5 / 5

Parallel Hash (cost=45,559.58..45,559.58 rows=187,976 width=24) (actual time=1,446.259..1,446.259 rows=116,545 loops=5)

  • Buckets: 1048576 Batches: 1 Memory Usage: 40192kB
15. 1,378.982 1,378.982 ↑ 1.6 116,545 5 / 5

Parallel Seq Scan on road_movements (cost=0.00..45,559.58 rows=187,976 width=24) (actual time=19.648..1,378.982 rows=116,545 loops=5)

16. 101.022 1,181.920 ↑ 1.2 174,222 5 / 5

Parallel Hash (cost=50,050.73..50,050.73 rows=217,777 width=33) (actual time=1,181.920..1,181.920 rows=174,222 loops=5)

  • Buckets: 1048576 Batches: 1 Memory Usage: 69888kB
17. 1,080.898 1,080.898 ↑ 1.2 174,222 5 / 5

Parallel Seq Scan on v_transit_roads (cost=0.00..50,050.73 rows=217,777 width=33) (actual time=1.283..1,080.898 rows=174,222 loops=5)

18. 0.000 0.000 ↓ 0.0 0 3,167

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,167)

  • One-Time Filter: false
19.          

CTE roadssegments

20. 11.054 125,744.509 ↑ 385.5 1,388 1

HashAggregate (cost=1,163,742.67..1,219,923.34 rows=535,054 width=246) (actual time=125,737.491..125,744.509 rows=1,388 loops=1)

  • Group Key: segments.street_name, segments.link_id, segments.road_km, segments.road_speed
21. 125,733.455 125,733.455 ↑ 1,689.5 3,167 1

CTE Scan on segments (cost=0.00..1,070,108.20 rows=5,350,541 width=246) (actual time=4,033.351..125,733.455 rows=3,167 loops=1)

22.          

CTE roads

23. 0.850 125,746.192 ↑ 4.3 47 1

HashAggregate (cost=125,737.69..125,761.19 rows=200 width=238) (actual time=125,746.171..125,746.192 rows=47 loops=1)

  • Group Key: roadssegments.street_name
24. 125,745.342 125,745.342 ↑ 385.5 1,388 1

CTE Scan on roadssegments (cost=0.00..107,010.80 rows=535,054 width=238) (actual time=125,737.501..125,745.342 rows=1,388 loops=1)

25.          

CTE roadtrips

26. 7.651 8.098 ↑ 4,612.5 116 1

HashAggregate (cost=1,110,237.26..1,163,742.66 rows=535,054 width=230) (actual time=1.910..8.098 rows=116 loops=1)

  • Group Key: segments_1.street_name, segments_1.trip_id, segments_1.trip_transport_costs
27. 0.447 0.447 ↑ 1,689.5 3,167 1

CTE Scan on segments segments_1 (cost=0.00..1,070,108.20 rows=5,350,541 width=230) (actual time=0.002..0.447 rows=3,167 loops=1)

28.          

CTE roadcosts

29. 0.093 8.281 ↑ 4.3 47 1

HashAggregate (cost=109,686.07..109,707.57 rows=200 width=222) (actual time=8.268..8.281 rows=47 loops=1)

  • Group Key: roadtrips.street_name
30. 8.188 8.188 ↑ 4,612.5 116 1

CTE Scan on roadtrips (cost=0.00..107,010.80 rows=535,054 width=222) (actual time=1.912..8.188 rows=116 loops=1)

31. 0.095 125,746.320 ↑ 4.3 47 1

Sort (cost=47.64..48.14 rows=200 width=238) (actual time=125,746.313..125,746.320 rows=47 loops=1)

  • Sort Key: roads.street_name
  • Sort Method: quicksort Memory: 28kB
32. 125,746.225 125,746.225 ↑ 4.3 47 1

CTE Scan on roads (cost=0.00..40.00 rows=200 width=238) (actual time=125,746.176..125,746.225 rows=47 loops=1)

33. 0.084 8.389 ↑ 4.3 47 1

Sort (cost=47.64..48.14 rows=200 width=222) (actual time=8.385..8.389 rows=47 loops=1)

  • Sort Key: roadcosts.street_name
  • Sort Method: quicksort Memory: 27kB
34. 8.305 8.305 ↑ 4.3 47 1

CTE Scan on roadcosts (cost=0.00..40.00 rows=200 width=222) (actual time=8.272..8.305 rows=47 loops=1)

Planning time : 7.129 ms