explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z5Kz

Settings
# exclusive inclusive rows x rows loops node
1. 34.158 48,785.158 ↑ 1.0 1 1

Aggregate (cost=2,438.67..2,438.68 rows=1 width=32) (actual time=48,785.158..48,785.158 rows=1 loops=1)

2. 2.428 48,751.000 ↓ 6.0 3,432 1

Subquery Scan on q (cost=2,360.91..2,437.23 rows=576 width=44) (actual time=27,360.370..48,751.000 rows=3,432 loops=1)

3. 20,891.490 48,748.572 ↓ 6.0 3,432 1

GroupAggregate (cost=2,360.91..2,431.47 rows=576 width=253) (actual time=27,360.359..48,748.572 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.249 0.249 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..2.30 rows=1 width=78,632) (actual time=0.248..0.249 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_34580}'::text[]))
6. 6,336.807 27,857.082 ↓ 6,064.5 3,493,140 1

Sort (cost=2,358.61..2,360.05 rows=576 width=209) (actual time=27,360.295..27,857.082 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: external merge Disk: 628,096kB
7. 1,572.279 21,520.275 ↓ 6,064.5 3,493,140 1

Nested Loop (cost=1.28..2,332.20 rows=576 width=209) (actual time=7.170..21,520.275 rows=3,493,140 loops=1)

8. 514.262 12,961.716 ↓ 6,064.5 3,493,140 1

Nested Loop (cost=0.86..2,077.29 rows=576 width=193) (actual time=6.758..12,961.716 rows=3,493,140 loops=1)

9. 2.048 174.622 ↓ 3,432.0 3,432 1

Nested Loop (cost=0.29..2.83 rows=1 width=185) (actual time=6.100..174.622 rows=3,432 loops=1)

10. 0.252 0.252 ↑ 1.0 1 1

CTE Scan on boundaries (cost=0.00..0.02 rows=1 width=32) (actual time=0.251..0.252 rows=1 loops=1)

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

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..2.80 rows=1 width=185) (actual time=4.533..172.322 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: 1,564
12. 12,272.832 12,272.832 ↑ 4.0 1,018 3,432

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..2,033.33 rows=4,113 width=16) (actual time=0.090..3.576 rows=1,018 loops=3,432)

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

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

  • Index Cond: (trip_id = road_segments.trip_id)