On Fri, Jul 19, 2013 at 8:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.janes@gmail.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.
Slight modification would be *all* aggregates having same ORDER BY clause. I think it would be easy given that we already influence the sortedness of underlying query result in planner.
That hasn't risen to the top of the to-do list either, as yet.