explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UApv

Settings
# exclusive inclusive rows x rows loops node
1. 10.977 280,308.135 ↑ 1.0 1 1

Aggregate (cost=10,673,010.41..10,673,010.51 rows=1 width=32) (actual time=280,308.135..280,308.135 rows=1 loops=1)

2. 1.129 280,297.158 ↑ 1.6 1,126 1

Subquery Scan on q (cost=10,672,120.47..10,673,005.91 rows=1,798 width=44) (actual time=280,241.651..280,297.158 rows=1,126 loops=1)

3. 53.161 280,296.029 ↑ 1.6 1,126 1

GroupAggregate (cost=10,672,120.47..10,672,826.11 rows=1,798 width=255) (actual time=280,241.620..280,296.029 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.          

CTE boundaries

5. 0.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..1,218.40 rows=3,404 width=78,632) (never executed)

6. 38.498 280,242.868 ↓ 1.0 7,996 1

Sort (cost=10,670,902.07..10,670,921.27 rows=7,681 width=211) (actual time=280,241.296..280,242.868 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: 2,398kB
7. 8.809 280,204.370 ↓ 1.0 7,996 1

Nested Loop Left Join (cost=4,186.80..10,670,406.38 rows=7,681 width=211) (actual time=190.975..280,204.370 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
8. 558.091 280,195.561 ↓ 1.0 7,996 1

Hash Join (cost=4,186.80..10,669,638.28 rows=7,681 width=211) (actual time=190.969..280,195.561 rows=7,996 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 561.656 279,612.087 ↓ 2.2 2,670,371 1

Nested Loop (cost=0.86..10,662,235.02 rows=1,225,566 width=195) (actual time=47.704..279,612.087 rows=2,670,371 loops=1)

10. 12,519.053 12,519.053 ↓ 2.7 4,871 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..6,935.37 rows=1,798 width=187) (actual time=0.428..12,519.053 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
11. 266,531.378 266,531.378 ↑ 9.6 548 4,871

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,401.10 rows=5,251 width=16) (actual time=4.642..54.718 rows=548 loops=4,871)

  • Index Cond: (link_id = v_transit_roads.link_id)
12. 1.509 25.383 ↓ 1.1 4,113 1

Hash (cost=3,811.61..3,811.61 rows=3,652 width=32) (actual time=25.382..25.383 rows=4,113 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 322kB
13. 23.874 23.874 ↓ 1.1 4,113 1

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..3,811.61 rows=3,652 width=32) (actual time=0.120..23.874 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
14. 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