openstreetmap/osm2pgsql — pull request #242 by pnorman (2014-12-30 05:22:51+00:00)
Title: Use GeoHash ordering instead of way ordering

Creating a table as ORDER BY way is known to offer no performance advantages (#87) and in fact ahve losses in some cases. `ST_GeoHash` offers a better way to have geographically nearby data in the same or nearby pages.

Rather than simply doing an order on `ST_GeoHash(ST_Transform(way,4326))`, we can get a total of a 15% gain by using `ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)`.

##Benchmark Details##

The polygons table from planet-130904.osm.pbf was used, and a new table created.
```sql
CREATE TABLE polygon_test AS
SELECT *
FROM planet_osm_polygon
ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),);
```
With this, we can see the change in ORDER time as the number of characters is varied, which leads to the selection of a 10 character geohash.
![image](https://cloud.githubusercontent.com/assets/1190866/5575881/7f84a612-8fa0-11e4-8e6d-a9e3b22e02d7.png)

Base time of `ST_GeoHash(ST_Transform(ST_Envelope(way),4326))` was 1848 seconds, and `ST_GeoHash(ST_Transform(way,4326))` was 2011 seconds.

There is a theoretical basis for preferring a geohash with an even number of characters, which this is.