2011/5/15 Josh Berkus <josh(at)agliodbs(dot)com>:
>> I think random_page_cost causes problems because I need to combine
>> disk random access time, which I can measure, with a guesstimate of
>> the disk cache hit rate.
> See, that's wrong. Disk cache hit rate is what effective_cache_size
> (ECS) is for.
> Really, there's several factors which should be going into the planner's
> estimates to determine a probability of a table being cached:
> * ratio between total database size and ECS
> * ratio between table size and ECS
> * ratio between index size and ECS
> * whether the table is "hot" or not
> * whether the index is "hot" or not
> The last two statistics are critically important for good estimation,
> and they are not things we currently collect. By "hot" I mean: is this
> a relation which is accessed several times per minute/hour and is thus
> likely to be in the cache when we need it? Currently, we have no way of
> knowing that.
> Without "hot" statistics, we're left with guessing based on size, which
> results in bad plans for small tables in large databases which are
> accessed infrequently.
> Mind you, for large tables it would be even better to go beyond that and
> actually have some knowledge of which
do you mean 'area' of the tables ?
> disk pages might be in cache.
> However, I think that's beyond feasibility for current software/OSes.
maybe not :) mincore is available in many OSes, and windows have
options to get those stats too.
> Josh Berkus
> PostgreSQL Experts Inc.
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
In response to
pgsql-performance by date
|Next:||From: Ezequiel Lovelle||Date: 2011-05-15 22:02:39|
|Subject: slow loop inserts?|
|Previous:||From: Robert Haas||Date: 2011-05-15 21:12:43|
|Subject: Re: DBT-5 & Postgres 9.0.3|