clear markdown compare help best fiddles feedback
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591684 fiddles created (45712 in the last week).

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

explain analyze select distinct on(station_id) station_id, submitted_at, level_sensor from station_logs order by station_id, submitted_at desc;
Unique (cost=0.42..62322.57 rows=101 width=20) (actual time=0.045..13829.698 rows=101 loops=1)
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59822.57 rows=1000000 width=20) (actual time=0.044..13507.493 rows=1000000 loops=1)
Planning time: 0.373 ms
Execution time: 13829.782 ms
 hidden batch(es)

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);
Nested Loop (cost=22354.43..23205.98 rows=1 width=40) (actual time=290.209..291.570 rows=101 loops=1)
Output: t.station_id, t.submitted_at,, l.station_id, l.submitted_at, l.level_sensor
-> HashAggregate (cost=22353.00..22354.01 rows=101 width=12) (actual time=290.137..290.172 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.025..99.764 rows=1000000 loops=1)
Output:, 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=290.142..290.222 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.012..0.012 rows=1 loops=101)
Output:, 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: 0.874 ms
Execution time: 292.403 ms
 hidden batch(es)