Subject Re: [Firebird-Architect] Index structures
Author Jim Starkey
At 10:29 AM 5/24/2003 -0400, Ann W. Harrison wrote:
>At Fulda, I was approached by Nickolay Samofatov and Arno
>Brinkmann on the subject of numeric indexes. They did
>some performance measurements that suggest three things.
>First the engine spends an inordinate amount of time in
>Second, the engine spends measurably less time in that
>routine if there's a hot floating point box on the machine.

This seems a little implausible to me. Before any conclusions are drawn,
I'd like to see the data.

The reason that I'm skeptical is index compress is called only during key
generation and index node insertion, both operations where the total cost
of the operation dwarfs the cycles consumed in key generation. The
comparison is between finding the index root, traversing (generally) three
index pages, each requiring examination of a couple of hundred index nodes,
to the cost of converting a quad to a double. The performance gain is
likely to be in the fourth or fifth decimal place if its detectable at all.

The whole point of the index design is to move the "expensive" operation --
key creation -- up front so the indexes can be scanned with the fewest
cycles possible. All keys are generated so they can be compared as unsigned
bytes so no scan time type checking needs to be done.

Furthermore, since we can expect the historical trend of processor speed
continuously increasing while disk speed remains essentially constant,
looking for tiny gains is next to pointless -- the engine should always be
disk bound.

Strategically, the key to dramatic performance enhancement is using the vast
amount of dirt cheap memory available. A major design goal of the original
implementation was to have an extremely faster request compiler and optimizer.
That all well and good, but if the memory is available, caching compiled
beats the pants off a fast compiler any day (but only if the memory consumed
by the statement cache doesn't induce paging overhead, which it would in
1984 and doesn't in 2003).

There may be something funny going on in btr compress, but I don't think its
the floating point conversion. I could be wrong, but I'd need to be convinced.

Jim Starkey