Re: help speeding up a query in postgres 8.4.5

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(at)fuzzy(dot)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
>