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) DEFERRABLE 
)

CREATE TABLE


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

INSERT 0 10


select *
from test


id
num
t


1
1
test

2
2
test

3
3
test

4
4
test

5
5
test

6
6
test

7
7
test

8
8
test

9
9
test

10
10
test



SELECT 10


update test set num = num - 1

UPDATE 10


select *
from test


id
num
t


1
0
test

2
1
test

3
2
test

4
3
test

5
4
test

6
5
test

7
6
test

8
7
test

9
8
test

10
9
test



SELECT 10


update test set num = num + 1 

UPDATE 10


select *
from test


id
num
t


1
1
test

2
2
test

3
3
test

4
4
test

5
5
test

6
6
test

7
7
test

8
8
test

9
9
test

10
10
test



SELECT 10

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