Re: Volunteer to build a configuration tool

On Wed, 20 Jun 2007, Tom Lane wrote:

> I think what would be much more useful in the long run is some serious 
> study of the parameters themselves.  For instance, random_page_cost is a 
> self-admitted oversimplification of reality.

If I could figure out who would sponsor such a study that's what I'd be 
doing right now.  I have studies on many of the commit-related parameters 
I'll have ready in another few days, those are straightforward to map out. 
But you know what I have never found?  A good benchmark that demonstrates 
how well complicated queries perform to run studies on things like 
random_page_cost against.  Many of the tuning knobs on the query optimizer 
seem very opaque to me so far, and I'm not sure how to put together a 
proper test to illuminate their operation and map out their useful range.

Here's an example of one of the simplest questions in this area to 
demonstate things I wonder about.  Let's say I have a properly indexed 
database of some moderate size such that you're in big trouble if you do a 
sequential scan.  How can I tell if effective_cache_size is in the right 
ballpark so it will do what I want to effectively navigate that?  People 
back into a setting for that parameter right now based on memory in their 
system, but I never see anybody going "since your main table is X GB 
large, and its index is Y GB, you really need enough memory to set 
effective_cache_size to Z GB if you want queries/joins on that table to 
perform well".

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD