Subject Re: [Firebird-Architect] Index structures
Author Jim Starkey
At 03:08 PM 6/8/2003 +0200, Arno Brinkman wrote:

> > I don't think it scales. The bigger the index the more you're hurt by the
> > fluffier
> > index. If your index goes an extra level, you lose big time. As the
> > size
> > increases relative to cache size, your cpu advantage is lost to increased
> > disk i/o.
>The extra-levels aren't there that much if they even are.

The problem is a fluffy index significantly decreases fanout -- the number of
lower level index pages controlled by a single intermediate or top level page.
It's an n-squared problem. Your indexes are generally going to be deeper.
That's a serious problem.

> > CPU double in speed every 18 months. Disks double in speed every 25
> > Trading increased disk traffic for lower CPU utilization isn't, to my old
> > foggie thinking, a win.
>Ofcourse i've to agree with that, but the index-lookup seems to be a very
>expensive task on larger page sizes. While i always read (and thus adopt)
>that a bigger page size should give a better performance? See for example
> chapter 8,
>but this seems to be not true at all. For faster index-lookups it seems a
>small page size is the best.

If you have a toy database that fits in memory, nothing matters. A small page
size is probably faster. But a completely memory resident database organized
around hash tables would be even faster, much faster. But, like your
scheme, it
doesn't scale in the real world.

Jim Starkey