It’s common to want to spatially order a table for improved performance. This is best done with the ST_GeoHash function, which allows you to put your data into a spatially correlated order using a Morton code.
This can be done with the SQL
1 2 3 

It turns out there’s three ways to improve the performance of this.
Quicker transforms
ST_GeoHash
requires coordinates in EPSG:4326, so they need to be transformed into that projection. But this involves reprojecting every point. For any projections that don’t cross the boundaries of EPSG:4326 or excessively distort lines, we can use the fact that the bounding box of the transformed geometry is the transformed bounding box of the original geometry. For data stored in EPSG:3857 (Spherical Mercator), this is always true. Even in odder projections, it’s still close enough to be useful for clustering.
The ST_Envelope
function gives the bounding box of a geometry, so we can change the SQL
1 2 3 

Shorter GeoHashes
ST_GeoHash
takes an argument which lets you define the length of the resulting GeoHash. It turns out that 10 GeoHash characters are ideal, being 5% faster than the full length GeoHashes. It also doesn’t impact the quality of the resulting order, as 12 characters only has 0.02% more unique hashes on a planetwide dataset. Shorter would be worse, as 8 characters has 23% fewer hashes.
Our SQL is now 15% faster
1 2 3 

Stupider sorting
ST_GeoHash
produces text, which on most databases will be sorted with a localespecific UTF8 sort. This will properly sort accents, capitals, and other special characters. But GeoHashes are in base32, using only 09 and bz. This lets us sort in the “C” locale, which sorts by byte order.
Changing the ORDER BY collation lets us change how it’s sorted
1 2 3 

This SQL is 40% faster than we started with, and it’s what osm2pgsql has started using to save up to an hour off of some imports.