Postgres 16

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 t (id int, score int);

CREATE TABLE


insert into t (id, score) values
  (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), 
  (6, 60), (7, 30), (8, 30), (9, 90), (10, 100);

INSERT 0 10


with
x as (
  select *,
    sum(case when score = 30 then 0 else 1 end) over(order by id) as s
  from t
)
select a.id, b.score
from x a
left join x b on (b.s = a.s - 1 and a.score <> 30 or b.s = a.s and a.score = 30)
             and b.score <> 30


id
score


1
null

2
10

3
20

4
20

5
40

6
50

7
60

8
60

9
60

10
90



SELECT 10

id	score
1	null
2	10
3	20
4	20
5	40
6	50
7	60
8	60
9	60
10	90