explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dit6

Settings
# exclusive inclusive rows x rows loops node
1. 4.737 22,488.816 ↑ 1.0 1 1

Aggregate (cost=36,283,997.30..36,283,997.50 rows=1 width=32) (actual time=22,488.816..22,488.816 rows=1 loops=1)

  • Functions: 32
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.538 ms, Inlining 23.862 ms, Optimization 466.797 ms, Emission 285.609 ms, Total 781.806 ms"Execution Time: 22494.682 ms
2. 0.836 22,484.079 ↑ 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=22,440.143..22,484.079 rows=1,126 loops=1)

3. 42.136 22,483.243 ↑ 4.7 1,126 1

GroupAggregate (cost=36,280,171.53..36,282,932.28 rows=5,247 width=255) (actual time=22,440.131..22,483.243 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. 19.016 22,441.107 ↑ 3.2 7,996 1

Sort (cost=36,280,171.53..36,280,235.61 rows=25,630 width=211) (actual time=22,439.847..22,441.107 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. 5.279 22,422.091 ↑ 3.2 7,996 1

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

  • Join Filter: NULL::boolean
6. 361.160 22,416.812 ↑ 3.2 7,996 1

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

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
7. 298.713 22,049.299 ↑ 1.3 2,670,371 1

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

8. 824.770 824.770 ↑ 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=777.446..824.770 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
9. 20,925.816 20,925.816 ↑ 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.253..4.296 rows=548 loops=4,871)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
11. 5.252 5.252 ↑ 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.039..5.252 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