Vitaly,
> I have a problem with certain queries performance. Trouble is that
> while their execution plan is pretty good and mostly their execution
> is great as well, their FIRST execution time (that is after you mount
> the database) is abysmal.
This is a well-known problem. The general approach to this is to run a
script to do select * queries against all important tables on system
start-up.
> I realize that it happens due to the loading of data from the HD to
> the memory/swap and it wouldn't be too bad if I just could make the
> data stay in the memory, sadly, after a few minutes the data is back
> on the HD and running the query again results the same bad
> performance.
This could be for a variety of reasons. On a standard platform (which yours
most definitely is not), this would be due to database vacuuming, commits of
large updates to your data, or another application using most of the system
memory.
> Before going on, I should say that I am running PostgreSQL on CoLinux
> under Windows 2000. From what I read/tested, the CoLinux performance
> on CoLinux are matching to the performance of VMWare. Yet, I'm still
> wondering if it is a side effect of my development setup or if some of
> my settings are indeed wrong.
Probably you will continue to get worse-than-normal performance from both.
You simply can't expect performance PostgreSQL running on an emulation
environment. If you could, we wouldn't have bothered with a Windows port.
Speaking of which, have you started testing the Windows port? I'd be
interested in your comparison of it against running on CoLinux.
> I can live up with the fact that the data has to be loaded the first
> time it is accessed, but is it possible to make it stick longer in the
> memory? Is it the fact that CoLinux gets only 128MB of RAM? Or one of
> my settings should be fixed?
Well, mostly it's that you should start testing 8.0, and the Windows port.
Not only should running native be better, but 8.0 (thanks to the work of Jan
Wieck) is now able to take advantage of a large chunk of dedicated memory,
which earlier versions were not. Also, "lazy vacuum" and the "background
writer", also features of 8.0 and Jan's work, should prevent PostgreSQL from
cleaning out its own cache completely. You should test this,
*particularly* on Windows where we could use some more performance testing.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco