Re: AGG_PLAIN thinks sorts are free

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.

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?). So it's something that hasn't gotten to the top of
anybody's to-do list.

An arguably more useful thing to do would be to integrate this behavior
into the planner more completely, so that (for instance) if only one
aggregate had ORDER BY then we would make the underlying query produce
that order instead of implementing a sort locally in the Agg node.
That hasn't risen to the top of the to-do list either, as yet.

regards, tom lane