Why couldn't Postgres learn for itself what the optimal performance settings would be? The easy one seems to be the effective_cache_size. Top shows us this information. Couldn't Postgres read that value from the same place top reads it instead of relying on a config file value? Seems like it could even adjust to changing conditions as the cache size changes.
Wouldn't it be great to set a single parameter in postgresql.conf like:

learn = on

This would make Postgres run the same queries multiple times with different settings, trying to find the ones that made the query run the fastest. Obviously you wouldn't want this on all the time because Postgres would be doing more work than it needs to satisfy the applications that are asking it for data. You'd leave it running like this for as long as you think it would need to get a sampling of real world use for your specific application.
Something like this could automagically adapt to load, hardware, schema, and operating system. If you drop another 1GB of RAM into the machine, just turn the learning option on and let Postgres tune itself again.

On Thursday, July 3, 2003, at 04:25 PM, Sean Chittenden wrote:

I'm curious how many of the configuration values can be determined
automatically, or with the help of some script.  It seem like there
could be some perl script in contrib that could help figure this out.
Possibly you are asked a bunch of questions and then the values are
computed based on that.   Something like:

How many tables will the system have?
How much memory will be available to the postmaster?
How many backends will there typically be?
What is the avg seek time of the drive?
What's the transfer rate of the drive?

Seems to me that a lot of reasonable default values can be figure out
from these basic questions.  FSM settings, Sort Mem, Random Page Cost,
Effective Cache Size, Shared Memor, etc, etc.

Someone was working on a thing called pg_autotune or some such program that'd do exactly what you're thinking of.


-- Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to