
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



12 Jul 2003 00:39:06 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
agg/order-by question
On Sat, Jul 12, 2003 at 00:39:06 -0700,
  Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> wrote:
> 
> 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 ..

You might try this in 7.4. I am pretty sure a change was made a couple
of weeks ago to let group by work with either sort order. Also hash
aggragates have been available for quite a while in 7.4. This is a better
plan when there are only a small number of distinct values.

Sat, 12 Jul 2003 08:42:52 -0500
Bruno Wolff III <bruno@wolff.to>
Re: agg/order-by question
>>>>> "Bruno" == Bruno Wolff, <Bruno> writes:

    Bruno> You might try this in 7.4. I am pretty sure a change was
    Bruno> made a couple of weeks ago to let group by work with either
    Bruno> sort order. Also hash aggragates have been available for
    Bruno> quite a while in 7.4. This is a better plan when there are
    Bruno> only a small number of distinct values.

Gotcha ! Thanks. 

TelegraphCQ is still on the 7.3.2 code base .. after doing one hellish
merge in March, I'm not too eager to do another, although merging more
often is likely going to be less painful.

I knew about the hash-aggregates - we had set spilling of
hash-aggregates to disk for large number of distinct values (with a
crude form of recursive partitioning) as a course project for our
undergraduate database class at Berkeley. When I get some time, I want
to clean up my solution code and contribute it as a patch. I don't
think that will be before the end of summer though.

BTW, some systems prefer sorted grouped-aggregates to hashed
grouped-aggregates - even for small distinct values. How it works is
to just update the running aggregates in place in the sort tournament
tree. The only requirement is to be able to compute aggregates of
aggregates, so that partial aggs for the same distinct values across
different runs can be merged. The advantage is that you get sorted
grouped aggregation for the same cost of unsorted hash-grouped
agg. The disadvantage is that you lose the modularity of the sort.


-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



12 Jul 2003 13:14:32 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: agg/order-by question