Postgres 13

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 users (id int, value int);
INSERT INTO users VALUES
  (1, 34)
, (2, 120)
, (3, 6)
, (4, 10)
, (5, 15)
, (6, 8)
, (7, 63)
;
7 rows affected

-- form two groups with equal number of members (or surplus of 1)
-- where the sum of values is as evenly split as possible
CREATE OR REPLACE FUNCTION f_fair_split()
  RETURNS TABLE (grp int, users int[], sum int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _id         int;
   _value      int;
   _users1     int[];
   _users2     int[];
   _sum1       int := 0;
   _sum2       int := 0;
   _ct1        int := 0; -- count of members in group 1
   _ct2        int := 0; -- count of members in group 2
   _ct_max     int;      -- current maximum member count
   _ct_limit   int := (SELECT (count(*)::int + 1) / 2 FROM users); -- member limit

BEGIN
   FOR _id, _value IN
      SELECT id, value
      FROM   users
      ORDER  BY value DESC, id
   LOOP
      -- once one group has maximum members, rest goes to other group
      IF _ct_max >= _ct_limit THEN
         IF _ct1 > _ct2 THEN
            _users2 := _users2 || _id;
            _sum2   := _sum2 + _value;
         ELSE
            _users1 := _users1 || _id;
            _sum1   := _sum1 + _value;
         END IF;
      -- else add to group with lower sum
      ELSE                 
         IF _sum1 > _sum2 THEN
            _users2 := _users2 || _id;
            _sum2   := _sum2 + _value;
            _ct2    := _ct2 + 1; 
         ELSE
            _users1 := _users1 || _id;
            _sum1   := _sum1 + _value;
            _ct1    := _ct1 + 1;
         END IF;

         _ct_max := GREATEST (_ct1, _ct2);  -- keep track of max member count
      END IF;
   END LOOP;
   
   RETURN QUERY VALUES (1, _users1, _sum1), (2, _users2, _sum2);
END
$func$;
SELECT * FROM f_fair_split();


grp
users
sum


1
{2,6,3}
134

2
{7,1,5,4}
122