Add conditions and functional indexes on ST_PointOnSurface(way)

[pnorman]

A typical layer with points from polygons looks like

(SELECT
    way,
    "natural"
  FROM
  (SELECT
      ST_PointOnSurface(way) AS way,
      "natural"
    FROM planet_osm_polygon
    WHERE way && !bbox!
  UNION ALL
  SELECT
      way,
      "natural"
    FROM planet_osm_point
    WHERE way && !bbox!
    ) _
  WHERE "natural" IN ('spring')
) AS springs

Adding a functional index with CREATE INDEX ON planet_osm_polygon USING GIST (ST_PointOnSurface(way)) and adding ST_PointOnSurface(way) && !bbox! to the polygon branch of the union all allows a query plan which avoids calculating ST_PointOnSurface for polygons where the point isn't within the metatile. This doesn't matter much for some layers like buildings, addresses, or springs, but it can be a savings for layers with lots of polygons that bounding box intersect the current MT but don't have the label point within the bbox.

On the other hand, these polygons are by definition large in relation to the current screen size, and we tend to exclude those with area filters (#1028). Perhaps we could get the same or greater gains by moving the area filters from MSS to SQL.

[imagico]

On the other hand, these polygons are by definition large in relation to the current screen size, and we tend to exclude those with area filters (#1028). Perhaps we could get the same or greater gains by moving the area filters from MSS to SQL.

Yes, the most promising candidate for that is the amenity-points layer.

Most way_pixels filters which have an upper threshold use 768000 pixels and most which don't could well use one. There is also still a lot of inconsistency in this with some rules using way_area and some using way_pixels in amenity-points.mss. Sorting this out and adding a universal threshold to the SQL code would be a good idea.

I don't know how much a functional index will help - that surely depends on how significant the costs of calculating ST_PointOnSurface(way) are practically.