On Sat, Dec 17, 2011 at 6:06 PM, Robert James <srobertjames@xxxxxxxxx> wrote: > On 12/15/11, Marti Raudsepp <marti@xxxxxxxxx> wrote: >> On Thu, Dec 15, 2011 at 18:10, Robert James <srobertjames@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
