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.

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.

enter image description here

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 
(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 sync;

sudo service postgresql stop;

sudo echo 3 | sudo tee /proc/sys/vm/drop_caches;

sudo service postgresql start

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.