I'd like to try to optimize a world-wide 10m contour lines (isohypses) Postgis database(from SRTM).
The complete DB takes 504GB on disk, contour lines are already in epsg:3857, and simplified with ogr2ogr at 4m. The DB is used for rendering tiles with Mapnik. What I tried so far is:
- convert elevation to smallint instead of numeric(12,3).
- add a boolean column to 50m and 100m lines instead of using the % operator in mapnik queries, indexed or not.
- CLUSTER using ST_GEOHASH().
The results are shown below for a 20x20km and a 50x50km box obtained on a 7200rpm HDD, and I'm a bit disappointed cause I expected huge improvements. These test results are obtained on a small 630MB extract (~550x750km in the Alps).
I'm not surprised the clustering did not add much improvement: the contours lines have been pushed into Postgresql sequentially from 1x1° shapefiles, so the disk order is probably fine enough.
Question: Maybe I made mistakes in my method, or maybe I forgot to try something else ? Any suggestions ?
More details below.
Convert elevation from numeric() to smallint:
ALTER TABLE contours ALTER COLUMN height TYPE smallint;
Boolean column for 50m lines:
ALTER TABLE contours ADD COLUMN _50m boolean; UPDATE contours SET _50m = NOT CAST( height % 50 AS BOOLEAN ); CREATE INDEX _50m_geom_idx ON contours (_50m);
Cluster by geohash:
DROP INDEX contours_geom_idx; CREATE INDEX contours_geohash ON contours (ST_GeoHash(ST_Transform(wkb_geometry,4326))); CLUSTER contours USING contours_geohash; DROP INDEX contours_geohash; CREATE INDEX contours_geom_idx ON contours USING GIST (wkb_geometry);
A typical query (adapted from mapnik queries):
EXPLAIN ANALYSE SELECT ST_AsBinary("wkb_geometry") AS geom FROM (SELECT wkb_geometry,height::integer FROM contours WHERE _50m) AS "contours-50" WHERE "wkb_geometry" && ST_SetSRID('BOX2D(698000 5678000,748000 5728000)'::box3d, 3857);
Drop cache between each test query:
sudo service postgresql stop;
sudo echo 3 | sudo tee /proc/sys/vm/drop_caches;
sudo service postgresql start