Best read performance when every query is known, and the data doesn't change 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.
Off the top of my head, here are a few things to do, in no particular order.


VACUUM FREEZE
ANALYZE
Add the indexes that you think are going to help.
Execute as many queries as you can.
Look at pg_stat_user_indexes to see which queries are being used.
Look at the EXPLAIN ANALYZE plan for each query.
Review https://wiki.postgresql.org/wiki/Slow_Query_Questions
Review https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Post follow-ups with server specs and questions to SO or the Postgresql mailing lists (most of the core developers answer questions in the pg lists).


It will likely be an iterative process, so don't expect to get it right in the first go.