I have the following table in PostgreSQL 12:
CREATE TABLE vehicle_fuel ( vehicle_id int NOT NULL , submitted_at timestamp NOT NULL , fuel float NOT NULL);
Currently there are approximately 1000 vehicle IDs each with an entry every 15 minutes or so, just over 100 million rows in total.
I'd like to be able to plot the fuel usage on a line chart for multiple chosen vehicles for any chosen time interval. I'd like to plot only
n=200 points spread relatively evenly over the time interval for each vessel.
My current solution uses
generate_series() to generate evenly spread "buckets" and then choosing the first timestamp within the "bucket" for each vehicle. E.g. for 10 vehicles, 200 points over the past 6 months
SELECT vehicle_id, submitted_at, fuel FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v) CROSS JOIN (SELECT generate_series('2020-05-17T00:00:00'::timestamp, '2020-11-17T00:00:00'::timestamp, '79488 seconds') AS bucket LIMIT 200) AS buckets CROSS JOIN LATERAL ( SELECT vehicle_id, submitted_at, fuel FROM vehicle_fuel WHERE vehicle_id = v AND submitted_at <= buckets.bucket ORDER BY submitted_at DESC LIMIT 1) data ORDER BY vehicle_id, submitted_at DESC;
However, this does not scale well with the number of vehicles requested, ~ 2300ms for the above query, significantly longer when I add more vehicles.
Is there any way I can make this faster? dbfiddle
I'm also using TimescaleDB if there is anything that can be utilised