Re: Logical Aggregate Functions (eg ANY())

On Sat, Dec 17, 2011 at 6:06 PM, Robert James <srobertjames(at)gmail(dot)com> wrote:
> On 12/15/11, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>> On Thu, Dec 15, 2011 at 18:10, Robert James <srobertjames(at)gmail(dot)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