Tile server running slow queries

Question

Hi all,

i recently installed my own OSM Tileserver on a very capable machine, expecting the performance to be quite good. Unlucky for me, most tiles even at high zoom levels take much too long for a smooth user interaction. I will append information about the system and used software below.

I found the actual slow part to be the Database, particulary some queries involving postgis functions on larger geometries. An example is the following query:

SELECT ST_AsBinary("way") AS geom,"area","name","type" FROM
(
    SELECT COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type,
    name, way_area AS area,   ST_PointOnSurface(way) AS way
    FROM planet_osm_polygon
    WHERE name IS NOT NULL
    AND way && ST_SetSRID('BOX3D(1335354.884142745 6090349.539709539,1338106.617161014 6093101.272727807)'::box3d, 900913)
    AND ST_IsValid(way)

    UNION ALL

    SELECT 'building' AS type, name, way_area AS area, ST_PointOnSurface(way) AS way
    FROM planet_osm_polygon
    WHERE name IS NOT NULL
    AND building NOT IN ('', 'no', '0', 'false')
    AND way && ST_SetSRID('BOX3D(1335354.884142745 6090349.539709539,1338106.617161014 6093101.272727807)'::box3d, 900913)
    AND ST_IsValid(way)
    ORDER BY area DESC
) AS data
;

The actual slow part seems to be, much to my surprise, the ST_IsValid and ST_PointOnSurface function, which take ~400ms each on a geometry that contains 134283 points. The question now is, can i - and how - reduce the time spent for those queries? 400ms for ST_IsValid seems unreasonable to me. Any help is much appreciated.

Stats and diagnostics

I used the very nice tutorial at: http://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/

Server specs:

Intel(R) Xeon(R) CPU E5-1650 v2 @ 3.50GHz
6 Cores, Hyperthreading
128 GiB RAM
2x 480GB SSD in Hardware RAID1

Software used:

Debian wheezy
Linux kernel 3.2.0
postgreSQL 9.4
postGIS 2.1
mapnik 2.2
mod_tile master from git@github.com:openstreetmap/mod_tile.git

Mapnik style:

OSM Bright from https://github.com/mapbox/osm-bright/archive/master.zip

PostgreSQL configuration:

shared_buffers = 24576MB
temp_buffers = 8MB
work_mem = 256MB
maintenance_work_mem = 32768MB

Answer 1

I've noticed the same problem with our tile server. Queries which use ST_PointOnSurface and ST_IsValid -- even in combination with a bounding box -- take several seconds to run, which kills our rendering performance. I found an intro to PostGIS on Boundless which states, "ST_PointOnSurface(geometry) returns a point that is guaranteed to be inside the input argument. It is substantially more computationally expensive than the centroid operation." In my testing, I found that replacing ST_PointOnSurface with ST_Centroid and removing ST_IsValid from the query can result in about a 10-fold performance gain (396 ms vs 4533 ms for my test query, which is similar to the one in the question above).

The problem with changing to ST_Centroid is that the centroid of a polygon may not fall within its bounds. I ran a couple of queries to see how many OSM polygons with labels have a centroid outside of their bounds:

SELECT count(*) FROM planet_osm_polygon WHERE name IS NOT NULL AND ST_Contains(way, ST_Centroid(way));
  count
---------
 6392511
(1 row)

select count(*) from planet_osm_polygon where name is not null;
  count  
---------
 6624842
(1 row)

It appears that roughly 4% of the labeled polygons do not contain their centroid.

Alternate queries

If speed is the most important consideration in rendering, you can rework your query to output the centroid of the geometry as follows:

SELECT COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type,
    name, way_area AS area,
    ST_Centroid(way) AS way
  FROM planet_osm_polygon
  WHERE name IS NOT NULL
    AND way && !bbox!

  UNION ALL

  SELECT 'building' AS type, name, way_area AS area,
    ST_Centroid(way) AS way
  FROM planet_osm_polygon
  WHERE name IS NOT NULL
    AND building NOT IN ('', 'no', '0', 'false')
    AND way && !bbox!
  ORDER BY area DESC

The following form of the query uses the centroid when it is inside the polygon and ST_PointOnSurface when it is outside. It will always return a point within the polygon, at the cost of a little extra processing time:

SELECT COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type,
    name, way_area AS area,
    CASE WHEN ST_Contains(way, ST_Centroid(way)) THEN ST_Centroid(way) WHEN ST_IsValid(way) THEN ST_PointOnSurface(way) ELSE null END AS way
  FROM planet_osm_polygon
  WHERE name IS NOT NULL
    AND way && !bbox!

  UNION ALL

  SELECT 'building' AS type, name, way_area AS area,
    CASE WHEN ST_Contains(way, ST_Centroid(way)) THEN ST_Centroid(way) WHEN ST_IsValid(way) THEN ST_PointOnSurface(way) ELSE null END AS way
  FROM planet_osm_polygon
  WHERE name IS NOT NULL
    AND building NOT IN ('', 'no', '0', 'false')
    AND way && !bbox!
  ORDER BY area DESC

In my measurements, the first query is about 10x as fast as the original, and the second about 5x as fast. We are planning to try the faster query on our tile servers. If the label placement looks strange, we will probably switch to the second version to see if there is any improvement.

Tile server running slow queries

Alternate queries

Follow this question

Related questions