Re: [PATCH] Negative Transition Aggregate Functions (WIP)

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   ] $$
languagesql 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   ] $$
languagesql strict immutable; 
 create or replace function myboolagg_and(counts bigint[]) returns bool as $$   select case counts[1] when 0 then true
elsefalse 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_idxon 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: 1Total runtime: 0.100 ms 

which looks fine, no?

best regards,
Florian Pflug