0

I'm trying to optimize my OpenStreetMap map tiles rendering.

This is one of the queries renderd makes to PostgreSQL:

SELECT ST_AsBinary("way") AS geom,
       "type"
FROM
  (SELECT way,
          way_area AS area,
          COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS TYPE
   FROM planet_osm_polygon
   WHERE way_area > 100000
   ORDER BY way_area DESC) AS DATA
WHERE "way" && ST_SetSRID('BOX3D(-626172.1357124988 -626172.1357125007,10644926.3071125 10644926.30711249)'::box3d, 900913)

This is a result of EXPLAIN ANALYZE:

                                                                                                                                         QUERY PLAN                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on data  (cost=12419561.95..12504420.70 rows=5657250 width=249) (actual time=426086.657..494431.204 rows=5359537 loops=1)
   ->  Sort  (cost=12419561.95..12433705.08 rows=5657250 width=270) (actual time=425901.123..429052.490 rows=5359537 loops=1)
         Sort Key: planet_osm_polygon.way_area DESC
         Sort Method: external merge  Disk: 5134488kB
         ->  Seq Scan on planet_osm_polygon  (cost=0.00..9638717.64 rows=5657250 width=270) (actual time=1.714..353664.879 rows=5359537 loops=1)
               Filter: ((way_area > '100000'::double precision) AND (way && '010300002031BF0D000100000005000000D01B7C45F81B23C1E01B7C45F81B23C1D01B7C45F81B23C193DDD3C9B74D644198DDD3C9B74D644193DDD3C9B74D644198DDD3C9B74D6441E01B7C45F81B23C1D01B7C45F81B23C1E01B7C45F81B23C1'::geometry))
               Rows Removed by Filter: 183821045
 Planning time: 15.584 ms
 Execution time: 495248.135 ms
(9 rows)
Improve this question
5
  • try: run VACUUM ANALYZE planet_osm_polygon; add an (btree) index on way_area; also, use BOX2D (why 3D?)
    – geozelot
    Oct 5, 2018 at 13:02
  • did you get some better results?
    – geozelot
    Oct 19, 2018 at 12:07
  • How to change BOX3D to BOX2D? This is somehow defined in OSM style?
    – Defozo
    Oct 19, 2018 at 12:11
  • 1
    no, that's a PostGIS specific bbox type for the usage with bbox comparisons; not sure if you can alter renderds standard queries (or did you write it?), though, and it's not important. the index I mentioned, however, is; if not in place, add it as I described, and run VACUUM ANALYZE planet_osm_polygon.
    – geozelot
    Oct 19, 2018 at 13:01
  • I haven't worked with renderd/tirex much, but I guess those are standard queries defined in the framework; if you can alter those, use the one I suggested, if not, DB tuning is what you're left with, and an index would speed up things in the scope of this qiestion.
    – geozelot
    Oct 19, 2018 at 13:04

2 Answers 2

Reset to default
1

Following my comment:

  • Add index on way_area:

    CREATE INDEX planet_osm_polygon_way_area_idx
  ON planet_osm_polygon (way_area);

  • update table stats:

    VACUUM ANALYZE planet_osm_polygon;

  • run a simplified

    SELECT ST_AsBinary(way) AS geom,
       COALESCE(landuse, "natural", highway, amenity, tourism) AS "type"
FROM planet_osm_polygon
WHERE way && ST_SetSRID('BOX2D(-626172.1357124988 -626172.1357125007,10644926.3071125 10644926.30711249)'::box2d, 900913)
  AND way_area > 100000

Runs about three times faster with index, according to EXPLAIN ANALYZE, on my setup.

Note that adding another index, however, would only really be worth it if you run this over and over again; indexes have a significant footprint on disk and large indexes might have an impact on overall cluster performance.

Improve this answer
1

I created the following indexes:

