explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lkuh

Settings
# exclusive inclusive rows x rows loops node
1. 0.252 21,458.282 ↑ 1.0 1 1

Aggregate (cost=15,815,391.47..15,815,391.48 rows=1 width=32) (actual time=21,458.281..21,458.282 rows=1 loops=1)

2. 0.330 21,458.030 ↑ 3.1 274 1

Subquery Scan on q (cost=15,815,109.95..15,815,389.34 rows=850 width=44) (actual time=21,267.974..21,458.030 rows=274 loops=1)

3. 186.298 21,457.700 ↑ 3.1 274 1

GroupAggregate (cost=15,815,109.95..15,815,380.84 rows=850 width=255) (actual time=21,267.953..21,457.700 rows=274 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.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..910.46 rows=3,346 width=75,373) (never executed)

6. 63.161 21,271.402 ↓ 9.3 36,284 1

Sort (cost=15,814,199.49..15,814,209.20 rows=3,882 width=211) (actual time=21,266.417..21,271.402 rows=36,284 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: 12500kB
7. 13.052 21,208.241 ↓ 9.3 36,284 1

Nested Loop Left Join (cost=15,810,974.19..15,813,968.08 rows=3,882 width=211) (actual time=20,448.341..21,208.241 rows=36,284 loops=1)

  • Join Filter: NULL::boolean
8. 848.931 21,195.189 ↓ 9.3 36,284 1

Merge Join (cost=15,810,974.19..15,813,929.26 rows=3,882 width=211) (actual time=20,448.332..21,195.189 rows=36,284 loops=1)

  • Merge Cond: (road_segments.trip_id = road_movements.trip_id)
9. 2,930.160 20,318.414 ↓ 4.7 2,746,691 1

Sort (cost=15,800,092.32..15,801,540.78 rows=579,383 width=195) (actual time=19,663.910..20,318.414 rows=2,746,691 loops=1)

  • Sort Key: road_segments.trip_id
  • Sort Method: quicksort Memory: 772445kB
10. 841.497 17,388.254 ↓ 4.8 2,799,258 1

Nested Loop (cost=187.26..15,744,633.32 rows=579,383 width=195) (actual time=1.033..17,388.254 rows=2,799,258 loops=1)

11. 55.104 55.913 ↓ 3.4 2,881 1

Bitmap Heap Scan on v_transit_roads (cost=91.63..8,373.54 rows=850 width=187) (actual time=0.983..55.913 rows=2,881 loops=1)

  • Recheck Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=599
12. 0.809 0.809 ↓ 1.1 2,882 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..91.42 rows=2,551 width=0) (actual time=0.809..0.809 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
13. 15,937.692 16,490.844 ↑ 5.2 972 2,881

Bitmap Heap Scan on road_segments (cost=95.63..18,462.60 rows=5,065 width=16) (actual time=0.368..5.724 rows=972 loops=2,881)

  • Recheck Cond: (link_id = v_transit_roads.link_id)
  • Heap Blocks: exact=2799258
14. 553.152 553.152 ↑ 5.2 972 2,881

Bitmap Index Scan on idx_road_segments_link_id (cost=0.00..94.36 rows=5,065 width=0) (actual time=0.192..0.192 rows=972 loops=2,881)

  • Index Cond: (link_id = v_transit_roads.link_id)
15. 2.196 27.844 ↓ 1.1 4,113 1

Sort (cost=10,881.85..10,891.61 rows=3,904 width=32) (actual time=27.382..27.844 rows=4,113 loops=1)

  • Sort Key: road_movements.trip_id
  • Sort Method: quicksort Memory: 514kB
16. 24.413 25.648 ↓ 1.1 4,113 1

Bitmap Heap Scan on road_movements (cost=74.68..10,648.96 rows=3,904 width=32) (actual time=1.843..25.648 rows=4,113 loops=1)

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
17. 1.235 1.235 ↓ 1.1 4,113 1

Bitmap Index Scan on idx_road_movements_commod_id (cost=0.00..73.70 rows=3,904 width=0) (actual time=1.235..1.235 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
18. 0.000 0.000 ↓ 0.0 0 36,284

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=36,284)

  • One-Time Filter: false
Planning time : 2.221 ms