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 T(amount INT, index INT);
INSERT INTO T VALUES
(55, 1),(88, 5),(45, 6),(86, 7),(87, 10),
(88, 11),(57, 16),(58, 17),(59, 20),(60, 21);
10 rows affected

SELECT generate_series(0, 14, 4) Serie;


serie


0

4

8

12


CREATE OR REPLACE FUNCTION GroupIntervals(low int, high int, step int)
RETURNS TABLE (amount int, interv int) AS
$$
BEGIN
    RETURN QUERY
    WITH CTS AS
    (
        SELECT generate_series(low, high, step) Serie
    )
    SELECT    COALESCE(SUM(T.amount),0)::int AS amount, CTS.Serie as interv
    FROM      CTS
    LEFT JOIN T
    ON        index >= CTS.Serie 
    AND       index <  CTS.Serie + step
    GROUP BY  CTS.Serie
    ORDER BY  CTS.Serie;
END;    
$$ LANGUAGE plpgsql;
SELECT * FROM GroupIntervals(0, 14, 4);


amount
interv


55
0

219
4

175
8

0
12


SELECT * FROM GroupIntervals(5, 14, 5);


amount
interv


219
5

175
10


WITH CTS AS
(
    SELECT generate_series(0, 14, 4) Serie
)
SELECT    COALESCE(SUM(amount),0) AS amount, CTS.Serie
FROM      CTS
LEFT JOIN T
ON        index >= CTS.Serie 
AND       index <  CTS.Serie + 4
GROUP BY  CTS.Serie
ORDER BY  CTS.Serie;


amount
serie


55
0

219
4

175
8

0
12