[Tile-serving] [osm2pgsql] Clustering strategies (#208)

[Christian Quest]

I've not really tried optimizing the clustering process itself (except by
using the object bbox prior to do the WGS84 transformation).

This process is done only once, while retrieving data pages is done
permanently by postgres to render tiles.

The benefit during rendering is clear on the I/O side as you can see in my
SOTM slides: https://pdf.yt/d/P0vxShtbGagwXg3Q

The I/O usage dropped from 100% to 15% (and the rendering rate also
increased as I/O were not at saturation anymore).

After some months, the I/O are increasing again as the clustering benefit
are slowly lost due to INSERT/UPDATE/DELETE in the data.

Someone also asked me after my talk why I was not computing some mercator
base tile number (this would prevent st_transform). That's something to try
too...


2014-11-10 4:23 GMT-03:00 Paul Norman <notifications at github.com>:

> CREATE TABLE ... ORDER BY ... took 1933s while CREATE INDEX ...
> ST_GeoHash...; CLUSTER ...; DROP INDEX ...; took 47734s, mainly in the
> CLUSTER.
>
> Unfortunately, I just realized that I did this with default work_mem and
> maintenance_work_mem, which make the results bad.
>
> --
> Reply to this email directly or view it on GitHub
> <https://github.com/openstreetmap/osm2pgsql/issues/208#issuecomment-62349476>
> .
>
> _______________________________________________
> Tile-serving mailing list
> Tile-serving at openstreetmap.org
> https://lists.openstreetmap.org/listinfo/tile-serving
>
>


-- 
Christian Quest - OpenStreetMap France
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20141110/488361bf/attachment.html>