explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gdr9

Settings
# exclusive inclusive rows x rows loops node
1. 68.730 73,889.208 ↑ 1.0 1 1

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

2. 7.508 73,820.478 ↓ 6.3 3,432 1

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

3. 16,752.207 73,812.970 ↓ 6.3 3,432 1

GroupAggregate (cost=7,065.99..7,181.16 rows=542 width=255) (actual time=56,557.762..73,812.970 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.124 0.124 ↑ 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.121..0.124 rows=1 loops=1)

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

Sort (cost=7,063.61..7,064.96 rows=542 width=211) (actual time=56,557.630..57,060.763 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,272.272 48,570.575 ↓ 6,444.9 3,493,140 1

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

8. 1,020.483 26,832.603 ↓ 6,444.9 3,493,140 1

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

9. 3.225 339.816 ↓ 3,432.0 3,432 1

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

10. 0.134 0.134 ↑ 1.0 1 1

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

11. 336.457 336.457 ↓ 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=6.709..336.457 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,472.304 25,472.304 ↑ 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.023..7.422 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 : 28.456 ms