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 mytable (id, seq) AS
VALUES
(8, 1),(3, 2),(2, 3),(null, 4),(324, 5),(82, 6),(89, 7),(null, 8),(1, 9),(2, 10),(90, 10),(null, 11);

SELECT * FROM mytable

SELECT 12




id
seq


8
1

3
2

2
3

null
4

324
5

82
6

89
7

null
8

1
9

2
10

90
10

null
11



SELECT 12


SELECT
    CASE WHEN id IS NULL THEN 1 ELSE 0 END as grp_index,
    id
FROM mytable


grp_index
id


0
8

0
3

0
2

1
null

0
324

0
82

0
89

1
null

0
1

0
2

0
90

1
null



SELECT 12


SELECT
    SUM(CASE WHEN id IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY seq) as grp,
    id
FROM mytable
   
    


grp
id


0
8

0
3

0
2

1
null

1
324

1
82

1
89

2
null

2
1

2
2

2
90

3
null



SELECT 12


SELECT
    SUM(CASE WHEN next_id IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY seq) as grp,
    id
FROM (
    SELECT
        LAG(id) OVER (ORDER BY seq) as next_id,
        seq,
        id
    FROM mytable
) s


grp
id


1
8

1
3

1
2

1
null

2
324

2
82

2
89

2
null

3
1

3
2

3
90

3
null



SELECT 12

id	seq
8	1
3	2
2	3
null	4
324	5
82	6
89	7
null	8
1	9
2	10
90	10
null	11

grp_index	id
0	8
0	3
0	2
1	null
0	324
0	82
0	89
1	null
0	1
0	2
0	90
1	null

grp	id
0	8
0	3
0	2
1	null
1	324
1	82
1	89
2	null
2	1
2	2
2	90
3	null

grp	id
1	8
1	3
1	2
1	null
2	324
2	82
2	89
2	null
3	1
3	2
3	90
3	null