Re: AGG_PLAIN thinks sorts are free

On Thu, Jul 18, 2013 at 8:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> AGG_PLAIN sometimes does sorts, but it thinks they are free. Also, under
>> explain analyze it does not explicitly report whether the sort was external
>> or not, nor report the disk or memory usage, the way other sorts do. I
>> don't know if those two things are related or not.
>
> DISTINCT (and also ORDER BY) properties of aggregates are implemented
> at runtime; the planner doesn't really do anything about them, except
> suppress the choice it might otherwise make of using hashed aggregation.
> Since the behavior is entirely local to the Agg plan node, it's also
> not visible to the EXPLAIN ANALYZE machinery.

Couldn't a hash aggregate be superior to a sort one (for the distinct,
not the order by)?

> Arguably we should have the planner add on some cost factor for such
> aggregates, but that would have no effect whatever on the current level
> of plan, and could only be useful if this was a subquery whose cost
> would affect choices in an outer query level. Which is a case that's
> pretty few and far between AFAIK (do you have a real-world example where
> it matters?).

Not that I know of. It is mainly an analytical headache. I'm trying
to figure out why the planner makes the choices it does on more
complex queries, but one of the component queries I'm trying to build
it up from suddenly falls into this plan, where I can't see the
estimated costs and can't use "set enable_*" to shift it away from
that into a more transparent one.

Thanks for the explanation.

Jeff