Re: Performance weirdness with/without vacuum analyze

Shridhar Daithankar writes:

First - many thanks for your suggestions and pointers to further info.

I have been trying some of them with some interesting results!

> Harry Broomhall wrote:
> > #effective_cache_size = 1000 # typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> You must tune the first one at least. Try
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these
> parameters.

Changing effective_cache_size seemed to have very little effect. I took it
in steps up to 300MB (the machine has 640MB memory), and the differences
in speed were less than 10%.

[SNIP]
>
> What happens if you turn off hash joins?

This makes the non vacuum version about 40% slower, and the vacuum version
to the same speed (i.e. about 4X faster than it had been!).

> Also bump sort memory to something
> good.. around 16MB and see what difference does it make to performance..

This was interesting. Taking it to 10MB made a slight improvement. Up to
20MB and the vacuum case improved by 5X speed, but the non-vacuum version
slowed down. Putting it up to 40MB slowed both down again.

I will need to test with some of the other scripts and functions I have
written, but it looks as if selective use of more sort memory will be
useful.

Regards,
Harry.