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 (
lap_id serial PRIMARY KEY
, lap_no int NOT NULL
, car_type text NOT NULL
, race_id int NOT NULL
, UNIQUE(race_id, car_type, lap_no)
);
INSERT INTO tbl VALUES
(1, 1, 'red', 1)
,(2, 2, 'red', 1)
,(3, 3, 'red', 1)
,(4, 4, 'red', 1)
,(5, 1, 'blue', 1)
,(6, 5, 'red', 1)
,(7, 2, 'blue', 1)
,(8, 1, 'green', 1)
,(9, 6, 'green', 1) -- note: no lap 6 for red / 1
,(10, 7, 'red', 1) -- another distinct sequence with red / 1
,(11, 8, 'red', 1)
,(12, 9, 'red', 1)
,(13, 10, 'red', 1)
,(14, 18, 'red', 1) -- more sequences to demonstrate the new version works
,(15, 19, 'red', 1)
,(16, 20, 'red', 1)
,(17, 30, 'red', 1)
,(18, 29, 'red', 1)
,(19, 28, 'red', 1);
CREATE TABLE
INSERT 0 19
-- generic solution
SELECT race_id, car_type, count(*) AS seq_len
FROM (
SELECT *, count(*) FILTER (WHERE step)
OVER (ORDER BY race_id, car_type, lap_no) AS grp
FROM (
SELECT *, (lag(lap_no) OVER (PARTITION BY race_id, car_type ORDER BY lap_no) + 1)
IS DISTINCT FROM lap_no AS step
FROM tbl
) x
) y
GROUP BY race_id, car_type, grp
ORDER BY seq_len DESC
LIMIT 1;
| race_id | car_type | seq_len |
|---|---|---|
| 1 | red | 5 |
SELECT 1
-- simpler for consecutive lap_no:
SELECT race_id, car_type, count(*) AS seq_len
FROM (
SELECT *
, row_number() OVER (ORDER BY race_id, car_type, lap_no) - lap_no AS grp
FROM tbl
) x
GROUP BY race_id, car_type, grp
ORDER BY seq_len DESC
LIMIT 1;
| race_id | car_type | seq_len |
|---|---|---|
| 1 | red | 5 |
SELECT 1
-- the same with PARTITION BY:
SELECT race_id, car_type, count(*) AS seq_len
FROM (
SELECT *
, row_number() OVER (PARTITION BY race_id, car_type ORDER BY lap_no) - lap_no AS grp
FROM tbl
) x
GROUP BY race_id, car_type, grp
ORDER BY seq_len DESC
LIMIT 1;
| race_id | car_type | seq_len |
|---|---|---|
| 1 | red | 5 |
SELECT 1
-- one given race/car type:
SELECT count(*) AS seq_len
FROM (
SELECT row_number() OVER (ORDER BY lap_no) - lap_no AS grp
FROM tbl
WHERE race_id = 1
AND car_type = 'red'
) x
GROUP BY grp
ORDER BY seq_len DESC
LIMIT 1;
| seq_len |
|---|
| 5 |
SELECT 1
-- Jack's simple version **fails** for multiple sequences of one car_type in one race
SELECT car_type, race_id
, sum(case when lap_no = (prev+1) THEN 1 ELSE 0 END) + 1 AS wrong_seq_len
FROM (
SELECT *, lag(lap_no) OVER (PARTITION BY car_type, race_id ORDER BY lap_no) prev
FROM tbl) z
GROUP BY car_type, race_id
ORDER BY wrong_seq_len DESC
LIMIT 1;
| car_type | race_id | wrong_seq_len |
|---|---|---|
| red | 1 | 12 |
SELECT 1