Postgres 9.6

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?.
-- building on Abelisto's setup
CREATE TABLE station_logs (
   station_log_id bigserial PRIMARY KEY
 , station_id     int
 , submitted_at   timestamp
 , level_sensor   double precision);

INSERT INTO station_logs (station_id, submitted_at, level_sensor)
SELECT (random() * 400)::int
     , now() - make_interval(secs := random()*100000000)
     , random()
FROM   generate_series(1,1000000);

CREATE INDEX station_id__submitted_at       ON station_logs(station_id, submitted_at DESC NULLS LAST);
CREATE INDEX idx_station_logs__station_id   ON station_logs(station_id);
1000000 rows affected

VACUUM ANALYZE station_logs;
-- adding station table (very fast)
CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (   
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;
401 rows affected

VACUUM ANALYZE station;
EXPLAIN (ANALYZE, BUFFERS)
SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;


QUERY PLAN


Nested Loop  (cost=0.42..1569.72 rows=401 width=20) (actual time=0.117..15.433 rows=401 loops=1)

  Buffers: shared hit=810 read=796 written=269

  ->  Seq Scan on station s  (cost=0.00..6.01 rows=401 width=4) (actual time=0.011..0.247 rows=401 loops=1)

        Buffers: shared hit=1 read=1

  ->  Limit  (cost=0.42..3.88 rows=1 width=16) (actual time=0.036..0.037 rows=1 loops=401)

        Buffers: shared hit=809 read=795 written=269

        ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.42..8616.01 rows=2494 width=16) (actual time=0.036..0.036 rows=1 loops=401)

              Index Cond: (station_id = s.station_id)

              Buffers: shared hit=809 read=795 written=269

Planning time: 0.270 ms

Execution time: 15.576 ms


-- Abelisto's query
EXPLAIN (ANALYZE, BUFFERS)
  with t as (select station_id, max(submitted_at) submitted_at from station_logs group by station_id)
  select * from t join station_logs l on (l.station_id = t.station_id and l.submitted_at = t.submitted_at);


QUERY PLAN


Nested Loop  (cost=14736.35..18018.40 rows=1 width=40) (actual time=452.380..463.350 rows=401 loops=1)

  Buffers: shared hit=1276 read=7685 written=102

  CTE t

    ->  Finalize GroupAggregate  (cost=14725.90..14735.92 rows=401 width=12) (actual time=452.324..453.133 rows=401 loops=1)

          Group Key: station_logs.station_id

          Buffers: shared hit=374 read=6979 written=96

          ->  Sort  (cost=14725.90..14727.90 rows=802 width=12) (actual time=452.317..452.591 rows=1203 loops=1)

                Sort Key: station_logs.station_id

                Sort Method: quicksort  Memory: 105kB

                Buffers: shared hit=374 read=6979 written=96

                ->  Gather  (cost=14603.00..14687.21 rows=802 width=12) (actual time=446.706..452.100 rows=1203 loops=1)

                      Workers Planned: 2

                      Workers Launched: 2

                      Buffers: shared hit=374 read=6979 written=96

                      ->  Partial HashAggregate  (cost=13603.00..13607.01 rows=401 width=12) (actual time=425.202..425.281 rows=401 loops=3)

                            Group Key: station_logs.station_id

                            Buffers: shared hit=374 read=6979 written=96

                            ->  Parallel Seq Scan on station_logs  (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.042..200.695 rows=333333 loops=3)

                                  Buffers: shared hit=374 read=6979 written=96

  ->  CTE Scan on t  (cost=0.00..8.02 rows=401 width=12) (actual time=452.327..453.382 rows=401 loops=1)

        Buffers: shared hit=374 read=6979 written=96

  ->  Index Scan using station_id__submitted_at on station_logs l  (cost=0.42..8.16 rows=1 width=28) (actual time=0.023..0.023 rows=1 loops=401)

        Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))

        Buffers: shared hit=902 read=706 written=6

Planning time: 0.404 ms

Execution time: 463.795 ms


-- your org. query
EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM   station_logs ORDER BY station_id, submitted_at DESC NULLS LAST;


QUERY PLAN


Unique  (cost=0.42..62324.27 rows=401 width=20) (actual time=0.013..9128.843 rows=401 loops=1)

  Buffers: shared hit=137475 read=866230 written=16

  ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.42..59824.27 rows=1000000 width=20) (actual time=0.012..8816.765 rows=1000000 loops=1)

        Buffers: shared hit=137475 read=866230 written=16

Planning time: 0.115 ms

Execution time: 9129.139 ms