Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. 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');
    
  2. 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 ...;
    
  3. 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.

share|improve this question
    
Häh? Normally you may have 1 CPU, 4 cores. But having more cpu's than cores - what did you do? Hit a core with a hammer and distribute the parts into multiple CPU slots? –  TomTom Oct 25 '13 at 5:30
2  
Do you already have a performance problem? If so you should post the specific details. If not, you should properly index and then see how it goes. –  a_horse_with_no_name Nov 24 '13 at 9:36

1 Answer 1

Off the top of my head, here are a few things to do, in no particular order.

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

share|improve this answer
1  
I also strongly suggest creating the indexes only after the data load. It'll not only make the data load way faster, but it'll produce more compact and sensible indexes. –  Craig Ringer Nov 24 '13 at 10:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.