explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IQ6Q

Settings
# exclusive inclusive rows x rows loops node
1. 6.014 254,786.643 ↑ 1.0 1 1

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

2. 1.129 254,780.629 ↑ 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=254,726.874..254,780.629 rows=1,126 loops=1)

3. 52.137 254,779.500 ↑ 1.6 1,126 1

GroupAggregate (cost=10,672,120.47..10,672,826.11 rows=1,798 width=255) (actual time=254,726.823..254,779.500 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. 26.919 254,727.363 ↓ 1.0 7,996 1

Sort (cost=10,670,902.07..10,670,921.27 rows=7,681 width=211) (actual time=254,726.526..254,727.363 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. 7.695 254,700.444 ↓ 1.0 7,996 1

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

  • Join Filter: NULL::boolean
8. 476.481 254,692.749 ↓ 1.0 7,996 1

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

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 557.674 253,936.234 ↓ 2.2 2,670,371 1

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

10. 9,857.786 9,857.786 ↓ 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=36.164..9,857.786 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
11. 243,520.774 243,520.774 ↑ 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.334..49.994 rows=548 loops=4,871)

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

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 322kB
13. 277.341 277.341 ↓ 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=33.159..277.341 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 : 386.945 ms