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 test (
  product_id INT,
  user_id INT,
  flag INT
);
INSERT INTO test VALUES
(1,1,NULL), (1,1,NULL), (1,1,NULL), 
(2,1,1), (2,1,1), (2,1,1), 
(3,1,NULL), (3,1,NULL), (3,1,NULL);
SELECT * FROM test;

CREATE TABLE



INSERT 0 9




product_id
user_id
flag


1
1
null

1
1
null

1
1
null

2
1
1

2
1
1

2
1
1

3
1
null

3
1
null

3
1
null



SELECT 9


SELECT *,
       SUM((flag IS NULL) :: INT) OVER (PARTITION BY user_id ORDER BY product_id ASC) grp
FROM test
ORDER BY 1,2;


product_id
user_id
flag
grp


1
1
null
3

1
1
null
3

1
1
null
3

2
1
1
3

2
1
1
3

2
1
1
3

3
1
null
6

3
1
null
6

3
1
null
6



SELECT 9


WITH cte AS (
  SELECT *,
         SUM((flag IS NULL) :: INT) OVER (PARTITION BY user_id ORDER BY product_id ASC) grp
  FROM test
  )
SELECT product_id, user_id, flag,
       DENSE_RANK() OVER (PARTITION BY user_id ORDER BY grp ASC) "rank"
FROM cte
ORDER BY 1,2;


product_id
user_id
flag
rank


1
1
null
1

1
1
null
1

1
1
null
1

2
1
1
1

2
1
1
1

2
1
1
1

3
1
null
2

3
1
null
2

3
1
null
2



SELECT 9

product_id	user_id	flag
1	1	null
1	1	null
1	1	null
2	1	1
2	1	1
2	1	1
3	1	null
3	1	null
3	1	null

product_id	user_id	flag	grp
1	1	null	3
1	1	null	3
1	1	null	3
2	1	1	3
2	1	1	3
2	1	1	3
3	1	null	6
3	1	null	6
3	1	null	6

product_id	user_id	flag	rank
1	1	null	1
1	1	null	1
1	1	null	1
2	1	1	1
2	1	1	1
2	1	1	1
3	1	null	2
3	1	null	2
3	1	null	2