Re: Logical Aggregate Functions (eg ANY())

On Mon, Dec 19, 2011 at 3:42 AM, Marti Raudsepp <marti@juffo.org> wrote:
> In fact, there's no reason why bool_or/bool_and couldn't do the same
> thing. bool_or() is like the max() for boolean values, and bool_and()
> is min().
>
> CREATE AGGREGATE my_bool_or(bool) (sfunc=boolor_statefunc, stype=bool,
> sortop= >);
> CREATE AGGREGATE my_bool_and(bool) (sfunc=booland_statefunc,
> stype=bool, sortop= <);
>
> db=# explain analyze select bool_and(b) from bools;
>  Aggregate  (cost=1693.01..1693.02 rows=1 width=1)
>   ->  Seq Scan on bools  (cost=0.00..1443.01 rows=100001 width=1)
>  Total runtime: 29.736 ms
>
> db=# explain analyze select my_bool_and(b) from bools;
>  Result  (cost=0.03..0.04 rows=1 width=0)
>   InitPlan 1 (returns $0)
>     ->  Limit  (cost=0.00..0.03 rows=1 width=1)
>           ->  Index Scan using bools_b_idx on bools
> (cost=0.00..3300.28 rows=100001 width=1)
>                 Index Cond: (b IS NOT NULL)
>  Total runtime: 0.109 ms
>
> Now obviously this still has limitations -- it doesn't do index
> accesses in a GROUP BY query -- but it's a fairly simple modification.

That's really clever...bravo.

merlin