[Tile-serving] [osm2pgsql] Investigate clustering on geohash (#87)

[Paul Norman]

With a BC extract I investigated the average pinning in the lower mainland. The pinning number is the number of pages that have data that `&&` intersect a particular point. I imported with -l to make it easier.

With ORDER BY way: 6.22
With CLUSTER on gist(way): 5.99-6.10
With cluster on btree(osm_id): 5.56
with cluster on st_geohash(way): 5.77
way_area:5.66
md5((osm_id*way_area)::text): 6.26
md5(osm_id::text): 6.26

The md5 queries are there to represent something "random"

Because rendering queries are areas not points I tried buffering by 0.001 degrees.

Geohash: 6.13
ID: 6.18
gist(way) at 6.61-7.2.
ORDER BY way: 7.11

At 0.01 degrees geohash is 14.29, 
ID is 18.86 and 
gist is 14.17-15.67
ORDER BY way: 30.77

I have to run tests on the other tables, but for the polygon table ORDER BY is clearly a *bad* idea in these tests. Either no clustering, geohash, or gist appear to be the best options.

I talked to people in #postgresql and there's no theoretical reason why ORDER BY would be particularly helpful.

---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/87#issuecomment-28284833
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20131112/ef9e8a16/attachment.html>