clear markdown compare 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. 2249325 fiddles created (32238 in the last week).

CREATE TABLE geoposition_records ( id integer , latitude numeric(9,6) , longitude numeric(9,6) , speed integer , equipment_id integer , created_at timestamp ); INSERT INTO geoposition_records SELECT g, g/1000, g/1000, g, (random() * 56)::int , timestamp '2010-1-1' + g * interval '13 sec' -- ascending created_at FROM generate_series(1, 100000) g; ALTER TABLE geoposition_records ADD primary key (id); CREATE INDEX geo_rec_mult_idx ON geoposition_records (equipment_id, created_at DESC NULLS LAST); CREATE TABLE equipment AS SELECT DISTINCT equipment_id, ('foo' || equipment_id) AS equipment FROM geoposition_records; ANALYZE geoposition_records; ANALYZE equipment; -- funky function CREATE OR REPLACE FUNCTION f_latest_equip() RETURNS TABLE (equipment_id int, latest timestamp) LANGUAGE plpgsql STABLE AS $func$ BEGIN FOR equipment_id IN SELECT e.equipment_id FROM equipment e ORDER BY 1 LOOP SELECT g.created_at FROM geoposition_records g WHERE g.equipment_id = f_latest_equip.equipment_id -- prepend func to disambiguate name ORDER BY g.created_at DESC NULLS LAST LIMIT 1 INTO latest; RETURN NEXT; END LOOP; END $func$;
100000 rows affected
57 rows affected
 hidden batch(es)


-- slow EXPLAIN ANALYZE SELECT DISTINCT ON (1) equipment_id, created_at FROM geoposition_records ORDER BY 1, 2 DESC;
QUERY PLAN
Unique (cost=10040.82..10540.82 rows=57 width=12) (actual time=113.398..134.997 rows=57 loops=1)
-> Sort (cost=10040.82..10290.82 rows=100000 width=12) (actual time=113.395..126.901 rows=100000 loops=1)
Sort Key: equipment_id, created_at DESC
Sort Method: external merge Disk: 2552kB
-> Seq Scan on geoposition_records (cost=0.00..1736.00 rows=100000 width=12) (actual time=0.021..21.966 rows=100000 loops=1)
Planning Time: 0.263 ms
Execution Time: 135.486 ms
 hidden batch(es)


-- slow EXPLAIN ANALYZE SELECT equipment_id, max(created_at) AS latest FROM geoposition_records GROUP BY 1;
QUERY PLAN
HashAggregate (cost=2236.00..2236.57 rows=57 width=12) (actual time=34.876..34.887 rows=57 loops=1)
Group Key: equipment_id
Batches: 1 Memory Usage: 32kB
-> Seq Scan on geoposition_records (cost=0.00..1736.00 rows=100000 width=12) (actual time=0.013..10.383 rows=100000 loops=1)
Planning Time: 0.134 ms
Execution Time: 34.970 ms
 hidden batch(es)


-- faster EXPLAIN ANALYZE SELECT * FROM f_latest_equip();
QUERY PLAN
Function Scan on f_latest_equip (cost=0.25..10.25 rows=1000 width=12) (actual time=3.171..3.175 rows=57 loops=1)
Planning Time: 0.033 ms
Execution Time: 3.239 ms
 hidden batch(es)


-- faster EXPLAIN ANALYZE SELECT equipment_id ,(SELECT max(created_at) FROM geoposition_records WHERE equipment_id = eq.equipment_id AND created_at IS NOT NULL) AS latest FROM equipment eq;
QUERY PLAN
Seq Scan on equipment eq (cost=0.00..123.64 rows=57 width=12) (actual time=0.028..0.604 rows=57 loops=1)
SubPlan 2
-> Result (cost=2.13..2.14 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=57)
InitPlan 1 (returns $1)
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=57)
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3006.83 rows=1754 width=8) (actual time=0.008..0.008 rows=1 loops=57)
Index Cond: ((equipment_id = eq.equipment_id) AND (created_at IS NOT NULL))
Heap Fetches: 57
Planning Time: 0.155 ms
Execution Time: 0.630 ms
 hidden batch(es)


-- fastest EXPLAIN ANALYZE SELECT equipment_id ,(SELECT created_at FROM geoposition_records WHERE equipment_id = eq.equipment_id ORDER BY created_at DESC NULLS LAST LIMIT 1) AS latest FROM equipment eq;
QUERY PLAN
Seq Scan on equipment eq (cost=0.00..122.92 rows=57 width=12) (actual time=0.024..0.502 rows=57 loops=1)
SubPlan 1
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=57)
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3002.45 rows=1754 width=8) (actual time=0.007..0.007 rows=1 loops=57)
Index Cond: (equipment_id = eq.equipment_id)
Heap Fetches: 57
Planning Time: 0.081 ms
Execution Time: 0.525 ms
 hidden batch(es)


-- fastest EXPLAIN ANALYZE SELECT eq.equipment_id, r.latest FROM equipment eq LEFT JOIN LATERAL ( SELECT created_at FROM geoposition_records WHERE equipment_id = eq.equipment_id ORDER BY created_at DESC NULLS LAST LIMIT 1 ) r(latest) ON true;
QUERY PLAN
Nested Loop Left Join (cost=0.42..124.06 rows=57 width=12) (actual time=0.118..0.669 rows=57 loops=1)
-> Seq Scan on equipment eq (cost=0.00..1.57 rows=57 width=4) (actual time=0.101..0.110 rows=57 loops=1)
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=57)
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3002.45 rows=1754 width=8) (actual time=0.007..0.007 rows=1 loops=57)
Index Cond: (equipment_id = eq.equipment_id)
Heap Fetches: 57
Planning Time: 0.100 ms
Execution Time: 0.693 ms
 hidden batch(es)