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=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)
(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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
