Postgres 13

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(v bigint, data jsonb DEFAULT '{}'::jsonb);
INSERT INTO test(v) SELECT vv FROM generate_series(0, 10000) as vv;
CREATE UNIQUE INDEX uniq_ind ON test(v);

CREATE TABLE

INSERT 0 10001

CREATE INDEX

SELECT * FROM test ORDER BY v LIMIT 5;

v
data

0
{}

1
{}

2
{}

3
{}

4
{}

SELECT 5

-- fails:
UPDATE test SET v = v + 1;

ERROR:  duplicate key value violates unique constraint "uniq_ind"
DETAIL:  Key (v)=(1) already exists.

-- works:
UPDATE test t
SET    v = t.v + 1
FROM  (SELECT * FROM test ORDER BY v DESC) t_ordered  -- put WHERE clauses here
WHERE  t_ordered.v = t.v;

UPDATE 10001

SELECT * FROM test ORDER BY v LIMIT 5;

v
data

1
{}

2
{}

3
{}

4
{}

5
{}

SELECT 5