Re: New 9.6 external sort guidance around temp_tablespaces and maintenance_work_mem

On Thu, Apr 21, 2016 at 08:37:54PM -0700, Peter Geoghegan wrote:
> Documentation
> =============
> 
> I think we can expand "21.6. Tablespaces" to describe the implications
> of these new performance characteristics. I'd like to hear opinions on
> how to approach that before proposing a patch, though. The basic
> guidance should, IMV, be:
> 
> * A temp tablespace with cheaper disks that have good sequential I/O
> performance can speed up external sorts quite a lot. Probably not a
> great idea to have many temp tablespaces. Use RAID0 instead, because
> that performs better, and because it doesn't matter that temp files
> are not recoverable if a disk is faulty.
> 
> * More memory for sorting and hashing is often better in PostgreSQL
> 9.6. Notably, the performance of hash joins that spill will tend to
> degrade less predictably than the performance of sorts that spill as
> less memory is made available. (Perhaps mention the number of external
> sort passes?)
> 
> * Increasing work_mem/maintenance_work_mem may fail to improve
> performance only because sorts then become more I/O bound. When in
> doubt, testing is advised. A balance may need to be found, if only to
> avoid wasting memory.

This seems very detailed.  I think we need much broader coverage of how
the existing GUC variables affect performance before we could cover
this.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +