[OSM-dev] Further Database optimisation and data size
nick at nickhill.co.uk
Thu Apr 20 23:51:37 BST 2006
Nick Hill wrote:
> 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.
More information about the dev