explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7JTQ

Settings
# exclusive inclusive rows x rows loops node
1. 6.978 4,357.947 ↑ 1.0 1 1

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

2. 1.240 4,350.969 ↑ 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=4,295.702..4,350.969 rows=1,126 loops=1)

3. 52.910 4,349.729 ↑ 1.6 1,126 1

GroupAggregate (cost=10,672,120.47..10,672,826.11 rows=1,798 width=255) (actual time=4,295.640..4,349.729 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. 25.949 4,296.819 ↓ 1.0 7,996 1

Sort (cost=10,670,902.07..10,670,921.27 rows=7,681 width=211) (actual time=4,295.348..4,296.819 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
7. 6.108 4,270.870 ↓ 1.0 7,996 1

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

  • Join Filter: NULL::boolean
8. 405.922 4,264.762 ↓ 1.0 7,996 1

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

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 535.077 3,670.597 ↓ 2.2 2,670,371 1

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

10. 66.790 66.790 ↓ 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.506..66.790 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
11. 3,068.730 3,068.730 ↑ 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=0.013..0.630 rows=548 loops=4,871)

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

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 322kB
13. 186.136 186.136 ↓ 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.071..186.136 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
Planning time : 1,047.406 ms