Logical Aggregate Functions (eg ANY())

[Robert James]

I see Postgres (I'm using 8.3) has bitwise aggregate functions
(bit_or), but doesn't seem to have logical aggregate functions.

How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?

[Merlin Moncure]

Post by Robert James
I see Postgres (I'm using 8.3) has bitwise aggregate functions
(bit_or), but doesn't seem to have logical aggregate functions.
How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?

CREATE OR REPLACE FUNCTION OrAgg(bool, bool) RETURNS BOOL AS
$$
SELECT COALESCE($1 or $2, false);
$$ LANGUAGE SQL IMMUTABLE;

create aggregate "any"(bool)
(
sfunc=OrAgg,
stype=bool
);

postgres=# select "any"(v) from (values (false), (true)) q(v);
any
-----
t
(1 row)

etc

note:, I don't like the use of double quoted "any" -- but I'm too lazy
to come up with a better name. :-)

merlin

[Kirill Simonov]

Post by Robert James
I see Postgres (I'm using 8.3) has bitwise aggregate functions
(bit_or), but doesn't seem to have logical aggregate functions.

They are called BOOL_AND and BOOL_OR, see
http://www.postgresql.org/docs/8.3/interactive/functions-aggregate.html


Thanks,
Kirill

[Marti Raudsepp]

Post by Robert James
How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?

Note that in many cases, writing an EXISTS(SELECT ...) or NOT
EXISTS(...) subquery is faster, since the planner can often optimize
those to a single index access -- whereas an aggregate function would
necessarily need to walk through and evaluate all potential rows.

Regards,
Marti

[Robert James]

Post by Marti Raudsepp

Post by Robert James
How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?

Note that in many cases, writing an EXISTS(SELECT ...) or NOT
EXISTS(...) subquery is faster, since the planner can often optimize
those to a single index access -- whereas an aggregate function would
necessarily need to walk through and evaluate all potential rows.

Really? The planner can't tell that, for instance, BOOL_AND (false, *)
is automatically false?

(BTW Thanks for the great responses everyone! On this and other posts
on this list)

[Merlin Moncure]

Post by Robert James

Post by Marti Raudsepp

Post by Robert James
How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?

Note that in many cases, writing an EXISTS(SELECT ...) or NOT
EXISTS(...) subquery is faster, since the planner can often optimize
those to a single index access -- whereas an aggregate function would
necessarily need to walk through and evaluate all potential rows.

Really? The planner can't tell that, for instance, BOOL_AND (false, *)
is automatically false?

No (by the way, I really should have known about the bool_x aggregate
functions before suggesting a hand rolled one!), 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' to bag the index, but that's the
exception rather than the rule.

Most queries that can be equivalently expressed in aggregate and
non-aggregate form are faster without aggregates. However,
aggregation can be a cleaner expression of the problem which is
important as well (performance isn't everything!).

merlin

[Marti Raudsepp]

Post by Merlin Moncure
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

[Merlin Moncure]

Post by Marti Raudsepp
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