Postgres 10

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?.

drop table if exists transactions;
create table transactions(id int, month int, amount int, percentile dec);
insert into transactions values
(1, 1, 1),
(2, 1, 2),
(3, 1, 5),
(4, 1, 3),
(5, 2, 1),
(6, 2, 3),
(1, 2, 5),
(1, 2, 7),
(1, 2, 9),
(1, 2, 11),
(1, 2, 15),
(1, 2, 16);
12 rows affected

update transactions t
set percentile = calc_percentile
from (
    select distinct on (month, amount) 
        id, 
        month, 
        amount, 
        calc_percentile
    from transactions
    join (
        select 
            bucket,
            month as calc_month, 
            percentile_cont(bucket*1.0/4) within group (order by amount asc) as calc_amount,
            bucket*100/4 as calc_percentile
        from transactions 
        cross join generate_series(1, 4) bucket
        group by month, bucket
        ) s on month = calc_month and amount <= calc_amount
    order by month, amount, calc_percentile 
    ) s
where t.month = s.month and t.amount = s.amount;
12 rows affected

select *
from transactions
order by month, amount;


id
month
amount
percentile


1
1
1
25

2
1
2
50

4
1
3
75

3
1
5
100

5
2
1
25

6
2
3
25

1
2
5
50

1
2
7
50

1
2
9
75

1
2
11
75

1
2
15
100

1
2
16
100

id	month	amount	percentile
1	1	1	25
2	1	2	50
4	1	3	75
3	1	5	100
5	2	1	25
6	2	3	25
1	2	5	50
1	2	7	50
1	2	9	75
1	2	11	75
1	2	15	100
1	2	16	100

id	month	amount	percentile
1	1	1	25
2	1	2	50
4	1	3	75
3	1	5	100
5	2	1	25
6	2	3	25
1	2	5	50
1	2	7	50
1	2	9	75
1	2	11	75
1	2	15	100
1	2	16	100

id	month	amount	percentile
1	1	1	25
2	1	2	50
4	1	3	75
3	1	5	100
5	2	1	25
6	2	3	25
1	2	5	50
1	2	7	50
1	2	9	75
1	2	11	75
1	2	15	100
1	2	16	100