First metric in each time "bucket" for multiple IDs

Question

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

Answer 1

Thanks to k_rus for suggesting to use the TimescaleDB function time_bucket(). The below query is significantly faster:

SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vehicle_ids(v)
         CROSS JOIN LATERAL (SELECT DISTINCT ON (vehicle_id, bucket) vehicle_id,
                                                                     time_bucket('79488 seconds',
                                                                                 '2020-11-17T00:00:00'::timestamp) AS bucket,
                                                                     submitted_at,
                                                                     fuel

                             FROM vehicle_fuel
                             WHERE vehicle_id = v
                               AND submitted_at BETWEEN '2020-05-17T00:00:00'::timestamp AND '2020-11-17T00:00:00'::timestamp
                             ORDER BY vehicle_id, bucket DESC, submitted_at DESC
                             LIMIT 200) d
ORDER BY vehicle_id, submitted_at DESC;