explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W6Pw

Settings
# exclusive inclusive rows x rows loops node
1. 40.956 382,410.106 ↑ 1.0 1 1

Aggregate (cost=7,381.39..7,381.49 rows=1 width=32) (actual time=382,410.105..382,410.106 rows=1 loops=1)

2. 5.050 382,369.150 ↓ 6.3 3,432 1

Subquery Scan on q (cost=7,210.65..7,380.03 rows=542 width=44) (actual time=365,539.248..382,369.150 rows=3,432 loops=1)

3. 16,495.870 382,364.100 ↓ 6.3 3,432 1

GroupAggregate (cost=7,210.65..7,325.83 rows=542 width=255) (actual time=365,539.195..382,364.100 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. 38.027 38.027 ↑ 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=38.024..38.027 rows=1 loops=1)

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

Sort (cost=7,208.27..7,209.63 rows=542 width=211) (actual time=365,539.055..365,868.230 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,503.230 357,832.638 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=1.28..7,183.66 rows=542 width=211) (actual time=337.357..357,832.638 rows=3,493,140 loops=1)

8. 1,081.602 314,904.868 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=0.86..5,927.15 rows=542 width=195) (actual time=296.232..314,904.868 rows=3,493,140 loops=1)

9. 151.840 9,500.962 ↓ 3,432.0 3,432 1

Nested Loop (cost=0.29..3.19 rows=1 width=187) (actual time=241.232..9,500.962 rows=3,432 loops=1)

10. 38.043 38.043 ↑ 1.0 1 1

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

11. 9,311.079 9,311.079 ↓ 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=55.019..9,311.079 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. 304,322.304 304,322.304 ↑ 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=5.030..88.672 rows=1,018 loops=3,432)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 38,424.540 38,424.540 ↑ 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.011..0.011 rows=1 loops=3,493,140)

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