osm2pgsql imported planet slow queries
- Dominant language
- CartoCSS
- Stars
- 703
- Forks
- 222
- PR merge metrics
- No merged PRs in 30d
Description
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.
Contributor guide
Research direction
The report provides slow SQL queries against planet_osm_polygon after a full osm2pgsql import, but names no repository file or test. Start by reproducing the queries on a full-planet import and inspect the database behavior; done would require identifying a confirmed cause and documenting a project-specific resolution or guidance.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100