From: Jim C. Nasby <jnasby@pervasive.com>
To: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: merge>hash>loop
Date: Tue, 18 Apr 2006 16:52:38 -0500
Message-ID: <20060418215238.GK49405@pervasive.com> (raw)
In-Reply-To: <4444C4CF.2000106@logix-tt.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, schabi@logix-tt.com
Subject: Re: merge>hash>loop
In-Reply-To: <20060418215238.GK49405@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox