EXPLAIN (ANALYZE, BUFFERS)
SELECT vehicle_id, submitted_at, fuel
FROM (VALUES (1), (3), (4), (34), (44), (56), (76), (79), (81), (83)) vessel_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;
QUERY PLAN
Sort (cost=8943.50..8948.50 rows=2000 width=20) (actual time=204.675..204.802 rows=2000 loops=1)
Sort Key: vehicle_fuel.vehicle_id, vehicle_fuel.submitted_at DESC
Sort Method: quicksort Memory: 205kB
Buffers: shared hit=6998 read=1009 written=353
-> Nested Loop (cost=0.42..8833.84 rows=2000 width=20) (actual time=0.063..203.271 rows=2000 loops=1)
Buffers: shared hit=6998 read=1009 written=353
-> Limit (cost=0.00..1.00 rows=200 width=8) (actual time=0.005..0.139 rows=200 loops=1)
-> ProjectSet (cost=0.00..5.02 rows=1000 width=8) (actual time=0.004..0.113 rows=200 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
-> Nested Loop (cost=0.42..44.05 rows=10 width=20) (actual time=0.398..1.013 rows=10 loops=200)
Buffers: shared hit=6998 read=1009 written=353
-> Values Scan on "*VALUES*" (cost=0.00..0.12 rows=10 width=4) (actual time=0.000..0.005 rows=10 loops=200)
-> Limit (cost=0.42..4.37 rows=1 width=20) (actual time=0.100..0.100 rows=1 loops=2000)
Buffers: shared hit=6998 read=1009 written=353
-> Index Scan using vehicle_id__submitted_at on vehicle_fuel (cost=0.42..1315.08 rows=333 width=20) (actual time=0.099..0.099 rows=1 loops=2000)
Index Cond: ((vehicle_id = "*VALUES*".column1) AND (submitted_at <= (generate_series('2020-05-17 00:00:00'::timestamp without time zone, '2020-11-17 00:00:00'::timestamp without time zone, '22:04:48'::interval))))
Buffers: shared hit=6998 read=1009 written=353
Planning Time: 0.257 ms
Execution Time: 204.928 ms
…
