explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oj5y

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 87,452.711 ↑ 200.0 1 1

Hash Left Join (cost=492,112.16..492,123.66 rows=200 width=242) (actual time=87,452.708..87,452.711 rows=1 loops=1)

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

CTE segments

3. 2,732.822 44,870.040 ↓ 12.2 7,730,523 1

Gather (cost=17,007.41..444,125.02 rows=635,393 width=57) (actual time=1,129.907..44,870.040 rows=7,730,523 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
4. 18,879.656 42,137.218 ↓ 29.2 7,730,523 1

Parallel Hash Left Join (cost=16,007.41..379,585.72 rows=264,747 width=57) (actual time=1,129.494..42,137.218 rows=7,730,523 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
5. 10,317.803 22,855.955 ↓ 29.2 7,730,523 1

Parallel Hash Left Join (cost=10,151.70..366,546.80 rows=264,747 width=60) (actual time=725.354..22,855.955 rows=7,730,523 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
6. 11,081.401 11,881.285 ↓ 29.2 7,730,523 1

Nested Loop (cost=1.00..355,701.13 rows=264,747 width=41) (actual time=42.095..11,881.285 rows=7,730,523 loops=1)

7. 796.361 796.361 ↓ 2.5 1,975 1

Parallel Index Only Scan using idx_v_transit_roads_agg on v_transit_roads (cost=0.42..30,159.92 rows=784 width=33) (actual time=39.377..796.361 rows=1,975 loops=1)

  • Filter: ((street_name)::text = ANY ('{"Warrego Hwy"}'::text[]))
  • Rows Removed by Filter: 770928
  • Heap Fetches: 0
8. 3.523 3.523 ↑ 2.5 3,914 1,975

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..317.82 rows=9,741 width=16) (actual time=0.143..3.523 rows=3,914 loops=1,975)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
9. 234.963 656.867 ↓ 2.4 300,768 1

Parallel Hash (cost=8,584.20..8,584.20 rows=125,320 width=19) (actual time=656.867..656.867 rows=300,768 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20608kB
10. 421.904 421.904 ↓ 2.4 300,768 1

Parallel Seq Scan on tblmovements (cost=0.00..8,584.20 rows=125,320 width=19) (actual time=0.016..421.904 rows=300,768 loops=1)

11. 179.758 401.607 ↓ 1.7 298,650 1

Parallel Hash (cost=3,659.76..3,659.76 rows=175,676 width=12) (actual time=401.607..401.607 rows=298,650 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 18112kB
12. 221.849 221.849 ↓ 1.7 298,650 1

Parallel Seq Scan on v_transit_movement_extras (cost=0.00..3,659.76 rows=175,676 width=12) (actual time=0.014..221.849 rows=298,650 loops=1)

13.          

CTE roadssegments

14. 13,066.368 73,240.149 ↑ 32.2 1,975 1

HashAggregate (cost=23,827.24..24,780.32 rows=63,539 width=246) (actual time=73,239.090..73,240.149 rows=1,975 loops=1)

  • Group Key: segments.street_name, segments.link_id, segments.road_km, segments.road_speed
15. 60,173.781 60,173.781 ↓ 12.2 7,730,523 1

CTE Scan on segments (cost=0.00..12,707.86 rows=635,393 width=246) (actual time=1,129.909..60,173.781 rows=7,730,523 loops=1)

16.          

CTE roads

17. 0.694 73,241.572 ↑ 200.0 1 1

HashAggregate (cost=3,494.64..3,500.14 rows=200 width=238) (actual time=73,241.570..73,241.572 rows=1 loops=1)

  • Group Key: roadssegments.street_name
18. 73,240.878 73,240.878 ↑ 32.2 1,975 1

CTE Scan on roadssegments (cost=0.00..1,270.78 rows=63,539 width=238) (actual time=73,239.091..73,240.878 rows=1,975 loops=1)

19.          

CTE roadtrips

20. 9,705.200 14,150.665 ↑ 2.7 23,437 1

HashAggregate (cost=17,473.31..18,108.70 rows=63,539 width=234) (actual time=14,123.852..14,150.665 rows=23,437 loops=1)

  • Group Key: segments_1.street_name, segments_1.trip_id, segments_1.trip_transport_costs
21. 4,445.465 4,445.465 ↓ 12.2 7,730,523 1

CTE Scan on segments segments_1 (cost=0.00..12,707.86 rows=635,393 width=234) (actual time=16.277..4,445.465 rows=7,730,523 loops=1)

22.          

CTE roadcosts

23. 29.237 14,211.116 ↑ 200.0 1 1

HashAggregate (cost=1,588.48..1,591.48 rows=200 width=222) (actual time=14,211.115..14,211.116 rows=1 loops=1)

  • Group Key: roadtrips.street_name
24. 14,181.879 14,181.879 ↑ 2.7 23,437 1

CTE Scan on roadtrips (cost=0.00..1,270.78 rows=63,539 width=226) (actual time=14,123.854..14,181.879 rows=23,437 loops=1)

25. 73,241.574 73,241.574 ↑ 200.0 1 1

CTE Scan on roads (cost=0.00..4.00 rows=200 width=238) (actual time=73,241.572..73,241.574 rows=1 loops=1)

26. 0.004 14,211.122 ↑ 200.0 1 1

Hash (cost=4.00..4.00 rows=200 width=222) (actual time=14,211.122..14,211.122 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 14,211.118 14,211.118 ↑ 200.0 1 1

CTE Scan on roadcosts (cost=0.00..4.00 rows=200 width=222) (actual time=14,211.117..14,211.118 rows=1 loops=1)