Postgres 10

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?.
-- test setup by Abelisto
CREATE TABLE foo AS
SELECT pkid
    , (random()* 100 - 50)::int AS numvalue
    , (random()* 1000)::int AS groupid
FROM   generate_series(1,100000) pkid;

CREATE INDEX idx_foo ON foo(groupid, pkid);

ANALYZE foo;

-- aggregate function by Evan
create function int_add_pos_or_zero(int, int)
returns int
as $$
  begin
    return greatest($1 + $2, 0);
  end;
$$  language plpgsql immutable;

create aggregate add_pos_or_zero(int) (
  sfunc = int_add_pos_or_zero,
  stype = int,
  initcond = 0
);

-- plpgsql function by Abeilsto
create function bar() returns table (data foo, strange_sum int) language plpgsql as $$
declare
  g int;
begin
  strange_sum := 0;
  g := -1;
  for data in (select * from foo order by groupid, pkid) loop
    if g <> data.groupid then
      g := data.groupid;
      strange_sum := 0;
    end if;
    strange_sum := greatest(strange_sum + data.numvalue, 0);
    return next;
  end loop;
end $$;
100000 rows affected

-- Evan's aggregate function
EXPLAIN (ANALYZE, TIMING OFF)  -- to see unburdened aboslute time
select pkid,
  groupid,
  numvalue,
  add_pos_or_zero(numvalue) over (partition by groupid order by pkid)
from foo;


QUERY PLAN


WindowAgg  (cost=0.29..6518.29 rows=100000 width=16) (actual rows=100000 loops=1)

  ->  Index Scan using idx_foo on foo  (cost=0.29..4768.29 rows=100000 width=12) (actual rows=100000 loops=1)

Planning time: 1.250 ms

Execution time: 359.243 ms


-- dnoeth's query with 2 window functions
EXPLAIN (ANALYZE, TIMING OFF)
select dt.*,
   -- find the lowest previous cumsum < 0       
   -- and adjust the current cumsum to zero
   max(case when cumsum < 0 then -cumsum else 0 end)
       over (partition by groupid
             order by pkid
             rows unbounded preceding)
   + cumsum as adjustedsum
from 
 ( 
   select pkid, numvalue, groupid,
      -- calculate a standard cumulative sum
      sum(numvalue)
      over (partition by groupid
            order by pkid
            rows unbounded preceding) as cumsum
   from foo
 ) as dt;


QUERY PLAN


WindowAgg  (cost=0.29..10018.29 rows=100000 width=28) (actual rows=100000 loops=1)

  ->  Subquery Scan on dt  (cost=0.29..7518.29 rows=100000 width=20) (actual rows=100000 loops=1)

        ->  WindowAgg  (cost=0.29..6518.29 rows=100000 width=20) (actual rows=100000 loops=1)

              ->  Index Scan using idx_foo on foo  (cost=0.29..4768.29 rows=100000 width=12) (actual rows=100000 loops=1)

Planning time: 0.192 ms

Execution time: 147.832 ms


-- Abelisto's plpgsql function
EXPLAIN (ANALYZE, TIMING OFF)
SELECT (data).*, strange_sum FROM bar();


QUERY PLAN


Function Scan on bar  (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1)

Planning time: 0.062 ms

Execution time: 171.275 ms


-- Erwin's query with 2 window functions
EXPLAIN (ANALYZE, TIMING OFF)
SELECT groupid, pkid
     , simple_sum
     - LEAST(MIN(simple_sum) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING), 0) AS rolling_sum
FROM ( 
   SELECT pkid, numvalue, groupid
        , SUM(numvalue) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING) AS simple_sum
   FROM   foo
   ) sub;


QUERY PLAN


WindowAgg  (cost=0.29..9768.29 rows=100000 width=16) (actual rows=100000 loops=1)

  ->  Subquery Scan on sub  (cost=0.29..7518.29 rows=100000 width=16) (actual rows=100000 loops=1)

        ->  WindowAgg  (cost=0.29..6518.29 rows=100000 width=20) (actual rows=100000 loops=1)

              ->  Index Scan using idx_foo on foo  (cost=0.29..4768.29 rows=100000 width=12) (actual rows=100000 loops=1)

