Message-ID: <1b572fb7e416cc09ec31ea1a16d0442f.squirrel@sq.gransy.com>
In-Reply-To: <4D9B6CBA.2050609@nasa.gov>
References: <4D9B6CBA.2050609@nasa.gov>
Date: Wed, 6 Apr 2011 15:16:05 +0200
Subject: Re: help speeding up a query in postgres 8.4.5
From: tv@fuzzy.cz
To: Maria.L.Wilson-1@nasa.gov
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

> 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