Re: [PERFORM] A Better External Sort?

[David Fetter <david(at)fetter(dot)org>]

On Thu, Sep 29, 2005 at 10:06:52AM -0700, Luke Lonergan wrote:
> Josh,
> 
> On 9/29/05 9:54 AM, "Josh Berkus" <josh(at)agliodbs(dot)com> wrote:
> 
> > Following an index creation, we see that 95% of the time required
> > is the external sort, which averages 2mb/s.  This is with seperate
> > drives for the WAL, the pg_tmp, the table and the index.  I've
> > confirmed that increasing work_mem beyond a small minimum (around
> > 128mb) had no benefit on the overall index creation speed.
> 
> Yuuuup!  That about sums it up - regardless of taking 1 or 2 passes
> through the heap being sorted, 1.5 - 2 MB/s is the wrong number.
> This is not necessarily an algorithmic problem, but is a
> optimization problem with Postgres that must be fixed before it can
> be competitive.
> 
> We read/write to/from disk at 240MB/s and so 2 passes would run at a
> net rate of 120MB/s through the sort set if it were that efficient.
> 
> Anyone interested in tackling the real performance issue? (flame
> bait, but for a worthy cause :-)

I'm not sure that it's flamebait, but what do I know?  Apart from the
nasty number (1.5-2 MB/s), what other observations do you have to
hand?  Any ideas about what things are not performing here?  Parts of
the code that could bear extra scrutiny?  Ideas on how to fix same in
a cross-platform way?

Cheers,
D
-- 
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!