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 (number int, status varchar(10));
insert into t values( 1 ,'g');
insert into t values( 2 ,'a');
insert into t values(3 ,'b');
insert into t values(4 ,'c');
insert into t values(5 ,'d');
insert into t values(6 ,'e');
insert into t values(7 ,'f');
insert into t values(8 ,'b');
insert into t values(9 ,'e');
insert into t values(10 , 'a');
insert into t values(11 ,'c');
insert into t values(12 ,'f');
insert into t values(13 ,'g');
insert into t values(14 ,'d');
insert into t values(15 ,'a');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select t.number, t.status
from (select t.*,
(count(*) filter (where status = 'a') over (order by number) -
count(*) filter (where status = 'f') over (order by number rows between unbounded preceding and 1 preceding)
) as grp,
min(number) filter (where status = 'f') over (order by number desc) as next_f
from t
) t
where grp > 0 and next_f is not null
| number | status |
|---|---|
| 2 | a |
| 3 | b |
| 4 | c |
| 5 | d |
| 6 | e |
| 7 | f |
| 10 | a |
| 11 | c |
| 12 | f |