From: Josh Berkus <josh@agliodbs.com>
To: Jeffrey W. Baker <jwbaker@acm.org>
Cc: Ron Peacetree <rjpeace@earthlink.net>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
Date: Thu, 29 Sep 2005 09:54:05 -0700
Message-ID: <433C1C2D.7090908@agliodbs.com> (raw)
In-Reply-To: <1127968040.8954.12.camel@noodles>
References: <21402654.1127923414088.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
<1127968040.8954.12.camel@noodles>
Jeff, Ron,
First off, Jeff, please take it easy. We're discussing 8.2 features at
this point and there's no reason to get stressed out at Ron. You can
get plenty stressed out when 8.2 is near feature freeze. ;-)
Regarding use cases for better sorts:
The biggest single area where I see PostgreSQL external sort sucking is
on index creation on large tables. For example, for free version of
TPCH, it takes only 1.5 hours to load a 60GB Lineitem table on OSDL's
hardware, but over 3 hours to create each index on that table. This
means that over all our load into TPCH takes 4 times as long to create
the indexes as it did to bulk load the data.
Anyone restoring a large database from pg_dump is in the same situation.
Even worse, if you have to create a new index on a large table on a
production database in use, because the I/O from the index creation
swamps everything.
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.
--Josh Berkus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, jwbaker@acm.org, rjpeace@earthlink.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] A Better External Sort?
In-Reply-To: <433C1C2D.7090908@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox