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