Planning time: 0.116 ms

Execution time: 133.168 ms


-- Erwin's plpgsql function
CREATE OR REPLACE FUNCTION f_special_rolling_sum()
  RETURNS TABLE (groupid int, pkid int, rolling_sum int) AS
$func$
DECLARE
   numvalue int;
   last_groupid int;
BEGIN
   FOR groupid, pkid, numvalue IN 
      SELECT f.groupid, f.pkid, f.numvalue
      FROM   foo f
      ORDER  BY f.groupid, f.pkid
   LOOP
      IF last_groupid = groupid THEN  -- partition continues
         rolling_sum := GREATEST(rolling_sum + numvalue, 0);
      ELSE                            -- new partition (and first row)
         last_groupid := groupid;
         rolling_sum  := GREATEST(numvalue, 0);
      END IF;
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM f_special_rolling_sum();


QUERY PLAN


Function Scan on f_special_rolling_sum  (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1)

Planning time: 0.034 ms

Execution time: 140.498 ms


CREATE INDEX idx_foo_covering ON foo(groupid, pkid, numvalue); -- faster, yet, with covering index
VACUUM foo;
-- Evan's aggregate function
EXPLAIN (ANALYZE, TIMING OFF)  -- to see unburdened aboslute time
select pkid, groupid, add_pos_or_zero(numvalue) over (partition by groupid order by pkid)
from foo;


QUERY PLAN


WindowAgg  (cost=0.42..4802.42 rows=100000 width=12) (actual rows=100000 loops=1)

  ->  Index Only Scan using idx_foo_covering on foo  (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)

        Heap Fetches: 0

Planning time: 0.216 ms

Execution time: 200.740 ms


-- Abelisto's plpgsql function
EXPLAIN (ANALYZE, TIMING OFF)
SELECT (data).*, strange_sum FROM bar();


QUERY PLAN


Function Scan on bar  (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1)

Planning time: 0.037 ms

Execution time: 393.593 ms


-- Erwin's plpgsql function
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM f_special_rolling_sum();


QUERY PLAN


Function Scan on f_special_rolling_sum  (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1)

Planning time: 0.034 ms

Execution time: 105.216 ms


-- dnoeth's query
EXPLAIN (ANALYZE, TIMING OFF)
select dt.*,
   max(case when cumsum < 0 then -cumsum else 0 end)
       over (partition by groupid
             order by pkid
             rows unbounded preceding)
   + cumsum as adjustedsum
from 
 ( 
   select pkid, numvalue, groupid,
      sum(numvalue)
      over (partition by groupid
            order by pkid
            rows unbounded preceding) as cumsum
   from foo
 ) as dt;


QUERY PLAN


WindowAgg  (cost=0.42..8302.42 rows=100000 width=28) (actual rows=100000 loops=1)

  ->  Subquery Scan on dt  (cost=0.42..5802.42 rows=100000 width=20) (actual rows=100000 loops=1)

        ->  WindowAgg  (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1)

              ->  Index Only Scan using idx_foo_covering on foo  (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)

                    Heap Fetches: 0

Planning time: 0.128 ms

Execution time: 109.008 ms


-- Erwin's query
EXPLAIN (ANALYZE, TIMING OFF)
SELECT groupid, pkid
     , simple_sum
     - LEAST(MIN(simple_sum) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING), 0) AS rolling_sum
FROM ( 
   SELECT pkid, numvalue, groupid
        , SUM(numvalue) OVER (PARTITION BY groupid ORDER BY pkid ROWS UNBOUNDED PRECEDING) AS simple_sum
   FROM   foo
   ) sub;


QUERY PLAN


WindowAgg  (cost=0.42..8052.42 rows=100000 width=16) (actual rows=100000 loops=1)

  ->  Subquery Scan on sub  (cost=0.42..5802.42 rows=100000 width=16) (actual rows=100000 loops=1)

        ->  WindowAgg  (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1)

              ->  Index Only Scan using idx_foo_covering on foo  (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1)

                    Heap Fetches: 0

Planning time: 0.124 ms

Execution time: 87.903 ms