explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wu6g

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 809.626 ↑ 4.3 47 1

Hash Left Join (cost=2,024,490.59..2,024,502.09 rows=200 width=242) (actual time=809.585..809.626 rows=47 loops=1)

  • Hash 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..909.04 rows=3,404 width=78,632) (never executed)

4.          

CTE segments

5. 0.000 800.852 ↑ 1,293.4 3,167 1

Nested Loop Left Join (cost=93,974.48..1,714,302.95 rows=4,096,200 width=53) (actual time=786.297..800.852 rows=3,167 loops=1)

  • Join Filter: NULL::boolean
6. 52.604 802.888 ↑ 1,293.4 3,167 1

Gather (cost=93,974.48..1,642,619.45 rows=4,096,200 width=57) (actual time=786.294..802.888 rows=3,167 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 4.108 750.284 ↑ 1,668.4 792 4 / 4

Hash Join (cost=92,974.48..1,231,999.45 rows=1,321,355 width=57) (actual time=747.887..750.284 rows=792 loops=4)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
8. 0.087 159.901 ↑ 1,668.4 792 4 / 4

Nested Loop (cost=45,101.53..1,180,657.91 rows=1,321,355 width=32) (actual time=157.922..159.901 rows=792 loops=4)

9. 5.788 159.032 ↑ 648.5 2 4 / 4

Merge Join (cost=45,100.95..50,166.68 rows=1,297 width=32) (actual time=157.516..159.032 rows=2 loops=4)

  • Merge Cond: (road_movements.trip_id = road_segments_1.trip_id)
10. 36.985 152.461 ↑ 1.4 136,726 4 / 4

Sort (cost=45,099.63..45,569.57 rows=187,976 width=24) (actual time=149.399..152.461 rows=136,726 loops=4)

  • Sort Key: road_movements.trip_id
  • Sort Method: quicksort Memory: 21657kB
11. 115.476 115.476 ↑ 1.3 145,681 4 / 4

Parallel Seq Scan on road_movements (cost=0.00..28,632.76 rows=187,976 width=24) (actual time=0.112..115.476 rows=145,681 loops=4)

12. 0.783 0.783 ↑ 574.3 7 4 / 4

Index Only Scan using idx_road_segments_key on road_segments road_segments_1 (cost=0.57..4,104.19 rows=4,020 width=8) (actual time=0.537..0.783 rows=7 loops=4)

  • Index Cond: (link_id = 1201710493)
  • Heap Fetches: 7
13. 0.782 0.782 ↑ 11.4 452 7 / 4

Index Scan using idx_road_segments_trip_id on road_segments (cost=0.57..819.90 rows=5,172 width=16) (actual time=0.335..0.447 rows=452 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
14. 130.563 586.275 ↑ 1.0 871,109 4 / 4

Hash (cost=36,984.09..36,984.09 rows=871,109 width=33) (actual time=586.275..586.275 rows=871,109 loops=4)

  • Buckets: 1048576 Batches: 1 Memory Usage: 66299kB
15. 455.712 455.712 ↑ 1.0 871,109 4 / 4

Seq Scan on v_transit_roads (cost=0.00..36,984.09 rows=871,109 width=33) (actual time=0.076..455.712 rows=871,109 loops=4)

16. 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
17.          

CTE roadssegments

18. 3.817 805.617 ↑ 295.1 1,388 1

HashAggregate (cost=153,607.50..159,751.80 rows=409,620 width=246) (actual time=803.503..805.617 rows=1,388 loops=1)

  • Group Key: segments.street_name, segments.link_id, segments.road_km, segments.road_speed
19. 801.800 801.800 ↑ 1,293.4 3,167 1

CTE Scan on segments (cost=0.00..81,924.00 rows=4,096,200 width=246) (actual time=786.299..801.800 rows=3,167 loops=1)

20.          

CTE roads

21. 0.589 806.604 ↑ 4.3 47 1

HashAggregate (cost=22,529.10..22,534.60 rows=200 width=238) (actual time=806.587..806.604 rows=47 loops=1)

  • Group Key: roadssegments.street_name
22. 806.015 806.015 ↑ 295.1 1,388 1

CTE Scan on roadssegments (cost=0.00..8,192.40 rows=409,620 width=238) (actual time=803.504..806.015 rows=1,388 loops=1)

23.          

CTE roadtrips

24. 2.647 2.857 ↑ 3,531.2 116 1

HashAggregate (cost=112,645.50..116,741.70 rows=409,620 width=230) (actual time=1.104..2.857 rows=116 loops=1)

  • Group Key: segments_1.street_name, segments_1.trip_id, segments_1.trip_transport_costs
25. 0.210 0.210 ↑ 1,293.4 3,167 1

CTE Scan on segments segments_1 (cost=0.00..81,924.00 rows=4,096,200 width=230) (actual time=0.001..0.210 rows=3,167 loops=1)

26.          

CTE roadcosts

27. 0.059 2.951 ↑ 4.3 47 1

HashAggregate (cost=10,240.50..10,244.00 rows=200 width=222) (actual time=2.941..2.951 rows=47 loops=1)

  • Group Key: roadtrips.street_name
28. 2.892 2.892 ↑ 3,531.2 116 1

CTE Scan on roadtrips (cost=0.00..8,192.40 rows=409,620 width=222) (actual time=1.105..2.892 rows=116 loops=1)

29. 806.616 806.616 ↑ 4.3 47 1

CTE Scan on roads (cost=0.00..4.00 rows=200 width=238) (actual time=806.589..806.616 rows=47 loops=1)

30. 0.012 2.979 ↑ 4.3 46 1

Hash (cost=4.00..4.00 rows=200 width=222) (actual time=2.979..2.979 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
31. 2.967 2.967 ↑ 4.3 47 1

CTE Scan on roadcosts (cost=0.00..4.00 rows=200 width=222) (actual time=2.943..2.967 rows=47 loops=1)

Planning time : 2.588 ms