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
| id | value | expected_output | comment |
|---|---|---|---|
| 1 | 0.89 | 0.89 | |
| 2 | 0.17 | 0.17 | |
| 3 | -0.30 | 0 | accum = -0.30 |
| 4 | -0.36 | 0 | accum = -0.30 - 0.36 = -0.66 |
| 5 | 1.41 | 0.75 | 0.75 = 1.41 - 0.66; accum = 0 |
| 6 | -0.49 | 0 | accum = -0.49 |
| 7 | 0.24 | 0 | 0, because 0.24 - 0.49 = -0.25 is still negative, so accum = -0.25 |
| 8 | 6.15 | 5.9 | 5.9 = 6.15 - 0.25; accum = 0 |
| 9 | 6.05 | 6.05 | |
| 10 | -1.00 | 0 | |
| 11 | 0.30 | 0 | |
| 12 | 0.40 | 0 | |
| 13 | -1.00 | 0 | |
| 14 | 2.00 | 0.7 |
INSERT 0 14
CREATE FUNCTION
CREATE FUNCTION
CREATE AGGREGATE
| id | value | expected_output |
|---|---|---|
| 1 | 0.89 | 0.89 |
| 2 | 0.17 | 0.17 |
| 3 | -0.30 | 0 |
| 4 | -0.36 | 0 |
| 5 | 1.41 | 0.75 |
| 6 | -0.49 | 0 |
| 7 | 0.24 | 0 |
| 8 | 6.15 | 5.90 |
| 9 | 6.05 | 6.05 |
| 10 | -1.00 | 0 |
| 11 | 0.30 | 0 |
| 12 | 0.40 | 0 |
| 13 | -1.00 | 0 |
| 14 | 2.00 | 0.70 |
SELECT 14
CREATE AGGREGATE
| id | value | expected_output |
|---|---|---|
| 1 | 0.89 | 0.89 |
| 2 | 0.17 | 0.17 |
| 3 | -0.30 | 0 |
| 4 | -0.36 | 0 |
| 5 | 1.41 | 0.75 |
| 6 | -0.49 | 0 |
| 7 | 0.24 | 0 |
| 8 | 6.15 | 5.90 |
| 9 | 6.05 | 6.05 |
| 10 | -1.00 | 0 |
| 11 | 0.30 | 0 |
| 12 | 0.40 | 0 |
| 13 | -1.00 | 0 |
| 14 | 2.00 | 0.70 |
SELECT 14