Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE observations (
   id         serial
 , station_id int
 , created_at timestamp
 , foo        text
);

INSERT INTO observations (station_id, created_at, foo)
SELECT ceil(random() * 10)::int    AS station_id -- few stations
     , '2014-01-01 0:0'::timestamp 
       + interval '1s' * g         AS created_at -- ascending (probably like real life)
     , 'aöguihaophgaduigha' || g   AS foo        -- random ballast
FROM   generate_series (1, 100000) g;              -- 100k rows

CREATE INDEX observations_special_idx ON observations(station_id, created_at DESC, id);

-- Or with separare stations table:
CREATE TABLE stations (station_id int PRIMARY KEY);
INSERT INTO stations
SELECT generate_series (1, 10);

CREATE TABLE



INSERT 0 100000



CREATE INDEX



CREATE TABLE



INSERT 0 10


-- Demos with just the latest 3 observations per station
-- With table stations
SELECT s.station_id, o.created_at, o.id
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT o.id, o.created_at
   FROM   observations o
   WHERE  o.station_id = s.station_id
   ORDER  BY o.created_at DESC
   LIMIT  3  -- exaple value
   ) o
ORDER  BY s.station_id, o.created_at DESC;


station_id
created_at
id


1
2014-01-02 03:46:31
99991

1
2014-01-02 03:46:27
99987

1
2014-01-02 03:46:21
99981

2
2014-01-02 03:46:26
99986

2
2014-01-02 03:46:13
99973

2
2014-01-02 03:46:11
99971

3
2014-01-02 03:46:28
99988

3
2014-01-02 03:46:19
99979

3
2014-01-02 03:46:17
99977

4
2014-01-02 03:46:22
99982

4
2014-01-02 03:46:20
99980

4
2014-01-02 03:46:10
99970

5
2014-01-02 03:46:34
99994

5
2014-01-02 03:46:33
99993

5
2014-01-02 03:46:25
99985

6
2014-01-02 03:46:40
100000

6
2014-01-02 03:46:39
99999

6
2014-01-02 03:46:05
99965

7
2014-01-02 03:46:37
99997

7
2014-01-02 03:46:32
99992

7
2014-01-02 03:46:08
99968

8
2014-01-02 03:46:24
99984

8
2014-01-02 03:46:23
99983

8
2014-01-02 03:46:01
99961

9
2014-01-02 03:46:29
99989

9
2014-01-02 03:46:14
99974

9
2014-01-02 03:46:12
99972

10
2014-01-02 03:46:38
99998

10
2014-01-02 03:46:36
99996

10
2014-01-02 03:46:35
99995



SELECT 30


-- Without table stations:
WITH RECURSIVE stations AS (
   (
   SELECT station_id
   FROM   observations
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT o.station_id
           FROM   observations o
           WHERE  o.station_id > s.station_id
           ORDER  BY o.station_id
           LIMIT  1)
   FROM   stations s
   WHERE  s.station_id IS NOT NULL
   )
SELECT s.station_id, o.created_at, o.id
FROM   stations s
CROSS  JOIN LATERAL (
   SELECT o.id, o.created_at
   FROM   observations o
   WHERE  o.station_id = s.station_id
   ORDER  BY o.created_at DESC
   LIMIT  3  -- example value
   ) o
WHERE  s.station_id IS NOT NULL
ORDER  BY s.station_id, o.created_at DESC;


station_id
created_at
id


1
2014-01-02 03:46:31
99991

1
2014-01-02 03:46:27
99987

1
2014-01-02 03:46:21
99981

2
2014-01-02 03:46:26
99986

2
2014-01-02 03:46:13
99973

2
2014-01-02 03:46:11
99971

3
2014-01-02 03:46:28
99988

3
2014-01-02 03:46:19
99979

3
2014-01-02 03:46:17
99977

4
2014-01-02 03:46:22
99982

4
2014-01-02 03:46:20
99980

4
2014-01-02 03:46:10
99970

5
2014-01-02 03:46:34
99994

5
2014-01-02 03:46:33
99993

5
2014-01-02 03:46:25
99985

6
2014-01-02 03:46:40
100000

6
2014-01-02 03:46:39
99999

6
2014-01-02 03:46:05
99965

7
2014-01-02 03:46:37
99997

7
2014-01-02 03:46:32
99992

7
2014-01-02 03:46:08
99968

8
2014-01-02 03:46:24
99984

8
2014-01-02 03:46:23
99983

8
2014-01-02 03:46:01
99961

9
2014-01-02 03:46:29
99989

9
2014-01-02 03:46:14
99974

9
2014-01-02 03:46:12
99972

10
2014-01-02 03:46:38
99998

10
2014-01-02 03:46:36
99996

10
2014-01-02 03:46:35
99995



SELECT 30

station_id	created_at	id
1	2014-01-02 03:46:31	99991
1	2014-01-02 03:46:27	99987
1	2014-01-02 03:46:21	99981
2	2014-01-02 03:46:26	99986
2	2014-01-02 03:46:13	99973
2	2014-01-02 03:46:11	99971
3	2014-01-02 03:46:28	99988
3	2014-01-02 03:46:19	99979
3	2014-01-02 03:46:17	99977
4	2014-01-02 03:46:22	99982
4	2014-01-02 03:46:20	99980
4	2014-01-02 03:46:10	99970
5	2014-01-02 03:46:34	99994
5	2014-01-02 03:46:33	99993
5	2014-01-02 03:46:25	99985
6	2014-01-02 03:46:40	100000
6	2014-01-02 03:46:39	99999
6	2014-01-02 03:46:05	99965
7	2014-01-02 03:46:37	99997
7	2014-01-02 03:46:32	99992
7	2014-01-02 03:46:08	99968
8	2014-01-02 03:46:24	99984
8	2014-01-02 03:46:23	99983
8	2014-01-02 03:46:01	99961
9	2014-01-02 03:46:29	99989
9	2014-01-02 03:46:14	99974
9	2014-01-02 03:46:12	99972
10	2014-01-02 03:46:38	99998
10	2014-01-02 03:46:36	99996
10	2014-01-02 03:46:35	99995