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 have (
    n int,
    x int
) ;
insert into have (n,x)
values (1,0),(2,1),(3,2),(4,3),(5,4),(6,2),(7,1),(8,0),(9,1),(10,0),(11,0),(12,0),(13,0),(14,1),(15,2),(16,3),(17,4)
returning *;

CREATE TABLE




n
x


1
0

2
1

3
2

4
3

5
4

6
2

7
1

8
0

9
1

10
0

11
0

12
0

13
0

14
1

15
2

16
3

17
4



INSERT 0 17


--Both start at zero. If at any point in time x > 2 then def = 1. 
--Now if def = 1 then in order for it to become zero again, 
--x has to equal zero for 3 consecutive periods otherwise it remains equal to 1.
with cte as (
    select *
      ,(x > 2) exceeded_2
      ,(every(x=0)over w1) as should_switch
    from have
    window w1 as (order by n rows 2 preceding) )
,cte2 as (
    select *,sum(should_switch::int)over(order by n) def_on_period 
    from cte
)
select n,x,(bool_or(exceeded_2) over w2)::int as def
from cte2
window w2 as (partition by def_on_period 
              order by n);


n
x
def


1
0
0

2
1
0

3
2
0

4
3
1

5
4
1

6
2
1

7
1
1

8
0
1

9
1
1

10
0
1

11
0
1

12
0
0

13
0
0

14
1
0

15
2
0

16
3
1

17
4
1



SELECT 17


--Both start at zero. If at any point in time x > 2 then def = 1. 
--Now if def = 1 then in order for it to become zero again, 
--x has to equal zero for 3 consecutive periods otherwise it remains equal to 1.
with cte as (
    select *
      ,(x > 2) exceeded_2
      ,(0 = all(array[     x
                      ,lag(x,1,0)over w1
                      ,lag(x,2,0)over w1
                     ]
                )
        ) as should_switch
    from have
    window w1 as (order by n) )
,cte2 as (
    select *,sum(should_switch::int)over(order by n) def_on_period 
    from cte
)
select n,x,(bool_or(exceeded_2) over w2)::int as def
from cte2
window w2 as (partition by def_on_period 
              order by n);


n
x
def


1
0
0

2
1
0

3
2
0

4
3
1

5
4
1

6
2
1

7
1
1

8
0
1

9
1
1

10
0
1

11
0
1

12
0
0

13
0
0

14
1
0

15
2
0

16
3
1

17
4
1



SELECT 17