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.
VACUUM ANALYZE planet_osm_polygon
; add an (btree) index onway_area
; also, useBOX2D
(why 3D?)BOX3D
toBOX2D
? This is somehow defined in OSM style?VACUUM ANALYZE planet_osm_polygon
.