Message-ID: <4D9C6C7A.1060107@nasa.gov>
Date: Wed, 6 Apr 2011 09:36:58 -0400
From: "Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>
Reply-To: <Maria.L.Wilson-1@nasa.gov>
Organization: SSAI
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
To: "tv@fuzzy.cz" <tv@fuzzy.cz>
CC: "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC]"
	<m.l.wilson@nasa.gov>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
References: <4D9B6CBA.2050609@nasa.gov>
	<1b572fb7e416cc09ec31ea1a16d0442f.squirrel@sq.gransy.com>
In-Reply-To: <1b572fb7e416cc09ec31ea1a16d0442f.squirrel@sq.gransy.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit

thanks for the reply, Tomas.  I'll test bumping up work_mem and see how 
that helps.....

thanks again,  Maria Wilson

On 4/6/11 9:16 AM, tv@fuzzy.cz wrote:
>> some additional info.....
>> the table inventory is about 4481 MB and also has postgis types.
>> the table gran_ver is about 523 MB
>> the table INVSENSOR is about 217 MB
>>
>> the server itself has 32G RAM with the following set in the postgres conf
>> shared_buffers = 3GB
>> work_mem = 64MB
>> maintenance_work_mem = 512MB
>> wal_buffers = 6MB
> Not sure how to improve the query itself - it's rather simple and the
> execution plan seems reasonable. You're dealing with a lot of data, so it
> takes time to process.
>
> Anyway, I'd try to bump up the shared buffers a bit (the tables you've
> listed have about 5.5 GB, so 3GB of shared buffers won't cover it). OTOH
> most of the data will be in pagecache maintained by the kernel anyway.
>
> Try to increase the work_mem a bit, that might speed up the hash joins
> (the two hash joins consumed about 15s, the whole query took 17s). This
> does not require a restart, just do
>
> set work_mem = '128MB'
>
> (or 256MB) and then run the query in the same session. Let's see if that
> works.
>
> regards
> Tomas
>