In my last post I took a sample of PostgreSQL queries and loaded them into a table to do some analysis. The next step is to

This log will include queries from osm2pgsql updating the database, but normally we’re only optimizing rendering queries. Mapnik starts all of these with SELECT ST_AsBinary("way") AS geom so we can make a view of just rendering queries with CREATE VIEW render_query_log AS SELECT * FROM query_log WHERE query LIKE 'SELECT ST_AsBinary("way") AS geom%';

There are some tools to turn PostgreSQL queries back into style layers and zoom, but none of them work well on modern complex style queries, so we need to figure out a different way to group queries for the same layers.

Mapnik has four tokens which can appear in a query.

1. !bbox! is replaced the buffered bounding box of the area being rendered. This text ends up looking like ST_SetSRID('BOX3D(...

2. !scale_denominator! is replaced by the scale in projected units per pixel. OpenStreetMap Carto doesn’t use this, but it’s often seen in vector tile styles to calculate the zoom level. This is a number, either an integer or floating point depending on zoom.

3. !pixel_width! and !pixel_height! which are the width and height of a pixel in projected units. These are both numbers, either integers or floating points. They can be zero. In OpenStreetMap carto they are alway seen in the form way_area/NULLIF(!pixel_width!::real*!pixel_height!::real,0) or way_area > [some number]*!pixel_width!::real*!pixel_height!::real.

To get the common part of the query we can cut off the query text at the first occurance of ST_SetSRID or way_area. The former is only inserted by Mapnik and the latter only appears on lines with Mapnik tokens or as part of an `ORDER BY very late in the query.

Splitting the string by these tokens and taking the first part can solve this.

1
2
3
4

SELECT count(*), sum(run_length), (regexp_split_to_array(query, '(way_area|ST_SetSRID)'))[1] AS q
  FROM render_query_log
  GROUP BY q
  ORDER by sum DESC;

SELECT count(*), sum(run_length), query q FROM render_query_log GROUP BY q

regexp_split_to_array(‘select foo from a way_area b seasdfas’, ‘(way_area|ST_SetSRID)’)