clear markdown help best fiddles feedback dbanow.uk a leap of faith?
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. 995012 fiddles created (11013 in the last week).

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


VACUUM ANALYZE station_logs;
 hidden batch(es)


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


VACUUM ANALYZE station;
 hidden batch(es)


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.73 rows=401 width=20) (actual time=0.059..10.832 rows=401 loops=1)
Buffers: shared hit=811 read=796 written=300
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.009..0.118 rows=401 loops=1)
Buffers: shared hit=1 read=1
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.026..0.026 rows=1 loops=401)
Buffers: shared hit=810 read=795 written=300
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8616.07 rows=2494 width=16) (actual time=0.025..0.025 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=810 read=795 written=300
Planning time: 0.195 ms
Execution time: 10.929 ms
QUERY PLAN
Nested Loop (cost=0.42..1569.73 rows=401 width=20) (actual time=0.059..10.832 rows=401 loops=1)
Buffers: shared hit=811 read=796 written=300
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.009..0.118 rows=401 loops=1)
Buffers: shared hit=1 read=1
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.026..0.026 rows=1 loops=401)
Buffers: shared hit=810 read=795 written=300
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8616.07 rows=2494 width=16) (actual time=0.025..0.025 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=810 read=795 written=300
Planning time: 0.195 ms
Execution time: 10.929 ms
 hidden batch(es)


-- 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=345.366..353.727 rows=401 loops=1)
Buffers: shared hit=1239 read=7720 written=36
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=345.318..346.023 rows=401 loops=1)
Group Key: station_logs.station_id
Buffers: shared hit=368 read=6985 written=34
-> Sort (cost=14725.90..14727.90 rows=802 width=12) (actual time=345.314..345.554 rows=1203 loops=1)
Sort Key: station_logs.station_id
Sort Method: quicksort Memory: 105kB
Buffers: shared hit=368 read=6985 written=34
-> Gather (cost=14603.00..14687.21 rows=802 width=12) (actual time=344.833..345.171 rows=1203 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=368 read=6985 written=34
-> Partial HashAggregate (cost=13603.00..13607.01 rows=401 width=12) (actual time=316.361..316.410 rows=401 loops=3)
Group Key: station_logs.station_id
Buffers: shared hit=368 read=6985 written=34
-> Parallel Seq Scan on station_logs (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.030..120.920 rows=333333 loops=3)
Buffers: shared hit=368 read=6985 written=34
-> CTE Scan on t (cost=0.00..8.02 rows=401 width=12) (actual time=345.321..346.213 rows=401 loops=1)
Buffers: shared hit=368 read=6985 written=34
-> Index Scan using station_id__submitted_at on station_logs l (cost=0.42..8.16 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=871 read=735 written=2
Planning time: 0.306 ms
Execution time: 354.024 ms
QUERY PLAN
Nested Loop (cost=14736.35..18018.40 rows=1 width=40) (actual time=345.366..353.727 rows=401 loops=1)
Buffers: shared hit=1239 read=7720 written=36
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=345.318..346.023 rows=401 loops=1)
Group Key: station_logs.station_id
Buffers: shared hit=368 read=6985 written=34
-> Sort (cost=14725.90..14727.90 rows=802 width=12) (actual time=345.314..345.554 rows=1203 loops=1)
Sort Key: station_logs.station_id
Sort Method: quicksort Memory: 105kB
Buffers: shared hit=368 read=6985 written=34
-> Gather (cost=14603.00..14687.21 rows=802 width=12) (actual time=344.833..345.171 rows=1203 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=368 read=6985 written=34
-> Partial HashAggregate (cost=13603.00..13607.01 rows=401 width=12) (actual time=316.361..316.410 rows=401 loops=3)
Group Key: station_logs.station_id
Buffers: shared hit=368 read=6985 written=34
-> Parallel Seq Scan on station_logs (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.030..120.920 rows=333333 loops=3)
Buffers: shared hit=368 read=6985 written=34
-> CTE Scan on t (cost=0.00..8.02 rows=401 width=12) (actual time=345.321..346.213 rows=401 loops=1)
Buffers: shared hit=368 read=6985 written=34
-> Index Scan using station_id__submitted_at on station_logs l (cost=0.42..8.16 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=871 read=735 written=2
Planning time: 0.306 ms
Execution time: 354.024 ms
 hidden batch(es)


-- 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.42 rows=401 width=20) (actual time=0.009..6932.339 rows=401 loops=1)
Buffers: shared hit=137749 read=865953 written=18
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59824.42 rows=1000000 width=20) (actual time=0.009..6669.630 rows=1000000 loops=1)
Buffers: shared hit=137749 read=865953 written=18
Planning time: 0.096 ms
Execution time: 6932.559 ms
QUERY PLAN
Unique (cost=0.42..62324.42 rows=401 width=20) (actual time=0.009..6932.339 rows=401 loops=1)
Buffers: shared hit=137749 read=865953 written=18
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59824.42 rows=1000000 width=20) (actual time=0.009..6669.630 rows=1000000 loops=1)
Buffers: shared hit=137749 read=865953 written=18
Planning time: 0.096 ms
Execution time: 6932.559 ms
 hidden batch(es)