explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FuhH

Settings
# exclusive inclusive rows x rows loops node
1. 32.645 66,999.787 ↑ 1.0 1 1

Aggregate (cost=7,381.39..7,381.49 rows=1 width=32) (actual time=66,999.787..66,999.787 rows=1 loops=1)

2. 4.397 66,967.142 ↓ 6.3 3,432 1

Subquery Scan on q (cost=7,210.65..7,380.03 rows=542 width=44) (actual time=50,233.732..66,967.142 rows=3,432 loops=1)

3. 16,404.723 66,962.745 ↓ 6.3 3,432 1

GroupAggregate (cost=7,210.65..7,325.83 rows=542 width=255) (actual time=50,233.710..66,962.745 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.062 0.062 ↑ 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.059..0.062 rows=1 loops=1)

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

Sort (cost=7,208.27..7,209.63 rows=542 width=211) (actual time=50,233.586..50,558.022 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,148.871 42,760.957 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=1.28..7,183.66 rows=542 width=211) (actual time=2.627..42,760.957 rows=3,493,140 loops=1)

8. 994.363 23,639.526 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=0.86..5,927.15 rows=542 width=195) (actual time=2.589..23,639.526 rows=3,493,140 loops=1)

9. 3.295 265.091 ↓ 3,432.0 3,432 1

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

10. 0.070 0.070 ↑ 1.0 1 1

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

11. 261.726 261.726 ↓ 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.248..261.726 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. 22,380.072 22,380.072 ↑ 5.2 1,018 3,432

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,398.85 rows=5,251 width=16) (actual time=0.019..6.521 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 : 14.347 ms