Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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);

CREATE TABLE

INSERT 0 1000000

CREATE INDEX

VACUUM ANALYZE vehicle_fuel;

VACUUM

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.43..8948.43 rows=2000 width=20) (actual time=23.619..23.708 rows=2000 loops=1)

  Sort Key: vehicle_fuel.vehicle_id, vehicle_fuel.submitted_at DESC

  Sort Method: quicksort  Memory: 205kB

  Buffers: shared hit=7060 read=941 written=522

  ->  Nested Loop  (cost=0.42..8833.77 rows=2000 width=20) (actual time=0.054..22.515 rows=2000 loops=1)

        Buffers: shared hit=7060 read=941 written=522

        ->  Limit  (cost=0.00..1.00 rows=200 width=8) (actual time=0.007..0.119 rows=200 loops=1)

              ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=8) (actual time=0.004..0.097 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.011..0.110 rows=10 loops=200)

              Buffers: shared hit=7060 read=941 written=522

              ->  Values Scan on "*VALUES*"  (cost=0.00..0.12 rows=10 width=4) (actual time=0.000..0.004 rows=10 loops=200)

              ->  Limit  (cost=0.42..4.37 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=2000)

                    Buffers: shared hit=7060 read=941 written=522

                    ->  Index Scan using vehicle_id__submitted_at on vehicle_fuel  (cost=0.42..1315.07 rows=333 width=20) (actual time=0.009..0.009 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=7060 read=941 written=522

Planning Time: 0.280 ms

Execution Time: 23.846 ms

EXPLAIN