Postgres 9.5

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?.
drop table if exists station_logs;
create table station_logs (
  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()*100)::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);
create index idx_station_logs__station_id on station_logs(station_id);
create index idx_station_logs__submitted_at on station_logs(submitted_at);

analyse station_logs;
1000000 rows affected

explain analyze
  select distinct on(station_id)
    station_id, submitted_at, level_sensor
  from station_logs
  order by station_id, submitted_at desc;

QUERY PLAN

Unique  (cost=0.42..62319.65 rows=101 width=20) (actual time=0.175..13397.064 rows=101 loops=1)

  ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.42..59819.65 rows=1000000 width=20) (actual time=0.173..13099.542 rows=1000000 loops=1)

Planning time: 1.427 ms

Execution time: 13397.945 ms

explain (analyse, verbose)
  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=22354.43..23205.98 rows=1 width=40) (actual time=291.951..293.356 rows=101 loops=1)

  Output: t.station_id, t.submitted_at, l.id, l.station_id, l.submitted_at, l.level_sensor

  CTE t

    ->  HashAggregate  (cost=22353.00..22354.01 rows=101 width=12) (actual time=291.840..291.894 rows=101 loops=1)

          Output: station_logs.station_id, max(station_logs.submitted_at)

          Group Key: station_logs.station_id

          ->  Seq Scan on public.station_logs  (cost=0.00..17353.00 rows=1000000 width=12) (actual time=0.029..101.083 rows=1000000 loops=1)

                Output: station_logs.id, station_logs.station_id, station_logs.submitted_at, station_logs.level_sensor

  ->  CTE Scan on t  (cost=0.00..2.02 rows=101 width=12) (actual time=291.845..291.941 rows=101 loops=1)

        Output: t.station_id, t.submitted_at

  ->  Index Scan using idx_station_logs__submitted_at on public.station_logs l  (cost=0.42..8.41 rows=1 width=28) (actual time=0.013..0.013 rows=1 loops=101)

        Output: l.id, l.station_id, l.submitted_at, l.level_sensor

        Index Cond: (l.submitted_at = t.submitted_at)

        Filter: (t.station_id = l.station_id)

Planning time: 1.738 ms

Execution time: 294.222 ms