Re: [PATCH] Negative Transition Aggregate Functions (WIP) - Mailing list pgsql-hackers

On Jan19, 2014, at 05:27 , David Rowley <dgrowleyml@gmail.com> wrote:
>> I just finished implementing the inverse transition functions for bool_and
>> and bool_or, these aggregates had a sort operator which I assume would have
>> allowed an index scan to be performed, but since I had to change the first
>> argument of these aggregates to internal and that meant I had to get rid of
>> the sort operator...

Why does having transition type "internal" prevent you from specifying a
sort operator? The sort operator's argument types must match the *input*
type of the aggregate, not the transition type.

Here's a pure SQL implementation of an optimized bool_and called myand_agg
that uses state type bigint[] and specifies a sort operator.

  create or replace function myboolagg_fwd(counts bigint[], value bool)
  returns bigint[] as $$
    select array[
      counts[1] + case value when true then 0 else 1 end,
      counts[2] + case value when true then 1 else 0 end
    ]
  $$ language sql strict immutable;

  create or replace function myboolagg_inv(counts bigint[], value bool)
  returns bigint[] as $$
    select array[
      counts[1] - case value when true then 0 else 1 end,
      counts[2] - case value when true then 1 else 0 end
    ]
  $$ language sql strict immutable;

  create or replace function myboolagg_and(counts bigint[])
  returns bool as $$
    select case counts[1] when 0 then true else false end
  $$ language sql strict immutable;

  create aggregate myand_agg (bool) (
    stype = bigint[],
    sfunc = myboolagg_fwd,
    invfunc = myboolagg_inv,
    finalfunc = myboolagg_and,
    sortop = <,
    initcond = '{0,0}'
  );

With this, doing

  create table boolvals as
    select i, random() < 0.5 as v from generate_series(1,10000) i;
  create index on boolvals(v);

  explain analyze select myand_agg(v) from boolvals;

yields

 Result  (cost=0.33..0.34 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.29..0.33 rows=1 width=1) (actual time=0.061..0.061 rows=1 loops=1)
           ->  Index Only Scan using boolvals_v_idx on boolvals  (cost=0.29..474.41 rows=9950 width=1) (actual time=0.061..0.061 rows=1 loops=1)
                 Index Cond: (v IS NOT NULL)
                 Heap Fetches: 1
 Total runtime: 0.100 ms

which looks fine, no?

best regards,
Florian Pflug