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 students (
id int,
score int,
section varchar(10)
);
insert into students values
(1 , 85 , 'A'),
(2 , 40 , 'A'),
(3 , 61 , 'B'),
(4 , 71 , 'B'),
(5, 80 , 'B');
CREATE TABLE
INSERT 0 5
select id, score, section, max(score) over ( partition by section) as section_max_score
from students
| id | score | section | section_max_score |
|---|---|---|---|
| 1 | 85 | A | 85 |
| 2 | 40 | A | 85 |
| 3 | 61 | B | 80 |
| 4 | 71 | B | 80 |
| 5 | 80 | B | 80 |
SELECT 5
select s.*, t.section_max_score
from students s
inner join (
select section, max(score) as section_max_score
from students
group by section
) as t on s.section = t.section
| id | score | section | section_max_score |
|---|---|---|---|
| 1 | 85 | A | 85 |
| 2 | 40 | A | 85 |
| 3 | 61 | B | 80 |
| 4 | 71 | B | 80 |
| 5 | 80 | B | 80 |
SELECT 5