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 test (id INT PRIMARY KEY, other_field INT);
INSERT INTO test VALUES (3,333),(55,555),(777,777);
SELECT * FROM test;

CREATE TABLE

INSERT 0 3

id
other_field

3
333

55
555

777
777

SELECT 3

ALTER TABLE test ADD COLUMN continuous INT;
SELECT * FROM test;

ALTER TABLE

id
other_field
continuous

3
333
null

55
555
null

777
777
null

SELECT 3

UPDATE test
SET continuous = calculate_rownumber.rownumber
FROM ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) rownumber
       FROM test ) calculate_rownumber
WHERE test.id = calculate_rownumber.id;
SELECT * FROM test;

UPDATE 3

id
other_field
continuous

3
333
1

55
555
2

777
777
3

SELECT 3