clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798725 fiddles created (41859 in the last week).

CREATE TABLE vehicle_fuel ( vehicle_id int NOT NULL , submitted_at timestamp NOT NULL , fuel float NOT NULL); INSERT INTO vehicle_fuel (vehicle_id, submitted_at, fuel) SELECT (random() * 1000)::int , now() - make_interval(secs := random()*100000000) , (random() * 50)::int FROM generate_series(1,1000000); CREATE INDEX vehicle_id__submitted_at ON vehicle_fuel(vehicle_id, submitted_at DESC);
1000000 rows affected
 hidden batch(es)


VACUUM ANALYZE vehicle_fuel;
 hidden batch(es)


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
 hidden batch(es)