explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8mn7

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 747.287 ↑ 66.7 3 1

Sort (cost=1,761,928.76..1,761,929.26 rows=200 width=36) (actual time=747.287..747.287 rows=3 loops=1)

  • Sort Key: road_movements.commodity
  • Sort Method: quicksort Memory: 25kB
2. 0.050 747.266 ↑ 66.7 3 1

HashAggregate (cost=1,761,915.61..1,761,921.11 rows=200 width=36) (actual time=747.263..747.266 rows=3 loops=1)

  • Group Key: road_movements.commodity
3. 4.200 747.216 ↑ 83,246.4 7 1

HashAggregate (cost=1,735,692.99..1,745,890.67 rows=582,725 width=52) (actual time=744.131..747.216 rows=7 loops=1)

  • Group Key: road_movements.trip_id
4.          

CTE boundaries

5. 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)

6. 0.327 743.016 ↑ 1,293.4 3,167 1

Nested Loop Left Join (cost=93,974.48..1,683,581.45 rows=4,096,200 width=40) (actual time=722.396..743.016 rows=3,167 loops=1)

  • Join Filter: NULL::boolean
7. 78.172 742.689 ↑ 1,293.4 3,167 1

Gather (cost=93,974.48..1,642,619.45 rows=4,096,200 width=40) (actual time=722.394..742.689 rows=3,167 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
8. 5.158 664.517 ↑ 1,668.4 792 4 / 4

Hash Join (cost=92,974.48..1,231,999.45 rows=1,321,355 width=40) (actual time=660.908..664.517 rows=792 loops=4)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
9. 0.088 165.997 ↑ 1,668.4 792 4 / 4

Nested Loop (cost=45,101.53..1,180,657.91 rows=1,321,355 width=48) (actual time=162.746..165.997 rows=792 loops=4)

10. 5.710 165.246 ↑ 648.5 2 4 / 4

Merge Join (cost=45,100.95..50,166.68 rows=1,297 width=48) (actual time=162.473..165.246 rows=2 loops=4)

  • Merge Cond: (road_movements.trip_id = road_segments_1.trip_id)
11. 41.969 158.838 ↑ 1.4 136,726 4 / 4

Sort (cost=45,099.63..45,569.57 rows=187,976 width=40) (actual time=155.773..158.838 rows=136,726 loops=4)

  • Sort Key: road_movements.trip_id
  • Sort Method: quicksort Memory: 22,981kB
12. 116.869 116.869 ↑ 1.3 145,681 4 / 4

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

13. 0.698 0.698 ↑ 670.0 6 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.330..0.698 rows=6 loops=4)

  • Index Cond: (link_id = 1,201,710,493)
  • Heap Fetches: 7
14. 0.663 0.663 ↑ 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.274..0.379 rows=452 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
15. 92.156 493.362 ↑ 1.0 871,109 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 42,220kB
16. 401.206 401.206 ↑ 1.0 871,109 4 / 4

Seq Scan on v_transit_roads (cost=0.00..36,984.09 rows=871,109 width=8) (actual time=0.074..401.206 rows=871,109 loops=4)

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