clear markdown 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. 648404 fiddles created (15128 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.71 rows=401 width=20) (actual time=0.082..11.315 rows=401 loops=1)
Buffers: shared hit=794 read=813 written=388
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.008..0.095 rows=401 loops=1)
Buffers: shared hit=2
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.027..0.027 rows=1 loops=401)
Buffers: shared hit=792 read=813 written=388
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8615.94 rows=2494 width=16) (actual time=0.026..0.026 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=792 read=813 written=388
Planning time: 0.187 ms
Execution time: 11.387 ms
QUERY PLAN
Nested Loop (cost=0.42..1569.71 rows=401 width=20) (actual time=0.082..11.315 rows=401 loops=1)
Buffers: shared hit=794 read=813 written=388
-> Seq Scan on station s (cost=0.00..6.01 rows=401 width=4) (actual time=0.008..0.095 rows=401 loops=1)
Buffers: shared hit=2
-> Limit (cost=0.42..3.88 rows=1 width=16) (actual time=0.027..0.027 rows=1 loops=401)
Buffers: shared hit=792 read=813 written=388
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..8615.94 rows=2494 width=16) (actual time=0.026..0.026 rows=1 loops=401)
Index Cond: (station_id = s.station_id)
Buffers: shared hit=792 read=813 written=388
Planning time: 0.187 ms
Execution time: 11.387 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=222.803..231.888 rows=401 loops=1)
Buffers: shared hit=1358 read=7600 written=4
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=222.766..223.474 rows=401 loops=1)
Group Key: station_logs.station_id
Buffers: shared hit=370 read=6983
-> Sort (cost=14725.90..14727.90 rows=802 width=12) (actual time=222.761..222.988 rows=1203 loops=1)
Sort Key: station_logs.station_id
Sort Method: quicksort Memory: 105kB
Buffers: shared hit=370 read=6983
-> Gather (cost=14603.00..14687.21 rows=802 width=12) (actual time=220.424..222.560 rows=1203 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=370 read=6983
-> Partial HashAggregate (cost=13603.00..13607.01 rows=401 width=12) (actual time=165.543..165.605 rows=401 loops=3)
Group Key: station_logs.station_id
Buffers: shared hit=370 read=6983
-> Parallel Seq Scan on station_logs (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.017..85.883 rows=333333 loops=3)
Buffers: shared hit=370 read=6983
-> CTE Scan on t (cost=0.00..8.02 rows=401 width=12) (actual time=222.768..223.683 rows=401 loops=1)
Buffers: shared hit=370 read=6983
-> Index Scan using station_id__submitted_at on station_logs l (cost=0.42..8.16 rows=1 width=28) (actual time=0.015..0.016 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=988 read=617 written=4
Planning time: 0.280 ms
Execution time: 232.186 ms
QUERY PLAN
Nested Loop (cost=14736.35..18018.40 rows=1 width=40) (actual time=222.803..231.888 rows=401 loops=1)
Buffers: shared hit=1358 read=7600 written=4
CTE t
-> Finalize GroupAggregate (cost=14725.90..14735.92 rows=401 width=12) (actual time=222.766..223.474 rows=401 loops=1)
Group Key: station_logs.station_id
Buffers: shared hit=370 read=6983
-> Sort (cost=14725.90..14727.90 rows=802 width=12) (actual time=222.761..222.988 rows=1203 loops=1)
Sort Key: station_logs.station_id
Sort Method: quicksort Memory: 105kB
Buffers: shared hit=370 read=6983
-> Gather (cost=14603.00..14687.21 rows=802 width=12) (actual time=220.424..222.560 rows=1203 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=370 read=6983
-> Partial HashAggregate (cost=13603.00..13607.01 rows=401 width=12) (actual time=165.543..165.605 rows=401 loops=3)
Group Key: station_logs.station_id
Buffers: shared hit=370 read=6983
-> Parallel Seq Scan on station_logs (cost=0.00..11519.67 rows=416667 width=12) (actual time=0.017..85.883 rows=333333 loops=3)
Buffers: shared hit=370 read=6983
-> CTE Scan on t (cost=0.00..8.02 rows=401 width=12) (actual time=222.768..223.683 rows=401 loops=1)
Buffers: shared hit=370 read=6983
-> Index Scan using station_id__submitted_at on station_logs l (cost=0.42..8.16 rows=1 width=28) (actual time=0.015..0.016 rows=1 loops=401)
Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
Buffers: shared hit=988 read=617 written=4
Planning time: 0.280 ms
Execution time: 232.186 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.08 rows=401 width=20) (actual time=0.009..7554.235 rows=401 loops=1)
Buffers: shared hit=137706 read=865993 written=14
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59824.08 rows=1000000 width=20) (actual time=0.008..7205.026 rows=1000000 loops=1)
Buffers: shared hit=137706 read=865993 written=14
Planning time: 0.097 ms
Execution time: 7554.474 ms
QUERY PLAN
Unique (cost=0.42..62324.08 rows=401 width=20) (actual time=0.009..7554.235 rows=401 loops=1)
Buffers: shared hit=137706 read=865993 written=14
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59824.08 rows=1000000 width=20) (actual time=0.008..7205.026 rows=1000000 loops=1)
Buffers: shared hit=137706 read=865993 written=14
Planning time: 0.097 ms
Execution time: 7554.474 ms
 hidden batch(es)