add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 tbl (id int, device_id int, fuel int);
INSERT INTO tbl VALUES
(1, 19, 100),
(2, 19, 97),
(3, 19, 100),
(4, 19, 96),
(5, 19, 94),
(6, 19, 66),
(7, 19, 33),
(8, 19, 31),
(9, 19, 30),
(10, 19, 10),
(11, 19, 6),
(12, 19, 5),
(13, 19, 6),
(14, 19, 100),
(15, 19, 99),
(16, 19, 98),
(17, 19, 100),
(18, 19, 99),
(19, 19, 97),
(20, 19, 96),
(21, 19, 90);
21 rows affected
SELECT *
, fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
FROM tbl;

id device_id fuel fillup
1 19 100 t
2 19 97 f
3 19 100 f
4 19 96 f
5 19 94 f
6 19 66 f
7 19 33 f
8 19 31 f
9 19 30 f
10 19 10 f
11 19 6 f
12 19 5 f
13 19 6 f
14 19 100 t
15 19 99 f
16 19 98 f
17 19 100 f
18 19 99 f
19 19 97 f
20 19 96 f
21 19 90 f
SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank
FROM (
SELECT *
, fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
FROM tbl
) sub1;

id device_id fuel fillup tank
1 19 100 t 1
2 19 97 f 1
3 19 100 f 1
4 19 96 f 1
5 19 94 f 1
6 19 66 f 1
7 19 33 f 1
8 19 31 f 1
9 19 30 f 1
10 19 10 f 1
11 19 6 f 1
12 19 5 f 1
13 19 6 f 1
14 19 100 t 2
15 19 99 f 2
16 19 98 f 2
17 19 100 f 2
18 19 99 f 2
19 19 97 f 2
20 19 96 f 2
21 19 90 f 2
SELECT *
FROM (
SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank
FROM (
SELECT *
, fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
FROM tbl
) sub1
) sub2
WHERE device_id = 19
AND tank = 1;

id device_id fuel fillup tank
1 19 100 t 1
2 19 97 f 1
3 19 100 f 1
4 19 96 f 1
5 19 94 f 1
6 19 66 f 1
7 19 33 f 1
8 19 31 f 1
9 19 30 f 1
10 19 10 f 1
11 19 6 f 1
12 19 5 f 1
13 19 6 f 1