explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mscM

Settings
# exclusive inclusive rows x rows loops node
1. 4.133 732.472 ↑ 83,246.4 7 1

HashAggregate (cost=1,715,211.99..1,726,866.49 rows=582,725 width=125) (actual time=729.145..732.472 rows=7 loops=1)

  • Group Key: road_movements.trip_id, (road_movements.mov_cst * '2'::real)
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. 0.466 728.339 ↑ 1,293.4 3,167 1

Nested Loop Left Join (cost=93,974.48..1,693,821.95 rows=4,096,200 width=121) (actual time=696.311..728.339 rows=3,167 loops=1)

  • Join Filter: NULL::boolean
5. 52.373 727.873 ↑ 1,293.4 3,167 1

Gather (cost=93,974.48..1,642,619.45 rows=4,096,200 width=117) (actual time=696.308..727.873 rows=3,167 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
6. 4.027 675.500 ↑ 1,668.4 792 4 / 4

Hash Join (cost=92,974.48..1,231,999.45 rows=1,321,355 width=117) (actual time=671.269..675.500 rows=792 loops=4)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
7. 0.121 197.930 ↑ 1,668.4 792 4 / 4

Nested Loop (cost=45,101.53..1,180,657.91 rows=1,321,355 width=125) (actual time=194.022..197.930 rows=792 loops=4)

8. 10.048 197.625 ↑ 648.5 2 4 / 4

Merge Join (cost=45,100.95..50,166.68 rows=1,297 width=125) (actual time=193.982..197.625 rows=2 loops=4)

  • Merge Cond: (road_movements.trip_id = road_segments_1.trip_id)
9. 69.801 187.445 ↑ 1.4 136,726 4 / 4

Sort (cost=45,099.63..45,569.57 rows=187,976 width=117) (actual time=184.430..187.445 rows=136,726 loops=4)

  • Sort Key: road_movements.trip_id
  • Sort Method: quicksort Memory: 53,879kB
10. 117.644 117.644 ↑ 1.3 145,681 4 / 4

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

11. 0.132 0.132 ↑ 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.106..0.132 rows=7 loops=4)

  • Index Cond: (link_id = 1,201,710,493)
  • Heap Fetches: 7
12. 0.184 0.184 ↑ 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.025..0.105 rows=452 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
13. 89.441 473.543 ↑ 1.0 871,109 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 42,220kB
14. 384.102 384.102 ↑ 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.060..384.102 rows=871,109 loops=4)

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