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?.
-- 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 $$;

CREATE FUNCTION


-- 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.25 rows=100000 width=16) (actual rows=100000 loops=1)

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

Planning time: 0.519 ms

Execution time: 693.124 ms



EXPLAIN


-- 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.25 rows=100000 width=28) (actual rows=100000 loops=1)

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

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

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

Planning time: 0.096 ms

Execution time: 158.436 ms



EXPLAIN


-- 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.028 ms

Execution time: 225.195 ms



EXPLAIN


-- 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..9518.25 rows=100000 width=16) (actual rows=100000 loops=1)

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

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

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

Planning time: 0.094 ms

Execution time: 171.326 ms



EXPLAIN


-- 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;

CREATE 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.027 ms

Execution time: 170.729 ms



EXPLAIN


CREATE INDEX idx_foo_covering ON foo(groupid, pkid, numvalue); -- faster, yet, with covering index

CREATE INDEX


VACUUM foo;

VACUUM


-- 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.115 ms

Execution time: 301.848 ms



EXPLAIN


-- 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.027 ms

Execution time: 184.935 ms



EXPLAIN


-- 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.026 ms

Execution time: 130.352 ms



EXPLAIN


-- 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.103 ms

Execution time: 111.176 ms



EXPLAIN


-- 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..7802.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.101 ms

Execution time: 104.355 ms



EXPLAIN