Postgres 10

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, b varchar(10));
insert into t(id, b) values
(1, 'value a'),
(2, 'value b'),
(3, 'value d'),
(4, 'value c'),
(5, 'value d'),
(6, 'value d'),
(7, 'value d'),
(8, 'value e'),
(9, 'value f'),
(10, 'value d');
10 rows affected

with cte1 as (
    select *, case when b = 'value d' then null else id end as grp_id
    from t
), cte2 as (
    select *, sum(case when grp_id is not null then 1 end) over (order by id) as grp_num
    from cte1
)
select *, case when b = 'value d' then max(grp_id) over (partition by grp_num) end as nearest_row_id
from cte2


id
b
grp_id
grp_num
nearest_row_id


1
value a
1
1
null

2
value b
2
2
null

3
value d
null
2
2

4
value c
4
3
null

5
value d
null
3
4

6
value d
null
3
4

7
value d
null
3
4

8
value e
8
4
null

9
value f
9
5
null

10
value d
null
5
9

id	b	grp_id	grp_num	nearest_row_id
1	value a	1	1	null
2	value b	2	2	null
3	value d	null	2	2
4	value c	4	3	null
5	value d	null	3	4
6	value d	null	3	4
7	value d	null	3	4
8	value e	8	4	null
9	value f	9	5	null
10	value d	null	5	9