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

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.