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(id,date,quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'2024-10-05',1,1)
,(6,'2024-10-06',1,1)
,(7,'2024-10-07',1,1)
;
table table1;
CREATE OR REPLACE FUNCTION functiontest()
RETURNS VOID LANGUAGE plpgsql AS $f$
DECLARE _r RECORD;
DECLARE _previous_value DOUBLE PRECISION := 0;
BEGIN
FOR _r IN
SELECT table1.date, table1.id
FROM table1
ORDER BY table1.date, table1.id
LOOP
WITH c AS
( SELECT
table1.id,
table1.quantity + _previous_value AS total
FROM table1
WHERE table1.id = _r.id
ORDER BY table1.date, table1.id
LIMIT 1
)
SELECT
COALESCE(c.total, 0)
INTO
_previous_value
FROM c;
SELECT 7
| id | date | quantity | value |
|---|---|---|---|
| 1 | 2024-10-01 | 1 | 1 |
| 2 | 2024-10-02 | 1 | 1 |
| 3 | 2024-10-03 | 1 | 1 |
| 4 | 2024-10-04 | 1 | 1 |
| 5 | 2024-10-05 | 1 | 1 |
| 6 | 2024-10-06 | 1 | 1 |
| 7 | 2024-10-07 | 1 | 1 |
SELECT 7
CREATE FUNCTION
begin;
explain analyze verbose select functiontest();
select*from table1 order by date,id limit 30;
rollback;
BEGIN
| QUERY PLAN |
|---|
| Result (cost=0.00..0.26 rows=1 width=4) (actual time=3.780..3.781 rows=1 loops=1) |
| Output: functiontest() |
| Planning Time: 0.017 ms |
| Execution Time: 3.884 ms |
EXPLAIN
| id | date | quantity | value |
|---|---|---|---|
| 1 | 2024-10-01 | 1 | 1 |
| 2 | 2024-10-02 | 1 | 2 |
| 3 | 2024-10-03 | 1 | 3 |
| 4 | 2024-10-04 | 1 | 4 |
| 5 | 2024-10-05 | 1 | 5 |
| 6 | 2024-10-06 | 1 | 6 |
| 7 | 2024-10-07 | 1 | 7 |
SELECT 7
ROLLBACK