explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UQjY

Settings
# exclusive inclusive rows x rows loops node
1. 6.931 6,232.291 ↑ 1.0 1 1

Aggregate (cost=4,445,370.74..4,445,370.75 rows=1 width=32) (actual time=6,232.291..6,232.291 rows=1 loops=1)

2. 0.594 6,225.360 ↑ 1.7 1,126 1

Subquery Scan on q (cost=4,444,697.22..4,445,365.94 rows=1,918 width=44) (actual time=6,167.720..6,225.360 rows=1,126 loops=1)

3. 57.190 6,224.766 ↑ 1.7 1,126 1

GroupAggregate (cost=4,444,697.22..4,445,346.76 rows=1,918 width=253) (actual time=6,167.707..6,224.766 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..909.04 rows=3,404 width=78,632) (never executed)

6. 9.953 6,167.576 ↑ 1.2 7,996 1

Sort (cost=4,443,788.18..4,443,811.82 rows=9,456 width=209) (actual time=6,167.323..6,167.576 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. 1.290 6,157.623 ↑ 1.2 7,996 1

Nested Loop Left Join (cost=6,828.72..4,443,163.75 rows=9,456 width=209) (actual time=497.996..6,157.623 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
8. 540.475 6,156.333 ↑ 1.2 7,996 1

Hash Join (cost=6,828.72..4,443,069.19 rows=9,456 width=209) (actual time=497.994..6,156.333 rows=7,996 loops=1)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
9. 740.155 5,574.800 ↓ 1.7 7,329,912 1

Nested Loop (cost=1.00..4,424,967.31 rows=4,294,897 width=32) (actual time=1.237..5,574.800 rows=7,329,912 loops=1)

10. 26.548 26.548 ↑ 1.0 4,113 1

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..3,994.02 rows=4,215 width=32) (actual time=0.439..26.548 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
11. 4,808.097 4,808.097 ↑ 2.9 1,782 4,113

Index Scan using idx_road_segments_trip_id on road_segments (cost=0.57..997.15 rows=5,172 width=16) (actual time=0.327..1.169 rows=1,782 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
12. 1.903 41.058 ↓ 2.5 4,871 1

Hash (cost=6,803.75..6,803.75 rows=1,918 width=185) (actual time=41.058..41.058 rows=4,871 loops=1)

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 864kB
13. 39.155 39.155 ↓ 2.5 4,871 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..6,803.75 rows=1,918 width=185) (actual time=1.639..39.155 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
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