Postgres 9.6

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 test (
    id int,
    name text,
    amount int,
    ratio int
);

INSERT INTO test VALUES
(2, 'a', 3, 1),
(3, 'a', 8, 2),
(4, 'a', 3, 31),
(5, 'a', 2, 10),
(6, 'a', 4, 15),
(7, 'b', 2, 42),
(8, 'b', 1, 42);

SELECT * FROM test;


id
name
amount
ratio


2
a
3
1

3
a
8
2

4
a
3
31

5
a
2
10

6
a
4
15

7
b
2
42

8
b
1
42



SELECT 7


SELECT DISTINCT ON (name)
    id, 
    name, 
    amount,
    SUM(ratio) OVER (PARTITION BY name)
FROM test
ORDER BY name, amount DESC


id
name
amount
sum


3
a
8
59

7
b
2
84



SELECT 2


SELECT 
    id, 
    name, 
    max_amount, 
    sum_ratio 
FROM (
    SELECT 
        t.*,
        MAX(amount) OVER w as max_amount,
        SUM(ratio) OVER w as sum_ratio
    FROM test t
    WINDOW w as (PARTITION BY name)
    ORDER BY name
) s 
WHERE amount = max_amount


id
name
max_amount
sum_ratio


3
a
8
59

7
b
2
84



SELECT 2