Subject: Re: Hardware/OS recommendations for large databases (
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: William Yu <wyu@talisys.com>, pgsql-performance@postgresql.org
In-Reply-To: <BFA0B0AE.13D4E%llonergan@greenplum.com>
References: <BFA0B0AE.13D4E%llonergan@greenplum.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <1132163674.3582.81.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 16 Nov 2005 11:54:34 -0600

On Wed, 2005-11-16 at 11:47, Luke Lonergan wrote:
> Scott,

Some cutting for clarity...  I agree on the OLTP versus OLAP
discussion. =20

> Here are the facts so far:
>       * Postgres can only use 1 CPU on each query
>       * Postgres I/O for sequential scan is CPU limited to 110-120
>         MB/s on the fastest modern CPUs
>       * Postgres disk-based sort speed is 1/10 or more slower than
>         commercial databases and memory doesn=E2=80=99t improve it (much)

But PostgreSQL only spills to disk if the data set won't fit into the
amount of memory allocated by working_mem / sort_mem.  And for most
Business analysis stuff, this can be quite large, and you can even crank
it up for a single query. =20

I've written reports that were horrifically slow, hitting the disk and
all, and I upped sort_mem to hundreds of megabytes until it fit into
memory, and suddenly, a slow query is running factors faster than
before.

Or did you mean something else by "disk base sort speed"???