From: Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
To: tv@fuzzy.cz <tv@fuzzy.cz>
Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC] <m.l.wilson@nasa.gov>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
Date: Wed, 6 Apr 2011 09:36:58 -0400
Message-ID: <4D9C6C7A.1060107@nasa.gov> (raw)
In-Reply-To: <1b572fb7e416cc09ec31ea1a16d0442f.squirrel@sq.gransy.com>
References: <4D9B6CBA.2050609@nasa.gov>
<1b572fb7e416cc09ec31ea1a16d0442f.squirrel@sq.gransy.com>
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
>
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: Maria.L.Wilson-1@nasa.gov, tv@fuzzy.cz, m.l.wilson@nasa.gov
Subject: Re: help speeding up a query in postgres 8.4.5
In-Reply-To: <4D9C6C7A.1060107@nasa.gov>
* 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