explain.depesz.com

PostgreSQL's explain analyze made readable

Result: acng

Settings
# exclusive inclusive rows x rows loops node
1. 6.340 7,280.040 ↑ 1.0 1 1

Aggregate (cost=36,283,997.30..36,283,997.50 rows=1 width=32) (actual time=7,280.039..7,280.040 rows=1 loops=1)

  • Functions: 32
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.290 ms, Inlining 62.037 ms, Optimization 825.987 ms, Emission 583.088 ms, Total 1476.403 ms"Execution Time: 7285.774 ms
2. 1.077 7,273.700 ↑ 4.7 1,126 1

Subquery Scan on q (cost=36,280,171.53..36,283,981.68 rows=5,247 width=44) (actual time=7,178.784..7,273.700 rows=1,126 loops=1)

3. 92.364 7,272.623 ↑ 4.7 1,126 1

GroupAggregate (cost=36,280,171.53..36,282,932.28 rows=5,247 width=255) (actual time=7,178.774..7,272.623 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. 30.178 7,180.259 ↑ 3.2 7,996 1

Sort (cost=36,280,171.53..36,280,235.61 rows=25,630 width=211) (actual time=7,178.553..7,180.259 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. 6.807 7,150.081 ↑ 3.2 7,996 1

Nested Loop Left Join (cost=5,599.64..36,278,294.71 rows=25,630 width=211) (actual time=1,485.804..7,150.081 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
6. 680.451 7,143.274 ↑ 3.2 7,996 1

Hash Join (cost=5,599.64..36,273,168.71 rows=25,630 width=211) (actual time=1,485.799..7,143.274 rows=7,996 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
7. 542.626 6,449.029 ↑ 1.3 2,670,371 1

Nested Loop (cost=0.86..36,258,180.41 rows=3,576,498 width=195) (actual time=1,471.971..6,449.029 rows=2,670,371 loops=1)

8. 1,561.471 1,561.471 ↑ 1.1 4,871 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..137,140.43 rows=5,247 width=187) (actual time=1,471.917..1,561.471 rows=4,871 loops=1)

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

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

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

Hash (cost=4,753.14..4,753.14 rows=4,176 width=32) (actual time=13.793..13.794 rows=4,113 loops=1)

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

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..4,753.14 rows=4,176 width=32) (actual time=0.020..12.650 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