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

Re: Hash join on int takes 8..114 seconds

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "Tomas Vondra" <tv(at)fuzzy(dot)cz>
Cc: "Scott Carey" <scott(at)richrelevance(dot)com>,"PFC" <lists(at)peufeu(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Hash join on int takes 8..114 seconds
Date: 2008-11-24 12:35:25
Message-ID: 7395E52430A9420E8248D54726DC634B@andrusnotebook (view raw or flat)
Thread:
Lists: pgsql-performance
Tomas,

> Let's suppose you set a reasonable value (say 8096) instead of 2GB. That 
> gives about 160MB.
> Anyway this depends - if you have a lot of slow queries caused by on-disk 
> sorts / hash tables, use a higher value. Otherwise leave it as it is.

Probably product orders table is frequently joined which product table.
currently there was work_memory = 512 in conf file.

I changed it to work_memory = 8096

>>> If it is all cached in memory, you may want to ensure that your
>>> shared_buffers is a reasonalbe size so that there is less shuffling of 
>>> data
>>> from the kernel to postgres and back.  Generally, shared_buffers works 
>>> best
>>> between 5% and 25% of system memory.
>>
>> currently shared_buffers = 15000
>
> That's 120MB, i.e. about 6% of the memory. Might be a little bit higher, 
> but seems reasonable.

I changed it to 20000

> Given the fact that the performance issues are caused by bloated tables 
> and / or slow I/O subsystem, moving to a similar system won't help I 
> guess.

I have ran VACUUM FULL ANALYZE VERBOSE
and set MAX_FSM_PAGES = 150000

So there is no any bloat except pg_shdepend indexes which should not affect 
to query speed.

Andrus. 


In response to

Responses

pgsql-performance by date

Next:From: Steve ClarkDate: 2008-11-24 13:23:54
Subject: Re: Perc 3 DC
Previous:From: Richard HuxtonDate: 2008-11-24 09:36:17
Subject: Re: Increasing pattern index query speed

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