clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
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. 582783 fiddles created (13382 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.60 rows=401 width=20) (actual time=0.064..11.026 rows=401 loops=1)
Buffers: shared hit=800 read=808 written=307
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.009..0.124 rows=401 loops=1)
Buffers: shared hit=2
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.026..0.026 rows=1 loops=401)
Buffers: shared hit=798 read=808 written=307
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8615.25 rows=2494 width=16) (actual time=0.025..0.025 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=798 read=808 written=307
Planning time: 0.231 ms
Execution time: 11.101 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=184.112..192.332 rows=401 loops=1)
Buffers: shared hit=1317 read=7643 written=6
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=184.072..185.844 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=184.067..184.286 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=183.197..183.882 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=176.044..176.263 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.018..70.143 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=184.074..186.017 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.014..0.015 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=952 read=655 written=6
Planning time: 0.306 ms
Execution time: 192.597 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..62322.29 rows=401 width=20) (actual time=0.009..6429.419 rows=401 loops=1)
Buffers: shared hit=137839 read=865877 written=13
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59822.29 rows=1000000 width=20) (actual time=0.009..6188.854 rows=1000000 loops=1)
Buffers: shared hit=137839 read=865877 written=13
Planning time: 0.092 ms
Execution time: 6429.636 ms
 hidden batch(es)