explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Man1

Settings
# exclusive inclusive rows x rows loops node
1. 66.200 73,935.240 ↑ 1.0 1 1

Aggregate (cost=7,236.72..7,236.82 rows=1 width=32) (actual time=73,935.240..73,935.240 rows=1 loops=1)

2. 10.145 73,869.040 ↓ 6.3 3,432 1

Subquery Scan on q (cost=7,065.99..7,235.36 rows=542 width=44) (actual time=56,667.804..73,869.040 rows=3,432 loops=1)

3. 16,758.734 73,858.895 ↓ 6.3 3,432 1

GroupAggregate (cost=7,065.99..7,181.16 rows=542 width=255) (actual time=56,667.781..73,858.895 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.073 0.073 ↑ 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.068..0.073 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_34580}'::text[]))
6. 8,456.033 57,100.161 ↓ 6,444.9 3,493,140 1

Sort (cost=7,063.61..7,064.96 rows=542 width=211) (actual time=56,667.652..57,100.161 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. 4,257.532 48,644.128 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=1.28..7,038.99 rows=542 width=211) (actual time=13.243..48,644.128 rows=3,493,140 loops=1)

8. 1,034.886 26,920.896 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=0.86..5,782.48 rows=542 width=195) (actual time=13.174..26,920.896 rows=3,493,140 loops=1)

9. 4.625 345.066 ↓ 3,432.0 3,432 1

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

10. 0.082 0.082 ↑ 1.0 1 1

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

11. 340.359 340.359 ↓ 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=8.973..340.359 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. 25,540.944 25,540.944 ↑ 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.024..7.442 rows=1,018 loops=3,432)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 17,465.700 17,465.700 ↑ 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.005..0.005 rows=1 loops=3,493,140)

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