Postgres 9.4

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 table1(
     id serial,
     name varchar(255),
     spent float
     
);

insert into table1(name,spent)values('a',5.5),('b',16),('c',7),
('a',3.2),('b',4.6),('a',3),('c',1.2);
7 rows affected

select * from table1 order by name;


id
name
spent


4
a
3.2

1
a
5.5

6
a
3

5
b
4.6

2
b
16

7
c
1.2

3
c
7


UPDATE table1
SET spent = 1
WHERE id IN (SELECT id 
             FROM table1
             WHERE name = 'a'
             ORDER BY id
             LIMIT 2)
2 rows affected

select * from table1 order by name;


id
name
spent


6
a
3

1
a
1

4
a
1

2
b
16

5
b
4.6

7
c
1.2

3
c
7