osm2pgsql imported planet slow queries

[pavel-rosputko]

I imported a full planet via osm2pgsql.

For example "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" query

and

SELECT ST_AsBinary("way") AS geom,"area","name","type" from ( SELECT COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type, name, way_area AS area, ST_PointOnSurface(way) AS way FROM planet_osm_polygon WHERE name IS NOT NULL AND ST_SetSRID(way,900913) && ST_SetSRID('BOX3D(242458.2537205777 5068997.967759746,243681.2461731433 5070220.960212312)'::box3d, 900913) AND ST_IsValid(way)  UNION ALL

  SELECT 'building' AS type, name, way_area AS area,     ST_PointOnSurface(way) AS way   FROM planet_osm_polygon   WHERE name IS NOT NULL     AND building NOT IN ('', 'no', '0', 'false')     AND ST_SetSRID(way,900913) && ST_SetSRID('BOX3D(242458.2537205777 5068997.967759746,243681.2461731433 5070220.960212312)'::box3d, 900913)     AND ST_IsValid(way)   ORDER BY area DESC ) AS data

hangs forever. Does anyone encounter this problem?

When I import only my city rendering also takes reasonable time.

[jazzzz]

The problem is the ST_SetSRID function, I fixed it in the pull request #57. Upgrade and retry.

[pavel-rosputko]

Thanks! It started working for small areas like cities. But it still hangs for large areas like a whole continent with 4 zoom level.

SELECT ST_AsBinary("way") AS geom,"admin_level" from ( SELECT way, admin_level + FROM planet_osm_line + WHERE boundary = 'administrative' + AND admin_level IN ('2','3','4') + ) AS data WHERE "way" && ST_SetSRID('BOX3D(-6261721.35712164 -1252344.271424328,1252344.271424328 6261721.35712164)'::box3d, 900913)

[kapouer]

The queries in project.mml are clearly not optimized for levels below 3 or 4.

SELECT count(*) FROM planet_osm_polygon WHERE way_area > 1000000
  count  
---------
 1444446

and landuse_gen0 filters by way_area > 100000, so for low levels it would return a lot of rows.

[springmeyer]

@kapouer - yes, this is true, the osm2pgsql based queries are not very optimized, since this recent stylesheet development put more emphasis on the imposm based schema. But ideally both could be optimized.

[kapouer]

@springmeyer i actually had a big problem elsewhere (was testing vector tiles...). Sorry for my not-so-relevant comment.

[YingcaiDong]

Thanks! It started working for small areas like cities. But it still hangs for large areas like a whole continent with 4 zoom level.

SELECT ST_AsBinary("way") AS geom,"admin_level" from ( SELECT way, admin_level + FROM planet_osm_line + WHERE boundary = 'administrative' + AND admin_level IN ('2','3','4') + ) AS data WHERE "way" && ST_SetSRID('BOX3D(-6261721.35712164 -1252344.271424328,1252344.271424328 6261721.35712164)'::box3d, 900913)

Have you still getting this result after using the indexing?