Re: Logical Aggregate Functions (eg ANY())

On Mon, Dec 19, 2011 at 06:32, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> that would require
> that the planner have very special understanding of the internal
> workings of aggregate functions.  There are a couple of cases where
> the planner *does* have that function, for example it can convert
> max(v) to 'order by v desc limit 1'

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.

Regards,
Marti