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

On Jan19, 2014, at 05:27 , David Rowley <dgrowleyml(at)gmail(dot)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