Postgres 15

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 SERIAL PRIMARY KEY,
  num int,
  t text,
  constraint u_constrainte unique (num, t)
)

CREATE TABLE


insert into test(num, t)
select i, 'test' from generate_series(1, 10) as t(i)

INSERT 0 10


update test set num = num + 1 from (
    SELECT
      id
    FROM
      test
    ORDER BY
      id desc
  ) as t
  where t.id = test.id;

UPDATE 10


select *
from test order by id desc


id
num
t


10
11
test

9
10
test

8
9
test

7
8
test

6
7
test

5
6
test

4
5
test

3
4
test

2
3
test

1
2
test



SELECT 10

id	num	t
10	11	test
9	10	test
8	9	test
7	8	test
6	7	test
5	6	test
4	5	test
3	4	test
2	3	test
1	2	test