explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A1Sn

Settings
# exclusive inclusive rows x rows loops node
1. 4.046 3,525.179 ↑ 1.0 1 1

Aggregate (cost=30,836,836.00..30,836,836.10 rows=1 width=32) (actual time=3,525.179..3,525.179 rows=1 loops=1)

  • Functions: 32
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.219 ms, Inlining 22.203 ms, Optimization 470.613 ms, Emission 296.721 ms, Total 794.756 ms
2. 0.715 3,521.133 ↑ 4.7 1,126 1

Subquery Scan on q (cost=30,834,059.63..30,836,820.38 rows=5,247 width=44) (actual time=3,478.937..3,521.133 rows=1,126 loops=1)

3. 40.559 3,520.418 ↑ 4.7 1,126 1

GroupAggregate (cost=30,834,059.63..30,836,295.68 rows=5,247 width=255) (actual time=3,478.926..3,520.418 rows=1,126 loops=1)

  • Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
4. 15.383 3,479.859 ↑ 3.2 7,996 1

Sort (cost=30,834,059.63..30,834,123.71 rows=25,630 width=211) (actual time=3,478.686..3,479.859 rows=7,996 loops=1)

  • Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
  • Sort Method: quicksort Memory: 2398kB
5. 3.976 3,464.476 ↑ 3.2 7,996 1

Nested Loop Left Join (cost=4,764.44..30,832,182.81 rows=25,630 width=211) (actual time=796.409..3,464.476 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
6. 299.951 3,460.500 ↑ 3.2 7,996 1

Hash Join (cost=4,764.44..30,829,619.81 rows=25,630 width=211) (actual time=796.404..3,460.500 rows=7,996 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
7. 262.304 3,154.808 ↑ 1.3 2,670,371 1

Nested Loop (cost=0.86..30,815,467.31 rows=3,576,498 width=195) (actual time=790.625..3,154.808 rows=2,670,371 loops=1)

8. 822.329 822.329 ↑ 1.1 4,871 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..136,615.73 rows=5,247 width=187) (actual time=790.573..822.329 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
9. 2,070.175 2,070.175 ↑ 9.5 548 4,871

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,328.33 rows=5,186 width=16) (actual time=0.006..0.425 rows=548 loops=4,871)

  • Index Cond: (link_id = v_transit_roads.link_id)
10. 1.093 5.741 ↑ 1.0 4,113 1

Hash (cost=4,335.54..4,335.54 rows=4,176 width=32) (actual time=5.740..5.741 rows=4,113 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
11. 4.648 4.648 ↑ 1.0 4,113 1

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..4,335.54 rows=4,176 width=32) (actual time=0.020..4.648 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
12. 0.000 0.000 ↓ 0.0 0 7,996

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=7,996)

  • One-Time Filter: false
Planning time : 1.532 ms