Where time is lost, and how many buses it took

Status: built 2026-09-17. Two drill-downs from the network overview: /overview/punctuality splits punctuality by where on the journey it was measured and adds excess wait time; /overview/vehicles measures peak vehicle requirement from the vehicles that actually reported.

Both take the same filters as /overview and read the same rollup, so a figure on one page and a figure on another describe the same journeys.

Punctuality is three numbers, not one

A single on-time percentage hides which of three problems a route has. Leaving late is a depot problem. Losing time in the middle is a road problem. Arriving early at the end is usually neither.

South Yorkshire, weekdays, 1–16 September 2026:

	on time	early	late	measured
Start point	86.0%	5.7%	8.3%	61,888
Intermediate	83.2%	2.7%	14.1%	334,503
End point	51.9%	34.7%	13.4%	38,020

The terminus figure is the point. Buses run early at the end of a journey thirteen times more often than at intermediate timing points — 34.7% against 2.7%. That is not bad driving: the last leg carries padding and an interchange is where a bus is meant to wait. Counted in with the rest it drags a route's figure down for doing something nobody minds, which is why punctuality_rollup has always held it apart and why this page shows it apart.

Intermediate is the number to watch. It is the middle of the journey, where the road is the only thing acting on the bus.

On time is −60 to +359 seconds at timing points throughout, and the constants are imported from punctuality_rollup rather than repeated, so the two cannot drift apart.

Excess wait time, and why it barely applies here

EWT asks how much longer people waited than the timetable promised, without pretending anybody consults a timetable for a bus every six minutes. Average wait time is sum(h²) / (2·sum(h)) over headways; EWT is actual minus scheduled.

headway_daily stores those components rather than the answer, because EWT cannot be averaged but n, sum(h) and sum(h²) can all be added — so any filter computes its own EWT correctly instead of averaging averages.

Measure the network first. Of 3.4 million measured gaps in that window, 2.4% were 12 minutes or less. On a single September weekday, exactly one route — 88, Stagecoach Yorkshire — was frequent on the usual definition, at 69% of its gaps with a median headway of 12.0 minutes.

So EWT is published per route, with its coverage beside it, and never as a network headline. A page printing one EWT for South Yorkshire would be describing about three per cent of it. The comparable products do print one; that is worth knowing when a number like "0.59 minutes" is quoted at you.

Worst in that window: route 218 (SYRK) at +7.65 minutes — scheduled wait 4.73, actual 12.38. Negative EWT is left unclamped, because buses running more evenly than the timetable asked for is a real outcome and hiding it would make the measure look one-directional.

The sums are filtered on the scheduled gap, not the actual one: which departures the measure applies to is a property of the timetable, and filtering on the actual gap would quietly drop the late buses that are the whole reason for measuring.

Peak vehicles, measured from vehicles

The comparable products derive PVR from the schedule's blocks. block_id is present on 142,131 of 336,058 trips — 42% — so that route is closed to us.

It also turns out to be the worse route. Measured against the trips that do carry a block, for consecutive journeys on the same vehicle with a gap under ten minutes:

	pairs
same block, same route	4,142
different block, different route	915
same block, different route	569
different block, same route	50

Where the block changed, 95% of the time the route changed too. A block is largely a working within one service, so one bus running two routes in succession gets two of them — 609 of 2,844 blocks span more than one route. Where block and vehicle ought to agree, they disagree on 50 pairs in 5,666: 99.1% agreement.

So a vehicle's day is reconstructed from the vehicle. vehicle_spans holds one row per observed journey with its vehicle and its real start and end; journeys are joined into duties wherever the gap is under PVR_LAYOVER_MINUTES (20 by default), and the peak is a sweep over those intervals.

The property the whole thing rests on: a vehicle is on exactly one journey at a time, so a per-route count of vehicles at an instant is additive across routes. The filtered set can be summed and the maximum taken, and the answer is a true peak rather than a sum of peaks that never happened at once.

Two numbers, both shown:

• In service counts a bus only while it is running a journey.
• Including layover counts it across the gaps too, which is what a vehicle requirement means — a bus standing at a terminus for eight minutes is not available for anything else.

The threshold moves the answer, so the page prints it. South Yorkshire, 15 September: 419 strict, 434 at ten minutes, 456 at twenty, 471 at thirty.

It is a floor, not the fleet. A vehicle that never reported never appears, so the true requirement is this or higher, never lower.

The headline is the median of the daily peaks, not the peak of the window: a peak across a fortnight is the busiest minute of the busiest day, which is true and useless.

Making the sweep fast

The first version ran two queries per day. Correct, and four seconds for a month. Partitioning the sweep by service_date made it two queries whatever the window — and slower, because finding the peak time meant joining the sweep back to its own maximum. DISTINCT ON (service_date) ... ORDER BY service_date, running DESC, t gets the peak and its moment from one ordered pass.

Then the sorts were spilling to disk: 134,000 rows into (service_date, vehicle_id, started_at) at 6.5 MB of external merge. ix_vehicle_spans_sweep on (admin_area, service_date, vehicle_id, started_at) removes that sort rather than speeding it up, and the sweep's own sort gets SET LOCAL work_mem = '64MB' — LOCAL, so it lasts for the transaction and does not become a setting every other query inherits.

Worst case now is the unfiltered 28-day network view at about 3.4 seconds; filtered to a route it is under half a second.

ORDER BY t, d matters. At an instant where one interval ends and another begins, the -1 has to land before the +1, or a clean handover reads as two vehicles for zero seconds and every terminus inflates the peak.

Files

Path	What
frontend/reliability_web.py	both pages and their APIs
frontend/overview_rollup.py	fills overview_daily, vehicle_spans, headway_daily
frontend/migrations/versions/d3a5c81e7f92_*.py	the tables
frontend/migrations/versions/e91c47b0da35_*.py	the sweep index
frontend/templates/punctuality_detail.html, vehicles.html, _overview_filters.html	the pages
frontend/static/js/punctuality_detail.js, vehicles.js	the charts
frontend/static/css/overview_detail.css	layered over overview.css