Re: The science of optimization in practical terms?

On Feb 17, 2009, at 11:23 PM, Robert Haas wrote:
>> Actually, a simple algorithm that might work really well would be to
>> calculate relation cache odds as ( number of page accesses for
>> relation /
>> number of page accesses for all relations ) * ( sum(relpages)*BLKSZ /
>> eff_cache_size ), where number of page accesses would be both from
>> relcache
>> and not.
>
> I don't think that formula makes any sense. If effective_cache_size
> is in the denominator, then increasing it will make the odds of
> finding the page in cache go down.

Yes, sorry... I got that part of the equation upside-down. It should be:

( number of page accesses for relation / number of page accesses for
all relations ) * ( eff_cache_size / sum(relpages)*BLKSZ )

>> One thing this doesn't address though is the report from a few
>> months ago that accessing small tables is still faster with an
>> index scan,
>> even if we know the whole thing is in cache (I don't remember if
>> that was
>> ever resolved...)
>
> I'm not sure if this is what you're referring to, but there was a
> relatively recent post on, I believe, -performance, where a bitmap
> index scan that hit almost the entire table beat out a seqscan. I
> don't think there was any further discussion and I'm still mystified
> as to how it's possible.

What I was thinking of was that when dealing with a very small table
(one or maybe a few pages), the planner thinks that a seqscan is the
fastest way to get a single row, but it's actually faster to use an
index. The bitmap case is even more interesting. Something is
seriously screwy with small seqscans it seems.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828