Postgres 16

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 (
  payment_id int, 
  amount int, 
  category varchar(50)
  );

CREATE TABLE


insert into table1 values 
(1, 5, 'action'), 
(2, 10, 'horror'), 
(3, 2, 'action'), 
(4, 6 ,'drama'), 
(5, 1, 'action');

INSERT 0 5


select 
 payment_id, 
 amount, 
 category, 
 sum(amount) over (partition by category) as category_total
from 
 table1
order by 
 category, payment_id


payment_id
amount
category
category_total


1
5
action
8

3
2
action
8

5
1
action
8

4
6
drama
6

2
10
horror
10



SELECT 5