su - postgres -c "psql -c \"CREATE INDEX idx_poly_idlanduse ON planet_osm_polygon USING gist (way) WHERE((landuse IS NOT NULL) OR (leisure IS NOT NULL) OR (aeroway = ANY ('{apron,aerodrome}'::text[])) OR (amenity = ANY ('{parking,university,college,school,hospital,kindergarten,grave_yard}'::text[])) OR (military = ANY ('{barracks,danger_area}'::text[])) OR ('natural' = ANY ('{field,beach,desert,heath,mud,grassland,wood,sand,scrub}'::text[])) OR (power = ANY ('{station,sub_station,generator}'::text[])) OR (tourism = ANY ('{attraction,camp_site,caravan_site,picnic_site,zoo}'::text[])) OR (highway = ANY ('{services,rest_area}'::text[])));\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"planet_osm_polygon_nobuilding_index\" ON \"planet_osm_polygon\" USING gist (\"way\") WHERE \"building\" IS NULL;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX ferry_idx ON planet_osm_line USING gist (way) WHERE (route = 'ferry'::text);\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_aeroway\" on planet_osm_polygon  USING gist (way) WHERE \"aeroway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_historic\" on planet_osm_polygon  USING gist (way) WHERE \"historic\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_leisure\" on planet_osm_polygon  USING gist (way) WHERE \"leisure\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_man_made\" on planet_osm_polygon  USING gist (way) WHERE \"man_made\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_military\" on planet_osm_polygon  USING gist (way) WHERE \"military\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_power\" on planet_osm_polygon  USING gist (way) WHERE \"power\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_landuse\" on planet_osm_polygon  USING gist (way) WHERE \"landuse\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_amenity\" on planet_osm_polygon  USING gist (way) WHERE \"amenity\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_natural\" on planet_osm_polygon  USING gist (way) WHERE 'natural' IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_highway\" on planet_osm_polygon  USING gist (way) WHERE \"highway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_tourism\" on planet_osm_polygon  USING gist (way) WHERE \"tourism\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_building\" on planet_osm_polygon  USING gist (way) WHERE \"building\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_barrier\" on planet_osm_polygon  USING gist (way) WHERE \"barrier\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_railway\" on planet_osm_polygon  USING gist (way) WHERE \"railway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_aerialway\" on planet_osm_polygon  USING gist (way) WHERE \"aerialway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_poly_power_source\" on planet_osm_polygon  USING gist (way) WHERE \"power_source\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_aerialway\" on planet_osm_line  USING gist (way) WHERE \"aerialway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_waterway\" on planet_osm_line  USING gist (way) WHERE \"waterway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_bridge\" on planet_osm_line  USING gist (way) WHERE \"bridge\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_tunnel\" on planet_osm_line  USING gist (way) WHERE \"tunnel\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_access\" on planet_osm_line  USING gist (way) WHERE \"access\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_railway\" on planet_osm_line  USING gist (way) WHERE \"railway\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_power\" on planet_osm_line  USING gist (way) WHERE \"power\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_name\" on planet_osm_line  USING gist (way) WHERE \"name\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_ref\" on planet_osm_line  USING gist (way) WHERE \"ref\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_aerialway\" on planet_osm_point  USING gist (way) WHERE \"aerialway\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_power_source\" on planet_osm_point  USING gist (way) WHERE \"power_source\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_shop\" on planet_osm_point  USING gist (way) WHERE \"shop\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_place\" on planet_osm_point  USING gist (way) WHERE \"place\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_barrier\" on planet_osm_point  USING gist (way) WHERE \"barrier\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_railway\" on planet_osm_point  USING gist (way) WHERE \"railway\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_amenity\" on planet_osm_point  USING gist (way) WHERE \"amenity\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_natural\" on planet_osm_point  USING gist (way) WHERE 'natural' IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_highway\" on planet_osm_point  USING gist (way) WHERE \"highway\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_tourism\" on planet_osm_point  USING gist (way) WHERE \"tourism\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_power\" on planet_osm_point  USING gist (way) WHERE \"power\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_aeroway\" on planet_osm_point  USING gist (way) WHERE \"aeroway\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_historic\" on planet_osm_point  USING gist (way) WHERE \"historic\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_leisure\" on planet_osm_point  USING gist (way) WHERE \"leisure\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_man_made\" on planet_osm_point  USING gist (way) WHERE \"man_made\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_waterway\" on planet_osm_point  USING gist (way) WHERE \"waterway\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_capital\" on planet_osm_point  USING gist (way) WHERE \"capital\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_lock\" on planet_osm_point  USING gist (way) WHERE \"lock\" IS NOT NULL ;\" -d gis"   \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_landuse\" on planet_osm_point  USING gist (way) WHERE \"landuse\" IS NOT NULL ;\" -d gis"  \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_point_military\" on planet_osm_point  USING gist (way) WHERE \"military\" IS NOT NULL ;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX idx_poly_wayarea_text ON  planet_osm_polygon USING gist (way) WHERE name IS NOT NULL AND place IS NULL AND way_area <= 320000;\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX \"idx_line_cutline\" on planet_osm_line  USING gist (way) where man_made='cutline';\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX idx_poly_buildings_lz ON planet_osm_polygon USING gist (way) where railway='station' or building in ('station','supermarket') or amenity='place_of_worship';\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX idx_poly_buildings ON planet_osm_polygon USING gist (way) where (building is not null and building not in ('no','station','supermarket','planned') and (railway is null or railway != 'station') and (amenity is null or amenity != 'place_of_worship')) or aeroway = 'terminal';\" -d gis" \
&& su - postgres -c "psql -c \"CREATE INDEX water_areas_idx ON planet_osm_polygon USING gist (way) WHERE (((waterway IS NOT NULL) OR (landuse = ANY (ARRAY['reservoir'::text, 'water'::text, 'basin'::text]))) OR ('natural' IS NOT NULL));\" -d gis" \

Based on: https://wiki.openstreetmap.org/wiki/User:Species/PostGIS_Tuning

I can see a significant improvement. Previously, a map view rendered for a few minutes. Now it's down to tens of seconds.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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