Message-ID: <4DBB1F29.6010407@mansionfamily.plus.com>
Date: Fri, 29 Apr 2011 21:27:21 +0100
From: James Mansion <james@mansionfamily.plus.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Robert Haas <robertmhaas@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>,
	Tomas Vondra <tv@fuzzy.cz>, pgsql-performance@postgresql.org
Subject: Re: Performance
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
	<20110412171855.GA14292@tux>
	<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
	<4DA496FA.3070908@fuzzy.cz>
	<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
	<4DA4BFA2.5060601@fuzzy.cz>
	<B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
	<4DA4D40A.4010200@fuzzy.cz>
	<A0B9339E-9D58-4842-A206-050B773360B6@darkstatic.com>
	<4DA56DA8020000250003C783@gw.wicourts.gov>
	<BANLkTinJqXqErAtQg--1MOaw2LJNFZmwOg@mail.gmail.com>
	<BANLkTimyWkoX8Dj=4CKAjhY82ibru-An7g@mail.gmail.com>
	<4DA6216E.9020907@fuzzy.cz>
	<BANLkTim_m-oz3UMXEKUdFjWJ6uuAza79cw@mail.gmail.com>
	<4DA63125.5070106@fuzzy.cz>
	<BANLkTikYtnzTfS8Yjc-3ap9kysXLLpJwmg@mail.gmail.com>
	<D2F0DB51-7D42-4573-99CA-B72E28440B16@gmail.com>
	<4DBA75DC.6070506@mansionfamily.plus.com>
	<4DBB09B5.80108@2ndquadrant.com>
In-Reply-To: <4DBB09B5.80108@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Greg Smith wrote:
> There are also some severe query plan stability issues with this idea 
> beyond this.  The idea that your plan might vary based on execution 
> latency, that the system load going up can make query plans alter with 
> it, is terrifying for a production server.
>
I thought I was clear that it should present some stats to the DBA, not 
that it would try to auto-tune?  This thread started with a discussion 
of appropriate tunings for random page cost vs sequential page cost I 
believe,, based on some finger in the air based on total size vs 
available disk cache.  And it was observed that on systems that have 
very large databases but modest hot data, you can perform like a fully 
cached system, for much of the time.

I'm just suggesting providing statistical information to the DBA which 
will indicate whether the system has 'recently' been behaving like a 
system that runs from buffer cache and/or subsystem caches, or one that 
runs from disk platters, and what the actual observed latency difference 
is.  It may well be that this varies with time of day or day of week.  
Whether the actual latencies translate directly into the relative costs 
is another matter.