Postgres 10

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 sensor_time_series
(
    id SERIAL PRIMARY KEY,
    "time" TIMESTAMP NOT NULL,
    sensor_id INTEGER NOT NULL,
    value NUMERIC NOT NULL
);
INSERT INTO sensor_time_series (id,sensor_id,time,value)
VALUES
(1,1,'2018-01-01 00:00:01',1),
(2,1,'2018-01-01 00:00:02',2),
(3,1,'2018-01-01 00:00:03',3),
(4,1,'2018-01-01 00:00:03',4),
(5,1,'2018-01-01 00:00:04',3),
(6,2,'2018-01-01 00:00:01',1),
(7,2,'2018-01-01 00:00:01',2),
(8,2,'2018-01-01 00:00:02',3),
(9,2,'2018-01-01 00:00:03',4),
(10,2,'2018-01-01 00:00:04',5);
10 rows affected

SELECT * FROM sensor_time_series;


id
time
sensor_id
value


1
2018-01-01 00:00:01
1
1

2
2018-01-01 00:00:02
1
2

3
2018-01-01 00:00:03
1
3

4
2018-01-01 00:00:03
1
4

5
2018-01-01 00:00:04
1
3

6
2018-01-01 00:00:01
2
1

7
2018-01-01 00:00:01
2
2

8
2018-01-01 00:00:02
2
3

9
2018-01-01 00:00:03
2
4

10
2018-01-01 00:00:04
2
5


SELECT t1.id, t2.id
FROM sensor_time_series t1
INNER JOIN sensor_time_series t2 ON t1.sensor_id = t2.sensor_id AND t1."time" < t2."time"
LEFT JOIN sensor_time_series t3 ON  t1.sensor_id = t3.sensor_id AND t1."time" < t3."time" AND t3."time" < t2."time"
WHERE t3.id IS NULL;


id
id


1
2

2
3

2
4

3
5

4
5

6
8

7
8

8
9

9
10


SELECT t1.id, t2.id
FROM (SELECT id, sensor_id, lead("time") over(partition by sensor_id order by time) AS leading
      FROM sensor_time_series ) t1
INNER JOIN sensor_time_series t2 ON t1.sensor_id = t2.sensor_id AND t1.id != t2.id
WHERE t2."time" = t1.leading;


id
id


1
2

2
3

2
4

3
4

4
5

6
7

7
8

8
9

9
10

id	time	sensor_id	value
1	2018-01-01 00:00:01	1	1
2	2018-01-01 00:00:02	1	2
3	2018-01-01 00:00:03	1	3
4	2018-01-01 00:00:03	1	4
5	2018-01-01 00:00:04	1	3
6	2018-01-01 00:00:01	2	1
7	2018-01-01 00:00:01	2	2
8	2018-01-01 00:00:02	2	3
9	2018-01-01 00:00:03	2	4
10	2018-01-01 00:00:04	2	5