[OSM-dev] Further Database optimisation and data size

[Nick Hill]

Nick Hill wrote:

> Conclusion:
> For 10M point data sets and less, using integers for the key columns 
> improves database query speed by a factor of between 2 and 3. for 100M 
> points, query speed is improved by 55%. Tripling available memory from 
> 340M to 980M improves query times by 10-20%. For 100M data set and 
> smaller, the memory size (340M/980M) makes no difference.
>

Integers are much faster to process than floating points. This probably 
explains the huge speed improvement in the select queries- clearly much 
geater than what would be expected from the smaller data set size alone.

Given the huge processing shortcuts which are made by avoiding floating 
points, and the exemplary 2-3 fold speed improvement for select query 
speed, we should consider avoiding any floating point representation 
throughout the API and use only scaled integers. We can probably avoid 
floating point numbers completely without noticeably increasing 
programmatic complexity, except for the initial GPX import.

The transition can easily be achieved by introducing scaled integer 
methods and deprecating floating point methods.

As we move from a labratory to an industrial scale operation, these 
types of efficiency improvements become important.