I'm adding polylines (elevation curves) to a world map. Said polylines are stored in a PostgreSQL database from gdal_contour tool. The geometry columns (LineString, 3857) has a GIST index and a CLUSTER on it. When Mapnik renders tiles, the GIST index is used.
Two bottlenecks are causing my map to be extremely slow :
- Even though GIST index is used, the SELECT statement takes several seconds (usually around 30 seconds) to fetch results.
- When the SELECT statement is done, renderd takes even more time to render the tiles (around 90 seconds).
My first guess is, the contour lines are quite long, and for most of these, a big part of the geometry is probably outside of the tile. I'm guessing Mapnik works with the entire shape, even if only a small part should be drawn on the tile. If I'm right, how should I split (or import) my contour shapes correctly, to work with smaller parts instead? (I need to draw text along the polyline with text-spacing at 500, so lines should not be shorter than this).
My second guess is, since the table is quite big (currently 45 341 726 rows), the GIST index is not enough (but could be sufficient if the polylines are splitted maybe?). I'm not sure how to handle this, but I'm wondering if there's a way to split the index in several parts, and index the splitted index to speed things up? Or is this a case when GeoHash would be helpful?
The polyline layer is defined as follows :
- id: contour100 geometry: linestring Datasource: type: "postgis" dbname: "gis" key_field: "" geometry_field: "wkb_geometry" extent: "-20037508,-20037508,20037508,20037508" table: (SELECT wkb_geometry, height FROM contour100 WHERE wkb_geometry && !bbox! ) AS c100 properties: minzoom: 12
The server has 128 GB of RAM, and Xeon E5-1660v3 processor. The database is stored on NVMe drives, so the problem has to come from a wrong configuration on my side.