Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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$;

CREATE TABLE

INSERT 0 100000

ALTER TABLE

CREATE INDEX

SELECT 57

ANALYZE

ANALYZE

CREATE FUNCTION

-- 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=89.141..105.614 rows=57 loops=1)

  ->  Sort  (cost=10040.82..10290.82 rows=100000 width=12) (actual time=89.138..99.118 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.018..20.364 rows=100000 loops=1)

Planning Time: 0.265 ms

Execution Time: 106.055 ms

EXPLAIN

-- 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=28.620..28.640 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..8.852 rows=100000 loops=1)

Planning Time: 0.120 ms

Execution Time: 28.692 ms

EXPLAIN

-- 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=2.411..2.415 rows=57 loops=1)

Planning Time: 0.031 ms

Execution Time: 2.470 ms

EXPLAIN

-- 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.65 rows=57 width=12) (actual time=0.027..0.444 rows=57 loops=1)

  SubPlan 2

    ->  Result  (cost=2.13..2.14 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=57)

          InitPlan 1 (returns $1)

            ->  Limit  (cost=0.42..2.13 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=57)

                  ->  Index Only Scan using geo_rec_mult_idx on geoposition_records  (cost=0.42..3007.25 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57)

                        Index Cond: ((equipment_id = eq.equipment_id) AND (created_at IS NOT NULL))

                        Heap Fetches: 57

Planning Time: 0.131 ms

Execution Time: 0.465 ms

EXPLAIN

-- 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.94 rows=57 width=12) (actual time=0.022..0.380 rows=57 loops=1)

  SubPlan 1

    ->  Limit  (cost=0.42..2.13 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=57)

          ->  Index Only Scan using geo_rec_mult_idx on geoposition_records  (cost=0.42..3002.87 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57)

                Index Cond: (equipment_id = eq.equipment_id)

                Heap Fetches: 57

Planning Time: 0.067 ms

Execution Time: 0.399 ms

EXPLAIN

-- 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.08 rows=57 width=12) (actual time=0.023..0.400 rows=57 loops=1)

  ->  Seq Scan on equipment eq  (cost=0.00..1.57 rows=57 width=4) (actual time=0.008..0.014 rows=57 loops=1)

  ->  Limit  (cost=0.42..2.13 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=57)

        ->  Index Only Scan using geo_rec_mult_idx on geoposition_records  (cost=0.42..3002.87 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57)

              Index Cond: (equipment_id = eq.equipment_id)

              Heap Fetches: 57

Planning Time: 0.107 ms

Execution Time: 0.420 ms

EXPLAIN