Subject Index structures
Author Ann W. Harrison
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.

Third, with large index buckets, the cost of walking a
leaf node is considerable.

An hypothesis is that the conversion from long to
double in compress is expensive. It may be
possible to map shorts and longs into the new
64bit key type. Converting columns between integer
types and floating types would (probably) require
rebuilding the index, but conversions between integer
types would not.

Arno and Nickolay also argued for a separate index
type for longs and to eliminate key compression
(prefix and suffix) for single key integer indexes.

The first was just (I think) a recognition that
single key indexes on longs are very common and
promoting them to quads seems extravagant.

The second would allow a binary search of a leaf
level index.

Scaled integers are a problem. Changing a column
from long to double is relatively rare, now that
64-bit integers are supported. Changing the scale
is extremely common.