I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or so queries for the map rendering is static, except that the bounding box for each tile changes.
Is this a common situation? What's the best approach? For example:
Create an index exactly matching each query:
CREATE index roads_high_idx ON planet_osm_line USING gist(way) WHERE (highway IS NOT NULL OR railway IS NOT NULL) AND (tunnel IS NULL OR tunnel = 'no') AND (bridge IS NULL OR bridge = 'no');
Pre-load all the queries into other tables:
SELECT [columns] INTO roads_high FROM planet_osm_line; CREATE index roads_high_idx ON planet_osm_line WHERE (highway IS NOT NULL OR railway IS NOT NULL) AND (tunnel IS NULL OR tunnel = 'no') AND (bridge IS NULL OR bridge = 'no') ORDER BY ...;
Some smarter approach, where Postgres figures this out implicitly?
Although all the queries are known, they're buried in an XML project file, so it's not quite trivial to extract them all.