From: Scott Marlowe <scott.marlowe@gmail.com>
To: Andrus <kobruleht2@hot.ee>
Cc: Scott Carey <scott@richrelevance.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: Fri, 28 Nov 2008 11:24:35 -0700
Message-ID: <dcc563d10811281024l4cc3a063r4f17ba8337b1ee36@mail.gmail.com> (raw)
In-Reply-To: <3E5CE5640FFD4172956D223988D8D578@andrusnotebook>
References: <ggp1au$1iq2$1@news.hub.org>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9C9@EXVMBX018-1.exch018.msoutlookonline.net>
<3E5CE5640FFD4172956D223988D8D578@andrusnotebook>
On Fri, Nov 28, 2008 at 10:58 AM, Andrus <kobruleht2@hot.ee> wrote:
>
> SET work_mem = 2097151 (this is max allowed value) or SET work_mem = 97151
> decreases query time from 12 seconds to 9 seconds.
Setting work_mem so high that it allocates memory that isn't there
WILL slow down your queries, because the OS will then wind up swapping
out one part of the swap to make room for another part. There are
values between 100M and 2G. Run it with increasing work_mem from 100
to 500 or so Meg and see if that helps. Keep an eye on vmstat 1 or
something to see if your machine starts swapping out while running the
query. If it does you've overallocated memory and things will start
to slow down a LOT.
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: scott.marlowe@gmail.com, kobruleht2@hot.ee, scott@richrelevance.com
Subject: Re: Increasing GROUP BY CHAR columns speed
In-Reply-To: <dcc563d10811281024l4cc3a063r4f17ba8337b1ee36@mail.gmail.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