Skip site navigation (1) Skip section navigation (2)

Re: Increasing GROUP BY CHAR columns speed

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: "Scott Carey" <scott(at)richrelevance(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Increasing GROUP BY CHAR columns speed
Date: 2008-11-28 18:24:35
Message-ID: dcc563d10811281024l4cc3a063r4f17ba8337b1ee36@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Nov 28, 2008 at 10:58 AM, Andrus <kobruleht2(at)hot(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2008-11-28 19:51:07
Subject: Re: Increasing GROUP BY CHAR columns speed
Previous:From: AndrusDate: 2008-11-28 17:58:59
Subject: Re: Increasing GROUP BY CHAR columns speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group