Consider the explain for the following queries ..
sample=# explain select a, count(*) from foo group by a order by a; QUERY PLAN
-------------------------------------------------------------------------Aggregate (cost=69.83..77.33 rows=100
width=4) -> Group (cost=69.83..74.83 rows=1000 width=4) -> Sort (cost=69.83..72.33 rows=1000 width=4)
Sort Key: a -> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4)
(5 rows)
sample=# explain select a, count(*) from foo group by a order by a desc; QUERY PLAN
-------------------------------------------------------------------------------Sort (cost=80.65..80.90 rows=100
width=4) Sort Key: a -> Aggregate (cost=69.83..77.33 rows=100 width=4) -> Group (cost=69.83..74.83
rows=1000width=4) -> Sort (cost=69.83..72.33 rows=1000 width=4) Sort Key: a
-> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4)
(7 rows)
In the first case pgsql doesn't have a Sort on top because the Sort
below the Group produces the right "interesting order" (using the
System-R term). In the second case however, since the order-by clause
demands "desc" there is a Sort tagged on on top.
Now, instead of doing this, isn't it better to just have a similar
plan as in the first case, but just change the lower Sort to be
descending ? It doesn't affect the Group and the Agg in any way ..
--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh
