explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3JL6

Settings
# exclusive inclusive rows x rows loops node
1. 32.854 65,698.033 ↑ 1.0 1 1

Aggregate (cost=7,236.72..7,236.82 rows=1 width=32) (actual time=65,698.032..65,698.033 rows=1 loops=1)

2. 4.415 65,665.179 ↓ 6.3 3,432 1

Subquery Scan on q (cost=7,065.99..7,235.36 rows=542 width=44) (actual time=48,884.784..65,665.179 rows=3,432 loops=1)

3. 16,457.485 65,660.764 ↓ 6.3 3,432 1

GroupAggregate (cost=7,065.99..7,181.16 rows=542 width=255) (actual time=48,884.765..65,660.764 rows=3,432 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.048 0.048 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..2.38 rows=1 width=78,632) (actual time=0.046..0.048 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_34580}'::text[]))
6. 7,848.925 49,203.279 ↓ 6,444.9 3,493,140 1

Sort (cost=7,063.61..7,064.96 rows=542 width=211) (actual time=48,884.629..49,203.279 rows=3,493,140 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: 1141224kB
7. 5,141.553 41,354.354 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=1.28..7,038.99 rows=542 width=211) (actual time=2.734..41,354.354 rows=3,493,140 loops=1)

8. 1,022.165 22,240.241 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=0.86..5,782.48 rows=542 width=195) (actual time=2.703..22,240.241 rows=3,493,140 loops=1)

9. 2.853 258.852 ↓ 3,432.0 3,432 1

Nested Loop (cost=0.29..3.19 rows=1 width=187) (actual time=2.430..258.852 rows=3,432 loops=1)

10. 0.055 0.055 ↑ 1.0 1 1

CTE Scan on boundaries (cost=0.00..0.20 rows=1 width=32) (actual time=0.051..0.055 rows=1 loops=1)

11. 255.944 255.944 ↓ 3,432.0 3,432 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..2.89 rows=1 width=187) (actual time=2.321..255.944 rows=3,432 loops=1)

  • Index Cond: ((geometry && boundaries.geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry))
  • Filter: (_st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, boundaries.geometry))
  • Rows Removed by Filter: 1564
12. 20,959.224 20,959.224 ↑ 5.0 1,018 3,432

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,266.99 rows=5,123 width=16) (actual time=0.018..6.107 rows=1,018 loops=3,432)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 13,972.560 13,972.560 ↑ 1.0 1 3,493,140

Index Scan using road_movements_pkey on road_movements (cost=0.42..2.32 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=3,493,140)

  • Index Cond: (trip_id = road_segments.trip_id)
Planning time : 8.619 ms