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 it221003 (
  id int
, article text
, itsorder int
);

insert into it221003 values
(1, 'A12', 1),
(2, 'A15', 5),
(3, 'A25', 10),
(4, 'A28', 15),
(5, 'A31', 20),
(6, 'A35', 25);

CREATE TABLE



INSERT 0 6


update it221003
   set itsorder = 2
 where id = 6;

UPDATE 1


select *
  from it221003;


id
article
itsorder


1
A12
1

2
A15
5

3
A25
10

4
A28
15

5
A31
20

6
A35
2



SELECT 6


update it221003 i
   set itsorder = case 
                     when a.rn = 1 then 1
                     else (a.rn - 1) * 5
                   end
  from (select id
             , rank() over(order by itsorder) rn
          from it221003) a
 where i.id = a.id;

UPDATE 6


select *
  from it221003;


id
article
itsorder


1
A12
1

2
A15
10

3
A25
15

4
A28
20

5
A31
25

6
A35
5



SELECT 6