Postgres 15

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

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE mytable(
   ID     INT  NOT NULL 
  ,Date   DATE  NOT NULL
  ,Person VARCHAR(8) NOT NULL
  ,Status VARCHAR(15) NOT NULL
);
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-01','API','NEW');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-02','REMCO','OKAY');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-03','API','RECALC');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-04','RUUD','OKAY');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-05','API','RECALC');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-06','MICHAEL','OKAY EXTRA INFO');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-07','ROY','OKAY');
INSERT INTO mytable(ID,Date,Person,Status) VALUES (1,'2020-01-08','ROY','OKAY');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select  t.*,
    1 + coalesce(
        sum(case when person != 'API' then 1 else 0 end)
        over(
            partition by id
            order by date
            rows between unbounded preceding and 1 preceding
        ),
        0
      ) as grp
from mytable t
order by id, date


id
date
person
status
grp


1
2020-01-01
API
NEW
1

1
2020-01-02
REMCO
OKAY
1

1
2020-01-03
API
RECALC
2

1
2020-01-04
RUUD
OKAY
2

1
2020-01-05
API
RECALC
3

1
2020-01-06
MICHAEL
OKAY EXTRA INFO
3

1
2020-01-07
ROY
OKAY
4

1
2020-01-08
ROY
OKAY
5



SELECT 8

id	date	person	status	grp
1	2020-01-01	API	NEW	1
1	2020-01-02	REMCO	OKAY	1
1	2020-01-03	API	RECALC	2
1	2020-01-04	RUUD	OKAY	2
1	2020-01-05	API	RECALC	3
1	2020-01-06	MICHAEL	OKAY EXTRA INFO	3
1	2020-01-07	ROY	OKAY	4
1	2020-01-08	ROY	OKAY	5