Postgres 14

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(
  specimen integer
, day      integer
, period   integer
, area     integer
);

INSERT INTO tbl VALUES 
  (1,1,1,1)
, (1,1,2,1)
, (1,1,3,2)
, (1,1,4,2)
, (1,1,5,3)
, (1,1,6,2)
, (1,2,1,2)
, (1,2,2,1)
, (1,2,3,2)
, (1,2,4,3)
, (1,2,5,2)
, (1,2,6,1)
, (2,1,1,3)
, (2,1,2,3)
, (2,1,3,1)
;

CREATE TABLE



INSERT 0 15


SELECT specimen, day, period, area 
     , count(*) FILTER (WHERE step) OVER(PARTITION BY specimen, day ORDER BY period) AS mov
FROM  (
   SELECT *, lag(area) OVER (PARTITION BY specimen, day ORDER BY period) <> area AS step
   FROM   tbl
   ) sub
ORDER  BY specimen, day;


specimen
day
period
area
mov


1
1
1
1
0

1
1
2
1
0

1
1
3
2
1

1
1
4
2
1

1
1
5
3
2

1
1
6
2
3

1
2
1
2
0

1
2
2
1
1

1
2
3
2
2

1
2
4
3
3

1
2
5
2
4

1
2
6
1
5

2
1
1
3
0

2
1
2
3
0

2
1
3
1
1



SELECT 15


-- if period is an incremented number without gaps starting from 1 for every (specimen,  day) ...
SELECT t1.*
     , count(*) FILTER (WHERE t1.area <> t2.area) OVER (PARTITION BY t1.specimen, t1.day ORDER BY t1.period)
FROM   tbl t1
LEFT   JOIN tbl t2 ON t2.specimen = t1.specimen
                  AND t2.day      = t1.day
                  AND t2.period   = t1.period - 1
;


specimen
day
period
area
count


1
1
1
1
0

1
1
2
1
0

1
1
3
2
1

1
1
4
2
1

1
1
5
3
2

1
1
6
2
3

1
2
1
2
0

1
2
2
1
1

1
2
3
2
2

1
2
4
3
3

1
2
5
2
4

1
2
6
1
5

2
1
1
3
0

2
1
2
3
0

2
1
3
1
1



SELECT 15

specimen	day	period	area	mov
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1

specimen	day	period	area	count
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1

specimen	day	period	area	mov
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1

specimen	day	period	area	count
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1

specimen	day	period	area	mov
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1

specimen	day	period	area	count
1	1	1	1	0
1	1	2	1	0
1	1	3	2	1
1	1	4	2	1
1	1	5	3	2
1	1	6	2	3
1	2	1	2	0
1	2	2	1	1
1	2	3	2	2
1	2	4	3	3
1	2	5	2	4
1	2	6	1	5
2	1	1	3	0
2	1	2	3	0
2	1	3	1	1