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. 582709 fiddles created (13308 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;
QUERY PLAN
Unique (cost=0.42..62323.02 rows=101 width=20) (actual time=0.045..17991.930 rows=101 loops=1)
-> Index Scan using station_id__submitted_at on station_logs (cost=0.42..59823.02 rows=1000000 width=20) (actual time=0.044..17788.844 rows=1000000 loops=1)
Planning time: 0.321 ms
Execution time: 17992.018 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);
QUERY PLAN
Nested Loop (cost=22354.43..23205.98 rows=1 width=40) (actual time=281.408..282.426 rows=101 loops=1)
Output: t.station_id, t.submitted_at, l.id, l.station_id, l.submitted_at, l.level_sensor
CTE t
-> HashAggregate (cost=22353.00..22354.01 rows=101 width=12) (actual time=281.316..281.346 rows=101 loops=1)
Output: station_logs.station_id, max(station_logs.submitted_at)
Group Key: station_logs.station_id
-> Seq Scan on fiddle_cjytsiykfcziiczqwmqk.station_logs (cost=0.00..17353.00 rows=1000000 width=12) (actual time=0.014..89.337 rows=1000000 loops=1)
Output: station_logs.id, 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=281.319..281.395 rows=101 loops=1)
Output: t.station_id, t.submitted_at
-> Index Scan using idx_station_logs__submitted_at on fiddle_cjytsiykfcziiczqwmqk.station_logs l (cost=0.42..8.41 rows=1 width=28) (actual time=0.009..0.009 rows=1 loops=101)
Output: l.id, l.station_id, l.submitted_at, l.level_sensor
Index Cond: (l.submitted_at = t.submitted_at)
Filter: (t.station_id = l.station_id)
Rows Removed by Filter: 0
Planning time: 0.482 ms
Execution time: 282.538 ms
 hidden batch(es)