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

On Mon, Jan 20, 2014 at 5:53 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:

> 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?
>
>
hmm, yeah you're right. I guess I didn't quite think through what the sort
comparison was comparing with, for some reason I had it in my head that it
was the aggregate state and not another value in a btree index.

I've applied that patch again and put in the sort operators.

Thanks for looking at that.

Regards

David Rowley

> best regards,
> Florian Pflug
>
>