Thread: Logical Aggregate Functions (eg ANY())
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?
On Thu, Dec 15, 2011 at 10:10 AM, Robert James <srobertjames@gmail.com> wrote: > 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
On 12/15/2011 11:10 AM, Robert James wrote: > 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
On Thu, Dec 15, 2011 at 18:10, Robert James <srobertjames@gmail.com> wrote: > 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
On 12/15/11, Marti Raudsepp <marti@juffo.org> wrote: > On Thu, Dec 15, 2011 at 18:10, Robert James <srobertjames@gmail.com> wrote: >> 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)
On Sat, Dec 17, 2011 at 6:06 PM, Robert James <srobertjames@gmail.com> wrote: > On 12/15/11, Marti Raudsepp <marti@juffo.org> wrote: >> On Thu, Dec 15, 2011 at 18:10, Robert James <srobertjames@gmail.com> wrote: >>> 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
On Mon, Dec 19, 2011 at 06:32, Merlin Moncure <mmoncure@gmail.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
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