Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE stations (id int PRIMARY KEY);  -- rest irrelevant
INSERT INTO stations (id) VALUES
  (147)
, (148)
, (149)
;

CREATE TABLE readings (station_id int, reading_id int, temp int, air_pressure int);
INSERT INTO readings VALUES
  (147, 100, 10, 800)
, (148, 101, 20, 850)
, (149, 102, 30, 900)
, (148, 103, 40, 950)
, (148, 104, 50, 1000)
, (147, 105, 60, 1050)
;

-- ideally, this index:
CREATE UNIQUE INDEX ON readings (station_id, reading_id DESC);
3 rows affected

6 rows affected

SELECT r.*
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT *
   FROM   readings r
   WHERE  r.station_id = s.id
   ORDER  BY reading_id DESC
   OFFSET 1
   LIMIT  1
   ) r
ORDER  BY r.station_id;  -- optional


station_id
reading_id
temp
air_pressure


147
100
10
800

148
103
40
950


SELECT (r).*
FROM  (
   SELECT r, dense_rank() OVER (PARTITION BY station_id ORDER BY reading_id DESC) AS rnk
   FROM   readings r
   ) r
WHERE  rnk = 2;


station_id
reading_id
temp
air_pressure


147
100
10
800

148
103
40
950


VACUUM ANALYZE readings;
VACUUM ANALYZE stations;
set enable_seqscan = off;  -- to see how index is applicable (despite minimal sample)
EXPLAIN ANALYZE
SELECT (r).*
FROM  (
   SELECT r, dense_rank() OVER (PARTITION BY station_id ORDER BY reading_id DESC) AS rnk
   FROM   readings r
   ) r
WHERE  rnk = 2;


QUERY PLAN


Subquery Scan on r  (cost=0.13..12.40 rows=1 width=16) (actual time=0.093..0.106 rows=2 loops=1)

  Filter: (r.rnk = 2)

  Rows Removed by Filter: 4

  ->  WindowAgg  (cost=0.13..12.33 rows=6 width=56) (actual time=0.085..0.100 rows=6 loops=1)

        ->  Index Scan using readings_station_id_reading_id_idx on readings r_1  (cost=0.13..12.22 rows=6 width=48) (actual time=0.060..0.067 rows=6 loops=1)

Planning Time: 0.210 ms

Execution Time: 0.179 ms


EXPLAIN ANALYZE
SELECT r.*
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT *
   FROM   readings r
   WHERE  r.station_id = s.id
   ORDER  BY reading_id DESC
   OFFSET 1
   LIMIT  1
   ) r
ORDER  BY r.station_id;  -- optional


QUERY PLAN


Sort  (cost=32.76..32.77 rows=3 width=16) (actual time=0.116..0.118 rows=2 loops=1)

  Sort Key: r.station_id

  Sort Method: quicksort  Memory: 25kB

  ->  Nested Loop  (cost=4.28..32.74 rows=3 width=16) (actual time=0.094..0.106 rows=2 loops=1)

        ->  Index Only Scan using stations_pkey on stations s  (cost=0.13..8.18 rows=3 width=4) (actual time=0.039..0.041 rows=3 loops=1)

              Heap Fetches: 0

        ->  Limit  (cost=4.15..8.17 rows=1 width=16) (actual time=0.019..0.019 rows=1 loops=3)

              ->  Index Scan using readings_station_id_reading_id_idx on readings r  (cost=0.13..8.17 rows=2 width=16) (actual time=0.017..0.018 rows=2 loops=3)

                    Index Cond: (station_id = s.id)

Planning Time: 0.274 ms

Execution Time: 0.173 ms