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.
-M@
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.
http://archives.postgresql.org/pgsql-performance/2002-10/msg00101.php http://gborg.postgresql.org/project/pgautotune/projdisplay.php
-- 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
