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 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