Postgres 14

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 tbl (id int, trx int, event float8, result_good float8);
INSERT INTO tbl VALUES 
  (1, 20, 0.1, 2.000000)
, (2,-10, 0.1, 1.200000)
, (3, 20,-0.1,-3.320000)
, (4,-10, 0.1, 1.988000)
, (5, 20, 0.1, 4.186800)
, (6,-10,-0.1,-3.605480)
, (7, 20, 0.1, 5.244932)
;
7 rows affected

CREATE OR REPLACE FUNCTION f_special_running_sum()
  RETURNS TABLE(id int, result text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   t record;
BEGIN
   DROP TABLE IF EXISTS pg_temp.result;
   CREATE TEMP TABLE result (id int, trx int, result float8) ON COMMIT DROP;

   FOR t IN 
      TABLE tbl ORDER BY id
   LOOP
      INSERT INTO result(id, trx, result)
      SELECT t.id, t.trx
           , (COALESCE(sum(r.trx + r.result), 0) + t.trx) * t.event
      FROM   result r;
   END LOOP;
   
   RETURN QUERY
   SELECT r.id, to_char(r.result, 'FM9999990.000000')
   FROM   result r;
END
$func$;
SELECT * FROM f_special_running_sum();


id
result


1
2.000000

2
1.200000

3
-3.320000

4
1.988000

5
4.186800

6
-3.605480

7
5.244932

id	result
1	2.000000
2	1.200000
3	-3.320000
4	1.988000
5	4.186800
6	-3.605480
7	5.244932