gtfs-via-postgres icon indicating copy to clipboard operation
gtfs-via-postgres copied to clipboard

arrivals_departures/connections: move id column into separate view

Open derhuerst opened this issue 1 year ago • 0 comments

Currently, queries on arrivals_departures & connections are slow as soon as there are frequencies-based rows in there. This is because the frequencies_it – which is needed for arrival_departure_id/connection_id – calculation uses a row_number() over a PARTITION BY.

This PR

  • moves these expensive columns into separate views arrivals_departures_with_ids/connections_with_ids;
  • adapts arrival_departure_by_arrival_departure_id/connection_by_connection_id to use the new views;
  • adds benchmarking for the case described above.

derhuerst avatar Nov 04 '24 16:11 derhuerst