clear markdown compare help best fiddles feedback dbanow.uk
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. 2591691 fiddles created (45708 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
 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
 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.190..16.899 rows=401 loops=1)
Buffers: shared hit=797 read=809 written=386
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.020..0.173 rows=401 loops=1)
Buffers: shared hit=2
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.040..0.040 rows=1 loops=401)
Buffers: shared hit=795 read=809 written=386
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8616.06 rows=2494 width=16) (actual time=0.039..0.039 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=795 read=809 written=386
Planning time: 0.223 ms
Execution time: 17.608 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=478.465..486.944 rows=401 loops=1)
Buffers: shared hit=1384 read=7576 written=4
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=478.422..479.113 rows=401 loops=1)
Group Key: station_logs.station_id
Buffers: shared hit=365 read=6988
-> Sort (cost=14725.90..14727.90 rows=802 width=12) (actual time=478.417..478.636 rows=1203 loops=1)
Sort Key: station_logs.station_id
Sort Method: quicksort Memory: 105kB
Buffers: shared hit=365 read=6988
-> Gather (cost=14603.00..14687.21 rows=802 width=12) (actual time=476.402..478.282 rows=1203 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=365 read=6988
-> Partial HashAggregate (cost=13603.00..13607.01 rows=401 width=12) (actual time=360.761..360.814 rows=401 loops=3)
Group Key: station_logs.station_id
Buffers: shared hit=365 read=6988
-> Parallel Seq Scan on station_logs (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.039..170.311 rows=333333 loops=3)
Buffers: shared hit=365 read=6988
-> CTE Scan on t (cost=0.00..8.02 rows=401 width=12) (actual time=478.425..479.327 rows=401 loops=1)
Buffers: shared hit=365 read=6988
-> 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.018 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=1019 read=588 written=4
Planning time: 4.081 ms
Execution time: 488.921 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.40 rows=401 width=20) (actual time=0.009..7168.333 rows=401 loops=1)
Buffers: shared hit=137604 read=866091 written=16
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59824.40 rows=1000000 width=20) (actual time=0.009..6889.090 rows=1000000 loops=1)
Buffers: shared hit=137604 read=866091 written=16
Planning time: 0.095 ms
Execution time: 7168.526 ms
 hidden batch(es)