Date: Tue, 18 Apr 2006 16:52:38 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: merge>hash>loop
Message-ID: <20060418215238.GK49405@pervasive.com>
References: <1145029009.3422.31.camel@spectre.intellivid.com>
	<10000.1145031205@sss.pgh.pa.us>
	<1145033149.3422.41.camel@spectre.intellivid.com>
	<11035.1145033929@sss.pgh.pa.us> <4444C4CF.2000106@logix-tt.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4444C4CF.2000106@logix-tt.com>
User-Agent: Mutt/1.5.11

On Tue, Apr 18, 2006 at 12:51:59PM +0200, Markus Schaber wrote:
> > In my mind this is tied into another issue, which is that the planner
> > always costs on the basis of each query starting from zero.  In a real
> > environment it's much cheaper to use heavily-used indexes than this cost
> > model suggests, because they'll already be swapped in due to use by
> > previous queries.  But we haven't got any infrastructure to keep track
> > of what's been heavily used, let alone a cost model that could make use
> > of the info.
> 
> An easy first approach would be to add a user tunable cache probability
> value to each index (and possibly table) between 0 and 1. Then simply
> multiply random_page_cost with (1-that value) for each scan.
> 
> Later, this value could be automatically tuned by stats analysis or
> other means.

Actually, if you run with stats_block_level turned on you have a
first-order approximation of what is and isn't cached. Perhaps the
planner could make use of this information if it's available.

> > I think part of the reason that people commonly reduce random_page_cost
> > to values much lower than physical reality would suggest is that it
> > provides a crude way of partially compensating for this basic problem.
> 
> I totall agree with this, it's just what we did here from time to time. :-)
> 
> Hmm, how does effective_cach_size correspond with it? Shouldn't a high
> effective_cache_size have a similar effect?

Generally, effective_cache_size is used to determine the likelyhood that
something will be in-cache. random_page_cost tells us how expensive it
will be to get that information if it isn't in cache.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461