
> [with the new effective_cache_size = 6400]

This seems way too low for a 512 Mb machine. Why does your OS
only use so little for filecache ? Is the rest used for processes ?
For the above number you need to consider OS cache and shared_buffers.
You can approximatly add them together minus a few %.

With the data you gave, a calculated value for effective_cache_size
would be 29370, assuming the random_page_cost is actually 4 on your
machine. 29370 might be a slight overestimate, since your new table
will probably still be somewhat sorted by date within one IP.

Try to measure IO/s during the seq scan and during the index path
and calculate the ratio. This should be done during an average workload
on the machine.

Andreas

Thu, 10 Jan 2002 15:46:54 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: again on index usage 
>>>"Zeugswetter Andreas SB SD" said:
 > 
 > > [with the new effective_cache_size = 6400]
 > 
 > This seems way too low for a 512 Mb machine. Why does your OS
 > only use so little for filecache ? Is the rest used for processes ?
 > For the above number you need to consider OS cache and shared_buffers.
 > You can approximatly add them together minus a few %.

As far as I am aware, 10% for buffer space is the default for BSD operating 
systems... although I have seen buffer space = 50% on MacOS X. There is no 
problem to increase the buffer space in kernel, although I am not very 
confident this will give much better overall performance (well, more memory 
can be added as well).

 > With the data you gave, a calculated value for effective_cache_size
 > would be 29370, assuming the random_page_cost is actually 4 on your
 > machine. 29370 might be a slight overestimate, since your new table
 > will probably still be somewhat sorted by date within one IP.

random_page_cost is 4.

If the select into then cluster do this, then yes, it is possible, but not 
guaranteed.

I will try with increased effective_cache_size.

Postmaster is started with -N 128 -B 256 -i -o "-e -S 10240" 

Daniel


Thu, 10 Jan 2002 18:09:15 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 