[Tile-serving] Performance problem serving tiles from planet file

[Frederik Ramm]

Hi,

On 01/02/2017 05:14 PM, Theodor Foerster wrote:
> I witness utterly bad performance. Parts of the query take around 300+
> seconds. Serving a tile takes approx. 20 minutes (1 tile!).

The query log you posted is for a zoom level 5 meta tile. This is not 1,
but 64 tiles, and covers 1/16th of the planet on that zoom level. Your
server is slow (you mention the throughput of your virtual volume but
the random I/O performance is what counts - if your data is not on an
SSD then you will not achieve much) but even a fast machine can take a
couple of minutes to render a tile on zoom levels 0-8 - these zoom
levels contain a huge amount of data per tile.

That's why you will usually pre-render tiles from z0-12 or so.

You can improve performance with partial indexes, for example if you see
a query like this being slow:

> 2017-01-02 15:29:01 UTC LOG:  00000: execute <unnamed>: SELECT
> ST_AsBinary("way") AS geom,"type" FROM ( SELECT way, way_area AS area,
> COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type
>           FROM planet_osm_polygon
>           WHERE way_area > 100000
>           ORDER BY way_area DESC
>         ) AS data WHERE "way" && ST_SetSRID('BOX3D(-10644926.3071125
> -626172.1357125007,626172.1357124997 10644926.30711249)'::box3d, 900913)
> 2017-01-02 15:29:01 UTC LOCATION:  exec_execute_message, postgres.c:1935
> 2017-01-02 15:37:40 UTC LOG:  00000: duration: 519101.014 ms

then do

CREATE INDEX speedup_index_001 ON planet_osm_polygon USING gist(way)
WHERE way_area > 100000;

or for the second query, copy the more complex WHERE condition into your
CREATE INDEX.

Creating these indexes takes a while too, however, and also slows down
future updates should you want to attempt those; sometimes it might be
better to simply wait for the pre-rendering to run its course.

Bye
Frederik

-- 
Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00'09" E008°23'33"