Hi,
is the database server overloaded? A user notified me that the POI
overlays from hstore columns are very slow.
A year ago when introduced it worked like a charm, usually queries
returned results in much than 1s.
Now I saw it running for minutes one time! I'm quite concerned about
this. Did it really run that long?
Did the new map layers being rendered put that much additional load on
the database?
Is there any possibility to see the actual load of the DB? A while ago I
asked for a munin or similar which was not available.
It's not the same as ptolemy, right?
Would it make sense to track down slow mapnik queries?
Probably some layer is eating up resources.
Situation seams to improve a lot after the index seams to be loaded to
RAM as subsequent queries are a lot faster.
Any ideas on how to proceed with this?
One of the most complex queries is explained as follows. You can see it
uses always indexes.
SELECT st_astext(st_makepoint((st_x(point)+st_x(cluster))/2,
(st_y(point)+st_y(cluster))/2)) as point, osm_id, name, count FROM (
SELECT st_centroid(st_collect(way)) AS point, min(osm_id) AS osm_id,
min(name) AS name, count(*),
st_astext(st_makepoint(floor(st_x(way)/117407.275425)*117407.275425,floor(st_y(way)/117407.275425)*117407.275425))
as cluster, min(way) as pos FROM ( SELECT st_centroid(way) AS way,
osm_id, name FROM planet_polygon WHERE ((tags @> '"smoking"=>"yes"'
OR tags @> '"smoking"=>"separated"' OR tags @> '"smoking"=>"dedicated"'
OR tags ? 'smoking_hours') AND (amenity='restaurant' OR amenity='bar' OR
amenity='pub' OR amenity='cafe' OR amenity='fast_food' OR
amenity='food_court' OR amenity='nightclub')) UNION ALL SELECT way,
osm_id, name FROM planet_point WHERE ((tags @> '"smoking"=>"yes"' OR
tags @> '"smoking"=>"separated"' OR tags @> '"smoking"=>"dedicated"' OR
tags ? 'smoking_hours') AND (amenity='restaurant' OR amenity='bar' OR
amenity='pub' OR amenity='cafe' OR amenity='fast_food' OR
amenity='food_court' OR amenity='nightclub')) ) AS t GROUP BY cluster )
AS t2 WHERE pos && SetSRID('BOX3D(-2044741.666614
4533998.8988156,4544741.666614 8066001.1011844)'::box3d,900913) LIMIT 500;
I could rewrite it to this to first test against existing of the hstore
then the query against the values. Would this be better? but "@>" is
already using indexes.
"Limit (cost=235173.39..235193.89 rows=200 width=108)"
" -> Subquery Scan t2 (cost=235173.39..235193.89 rows=200 width=108)"
" -> HashAggregate (cost=235173.39..235185.89 rows=200 width=68)"
" Filter:
((min(((st_centroid(planet_polygon.way)))::text))::geometry &&
'010300002031BF0D0001000000050000001137A7AA45333FC1DE3186B9BB4B51411137A7AA45333FC122CE7946F4C45E41C4CDA96A3956514122CE7946F4C45E41C4CDA96A39565141DE3186B9BB4B51411137A7AA45333FC1DE3186B9BB4B5141'::geometry)"
" -> Result (cost=2853.58..235160.18 rows=755 width=68)"
" -> Append (cost=2853.58..235141.31 rows=755
width=68)"
" -> Bitmap Heap Scan on planet_polygon
(cost=2853.58..140405.42 rows=411 width=1045)"
" Recheck Cond: ((((tags @>
'"smoking"=>"yes"'::hstore) AND (tags ? 'smoking'::text)) OR ((tags @>
'"smoking"=>"separated"'::hstore) AND (tags ? 'smoking'::text)) OR
((tags @> '"smoking"=>"dedicated"'::hstore) AND (tags ?
'smoking'::text))) OR (tags ? 'smoking_hours'::text))"
" Filter: ((((tags ? 'smoking'::text) AND
((tags @> '"smoking"=>"yes"'::hstore) OR (tags @>
'"smoking"=>"separated"'::hstore) OR (tags @>
'"smoking"=>"dedicated"'::hstore))) OR (tags ? 'smoking_hours'::text))
AND ((amenity = 'restaurant'::text) OR (amenity = 'bar'::text) OR
(amenity = 'pub'::text) OR (amenity = 'cafe'::text) OR (amenity =
'fast_food'::text) OR (amenity = 'food_court'::text) OR (amenity =
'nightclub'::text)))"
" -> BitmapOr (cost=2853.58..2853.58
rows=38649 width=0)"
" -> BitmapOr (cost=87.72..87.72
rows=116 width=0)"
" -> Bitmap Index Scan on
planet_polygon_tags (cost=0.00..29.14 rows=39 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"yes"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_polygon_tags (cost=0.00..29.14 rows=39 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"separated"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_polygon_tags (cost=0.00..29.14 rows=39 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"dedicated"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_polygon_tags (cost=0.00..2765.75 rows=38534 width=0)"
" Index Cond: (tags ?
'smoking_hours'::text)"
" -> Bitmap Heap Scan on planet_point
(cost=2440.45..94728.34 rows=344 width=121)"
" Recheck Cond: ((((tags @>
'"smoking"=>"yes"'::hstore) AND (tags ? 'smoking'::text)) OR ((tags @>
'"smoking"=>"separated"'::hstore) AND (tags ? 'smoking'::text)) OR
((tags @> '"smoking"=>"dedicated"'::hstore) AND (tags ?
'smoking'::text))) OR (tags ? 'smoking_hours'::text))"
" Filter: ((((tags ? 'smoking'::text) AND
((tags @> '"smoking"=>"yes"'::hstore) OR (tags @>
'"smoking"=>"separated"'::hstore) OR (tags @>
'"smoking"=>"dedicated"'::hstore))) OR (tags ? 'smoking_hours'::text))
AND ((amenity = 'restaurant'::text) OR (amenity = 'bar'::text) OR
(amenity = 'pub'::text) OR (amenity = 'cafe'::text) OR (amenity =
'fast_food'::text) OR (amenity = 'food_court'::text) OR (amenity =
'nightclub'::text)))"
" -> BitmapOr (cost=2440.45..2440.45
rows=26840 width=0)"
" -> BitmapOr (cost=78.01..78.01
rows=80 width=0)"
" -> Bitmap Index Scan on
planet_point_tags (cost=0.00..25.92 rows=27 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"yes"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_point_tags (cost=0.00..25.92 rows=27 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"separated"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_point_tags (cost=0.00..25.92 rows=27 width=0)"
" Index Cond: ((tags @>
'"smoking"=>"dedicated"'::hstore) AND (tags ? 'smoking'::text))"
" -> Bitmap Index Scan on
planet_point_tags (cost=0.00..2362.35 rows=26760 width=0)"
" Index Cond: (tags ?
'smoking_hours'::text)"
A complete rewrite of the POI filtering is on the go to use a separate
DB, but not available within the next days. As an immediate measure I
reduced the initial zoom so a smaller bounding box would limit the
stress on the DB. I counted the log-hits, the service is low-volume,
just 1700 DB-queries the last week. But I still would like to improve
the situation.
Stephan
