explain.depesz.com

PostgreSQL's explain analyze made readable

Result: boYx

Settings
# exclusive inclusive rows x rows loops node
1. 0.278 200,444.366 ↑ 1.0 1 1

Aggregate (cost=12,466,534.05..12,466,534.06 rows=1 width=32) (actual time=200,444.366..200,444.366 rows=1 loops=1)

2. 0.352 200,444.088 ↑ 3.1 274 1

Subquery Scan on q (cost=12,466,252.54..12,466,531.93 rows=850 width=44) (actual time=200,241.758..200,444.088 rows=274 loops=1)

3. 194.088 200,443.736 ↑ 3.1 274 1

GroupAggregate (cost=12,466,252.54..12,466,523.43 rows=850 width=255) (actual time=200,241.738..200,443.736 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. 80.203 200,249.648 ↓ 9.3 36,284 1

Sort (cost=12,465,342.08..12,465,351.79 rows=3,882 width=211) (actual time=200,240.083..200,249.648 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. 14.245 200,169.445 ↓ 9.3 36,284 1

Nested Loop Left Join (cost=19,081.93..12,465,110.67 rows=3,882 width=211) (actual time=1,735.049..200,169.445 rows=36,284 loops=1)

  • Join Filter: NULL::boolean
8. 309.371 200,155.200 ↓ 9.3 36,284 1

Hash Join (cost=19,081.93..12,465,071.85 rows=3,882 width=211) (actual time=1,735.042..200,155.200 rows=36,284 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 90,765.281 199,836.491 ↓ 4.8 2,799,258 1

Hash Join (cost=8,384.17..12,452,853.19 rows=579,383 width=195) (actual time=75.617..199,836.491 rows=2,799,258 loops=1)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
10. 109,050.190 109,050.190 ↑ 1.0 593,771,497 1

Seq Scan on road_segments (cost=0.00..10,885,811.20 rows=593,771,520 width=16) (actual time=8.586..109,050.190 rows=593,771,497 loops=1)

11. 2.047 21.020 ↓ 3.4 2,881 1

Hash (cost=8,373.54..8,373.54 rows=850 width=187) (actual time=21.019..21.020 rows=2,881 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 523kB
12. 18.162 18.973 ↓ 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.988..18.973 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
13. 0.811 0.811 ↓ 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.811..0.811 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
14. 1.126 9.338 ↓ 1.1 4,113 1

Hash (cost=10,648.96..10,648.96 rows=3,904 width=32) (actual time=9.338..9.338 rows=4,113 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 322kB
15. 7.633 8.212 ↓ 1.1 4,113 1

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

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
16. 0.579 0.579 ↓ 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=0.579..0.579 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
17. 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 : 9.049 ms