Date: Fri, 24 Dec 1999 14:04:40 +1300 (NZDT)
From: Mark Kirkwood <markir@hnz.co.nz>
Message-Id: <199912240104.OAA26418@hudev0.hnz.co.nz>
subject: Sorting Problem
To: undisclosed-recipients:;

Dear List,

I gave been investigating how postgresql does sorting, using the query from my
last posting :

  select d0.d0f1,
         count(f.f1)
  from dim0 d0,
       fact0 f
  where d0.d0key = f.d0key
  and   d0.d0f1 between '1996-05-01' and '1996-05-31'
  group by d0.d0f1 ;

There is about 5-7M of data to sort - all with the same sort key ( d0f1 ).

I noticed that 19000K of temporary sort tables were written during query 
execution, so I tried using backend options ( -o ) of : -S 25000.
Whilst there was no sort tables written with this option, the elapsed time for
the query was longer ! ( 35s instead of 14s )

The platform is redhat 6.0 and pgsql 6.5.3 
There is 160M ram with about 60M free during the query exec
( note that hpux 11 with pgsql 6.5.3 gives similar results too...)

Is there something I need to set or unset, or is this expected ?

Cheers

Mark