explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x1qv

Settings
# exclusive inclusive rows x rows loops node
1. 6.461 2,590.628 ↑ 1.0 1 1

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

2. 0.557 2,584.167 ↑ 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=2,528.267..2,584.167 rows=1,126 loops=1)

3. 55.420 2,583.610 ↑ 1.7 1,126 1

GroupAggregate (cost=4,444,697.22..4,445,346.76 rows=1,918 width=253) (actual time=2,528.255..2,583.610 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.407 2,528.190 ↑ 1.2 7,996 1

Sort (cost=4,443,788.18..4,443,811.82 rows=9,456 width=209) (actual time=2,527.941..2,528.190 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.154 2,518.783 ↑ 1.2 7,996 1

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

  • Join Filter: NULL::boolean
8. 467.114 2,517.629 ↑ 1.2 7,996 1

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

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
9. 640.116 2,022.111 ↓ 1.7 7,329,912 1

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

10. 20.592 20.592 ↑ 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.069..20.592 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
11. 1,361.403 1,361.403 ↑ 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.020..0.331 rows=1,782 loops=4,113)

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

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

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 864kB
13. 26.732 26.732 ↓ 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=0.544..26.732 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