Andre Felipe Machado wrote:
>Postgresql uses around 30% cpu and hard disk heavily (not so as vacuum)
>at all executions.
>Firebird uses around 40% cpu and hard disk heavily at the first
>execution.
>The second execution uses around 60% cpu and **NO** disk activity.
>
>The previously cited query running at 26 miliseconds down from 10
>minutes, can achieve this performance at the second run, with **NO**
>disk activity.
>At the first run it uses 1,7 seconds, down from 10 minutes.
>
>The hard disk is clearly a bottleneck.
>1,7 seconds against 26 miliseconds.
>
>
>So,
>How "convince" postgresql to use windows disk cache or to read all
>indexes to ram?
>It seems that effective_cache_size does not tell postgresql to actually
>use windows disk cache.
>What parameter must be configured?
>Do you have some suggestions?
>
>
Assuming these are selects and that you have already vacuumed, etc.
Look at memory useage. It seems likely that you have a difference in
caching behavior. PostgreSQL has its own cache, and failing that will
use the OS disk cache. So there may be a number of possible issues
involved including whether the data is staying in the OS cache, how much
memory is being used for caching, etc. It is also likely that the
Windows version of PostgreSQL may have some issues in these areas that
the UNIX/Linux versions may not simply because it is more immature.
You might even try a vacuum full to retrieve space. This may mean
smaller tables, more likely to remain in disk cache, etc. But that
would not provide any indication of scalability.
Best Wishes,
Chris Travers
Metatron Technology Consulting