New openstreetmap-carto release, v4.12.0

[kocio-pl]

A new version of openstreetmap-carto, v4.12.0, has been released.

Please notice that this version includes tuning some database indexes, which might speed up reading data.

[tomhughes]

Well it's not going to magically tune any database indexes - you'll need to tell me what needs to be done.

That said, given that we're still trying to restabilise the caches, and that I'm about ready to do a full upgrade on the render servers, it may be best to leave this for a few weeks and deploy it as the render servers are updated, which will include a reimport with whatever indexes are needed.

[kocio-pl]

Sure - the code for all the indexes is here:

https://github.com/gravitystorm/openstreetmap-carto/blob/master/indexes.sql

Exact changes:

• planet_osm_polygon_way_area_z6 has different way_area limit
• planet_osm_polygon_way_area_z10 has been added

Do you need anything else?

[pnorman]

scripts/indexes.py can also generate the index statements in a form that builds them concurrently.

[pnorman]

I've had reports of serious performance problems on v4.12.0 with pre-rendering z0-z10 taking at least an order of magnitude longer, so I recommend not switching to v4.12.0 until that is looked at.

[kocio-pl]

Thanks, I hope we can review it before the servers are ready.

[tomhughes]

We now have confirmation from @woodpeck that at z10 average render times have jumped from 1.5s with 4.11.0 to 33.7s with the 4.12.0 stylesheet.

[kocio-pl]

Basic debugging questions - we need more detailed reports:

• is it related to Dropping subpixel accuracy for areas gravitystorm/openstreetmap-carto#2874 or not?
• if yes, what indexes were used - old or new ones?
• what about other zoom levels?

[woodpeck]

I didn't mean to run a thorough performance analysis, it was a side effect of something else I did. I did a "git checkout" last week when 9b7067e6a2734bf65b3c8bd3fc7bb78b6f892758 was current. I used the index creation script I was given by that checkout. I ran a planet-wide pre-rendering and while z0-9 performed as expected, z10 performance was abysmal. I later did a "git checkout v4.11" and everything works nicely again. It is possible that some change between 9b7067e6a2734bf65b3c8bd3fc7bb78b6f892758 and the release of 4.12 already fixed the issue; I wouldn't know.

[kocio-pl]

So, if z0-9 was OK, it hints me that maybe new shiny z10 index is bad? The index problem has been explained in detail by @pnorman here (with table and PostgreSQL index usage heuristics), so it might be good to review it, however that's not something I'm capable of:

gravitystorm/openstreetmap-carto#2874 (comment)

Another test might be to look at z0-z9 and z10 with the old indexes.

[woodpeck]

One query it tried to run frequently was this

explain SELECT ST_AsBinary("way") AS geom,"feature","int_surface","layernotnull","link" FROM (SELECT
            way,
            (CASE WHEN substr(feature, length(feature)-4, 5) = '_link' THEN substr(feature, 0, length(feature)-4) ELSE feature END) AS feature,
            horse,
            foot,
            bicycle,
            tracktype,
            int_surface,
            access,
            construction,
            service,
            link,
            layernotnull
          FROM (
            SELECT
                way,
                'highway_' || highway AS feature,
                horse,
                foot,
                bicycle,
                tracktype,
                CASE WHEN surface IN ('unpaved', 'compacted', 'dirt', 'earth', 'fine_gravel', 'grass', 'grass_paver', 'gravel', 'ground',
                                      'mud', 'pebblestone', 'salt', 'sand', 'woodchips', 'clay') THEN 'unpaved'
                  WHEN surface IN ('paved', 'asphalt', 'cobblestone', 'cobblestone:flattened', 'sett', 'concrete', 'concrete:lanes',
                                      'concrete:plates', 'paving_stones', 'metal', 'wood') THEN 'paved'
                  ELSE NULL
                END AS int_surface,
                CASE WHEN access IN ('destination') THEN 'destination'::text
                  WHEN access IN ('no', 'private') THEN 'no'::text
                  ELSE NULL
                END AS access,
                construction,
                CASE
                  WHEN service IN ('parking_aisle', 'drive-through', 'driveway') THEN 'INT-minor'::text
                  ELSE 'INT-normal'::text
                END AS service,
                CASE
                  WHEN substr(highway, length(highway)-4, 5) = '_link' THEN 'yes'
                  ELSE 'no'
                END AS link,
                COALESCE(layer,0) AS layernotnull,
                z_order
              FROM planet_osm_line
              WHERE bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct')
                AND highway IS NOT NULL
            UNION ALL
            SELECT
                ST_MakeEnvelope(-20037508.342789244, -20037508.342789244, 20037508.342789244, 20037508.342789244, 900913) AS way,
                'globalboundingbox' AS feature,
                NULL AS horse,
                NULL AS foot,
                NULL AS bicycle,
                NULL AS tracktype,
                NULL AS surface,
                NULL AS access,
                NULL AS construction,
                NULL AS service,
                NULL AS link,
                layernotnull,
                NULL AS z_order
              FROM
                (SELECT DISTINCT
                    layernotnull
                  FROM
                    (SELECT
                        COALESCE(layer,0) AS layernotnull
                      FROM planet_osm_line
                      WHERE bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct')
                        AND highway IS NOT NULL
                        AND surface IN ('unpaved', 'compacted', 'dirt', 'earth', 'fine_gravel', 'grass', 'grass_paver', 'gravel', 'ground',
                                            'mud', 'pebblestone', 'salt', 'sand', 'woodchips', 'clay')
                    ) AS roads
                ) AS layers_list
            UNION ALL
            SELECT
                way,
                'railway_' || (CASE WHEN railway = 'preserved' AND service IN ('spur', 'siding', 'yard') THEN 'INT-preserved-ssy'::text
                                 WHEN (railway = 'rail' AND service IN ('spur', 'siding', 'yard')) THEN 'INT-spur-siding-yard'
                                 WHEN (railway = 'tram' AND service IN ('spur', 'siding', 'yard')) THEN 'tram-service'
                                 ELSE railway END) AS feature,
                horse,
                foot,
                bicycle,
                tracktype,
                'null',
                CASE
                  WHEN access IN ('destination') THEN 'destination'::text
                  WHEN access IN ('no', 'private') THEN 'no'::text
                  ELSE NULL
                END AS access,
                construction,
                CASE WHEN service IN ('parking_aisle', 'drive-through', 'driveway') THEN 'INT-minor'::text ELSE 'INT-normal'::text END AS service,
                'no' AS link,
                COALESCE(layer,0) AS layernotnull,
                z_order
              FROM planet_osm_line
              WHERE bridge IN ('yes', 'boardwalk', 'cantilever', 'covered', 'low_water_crossing', 'movable', 'trestle', 'viaduct')
                AND railway IS NOT NULL
            ) AS features
          ORDER BY
            layernotnull,
            z_order,
            CASE WHEN substring(feature for 8) = 'railway_' THEN 2 ELSE 1 END,
            CASE WHEN feature IN ('railway_INT-preserved-ssy', 'railway_INT-spur-siding-yard', 'railway_tram-service') THEN 0 ELSE 1 END,
            CASE WHEN feature IN ('highway_road') THEN 0 ELSE 1 END,
            CASE WHEN feature IN ('highway_platform', 'railway_platform') THEN 0 ELSE 1 END,
            CASE WHEN access IN ('no', 'private') THEN 0 WHEN access IN ('destination') THEN 1 ELSE 2 END,
            CASE WHEN int_surface IN ('unpaved') THEN 0 ELSE 2 END
        ) AS bridges WHERE "way" && ST_SetSRID('BOX3D(7181411.681448881 3737464.93503198,7533633.507786971 4089686.761370071)'::box3d, 3857);

and when I EXPLAIN that, the following sticks out:

->  Subquery Scan on layers_list  (cost=12564051.13..12564051.15 rows=1 width=168)
                           ->  HashAggregate  (cost=12564051.13..12564051.14 rows=1 width=4)
                                 Group Key: COALESCE(planet_osm_line_1.layer, 0)
                                 ->  Seq Scan on planet_osm_line planet_osm_line_1  (cost=0.00..12563773.28 rows=111138 width=4)
                                       Filter: ((highway IS NOT NULL) AND (bridge = ANY ('{yes,boardwalk,cantilever,covered,low_water_crossing,movable,trestle,viaduct}'::text[])) AND (surface = ANY ('{unpaved,compacted,dirt,earth,fine_gravel,grass,grass_paver,gravel,ground,mud,pebblestone,salt,sand,woodchips,clay}'::text[])))

[kocio-pl]

So maybe this is rather a problem with gravitystorm/openstreetmap-carto#2640? I have not seen regression at z16+, but maybe this is a problem at lower zoom levels?

[pnorman]

We have gravitystorm/openstreetmap-carto#3280 to discuss the osm-carto issue, but I think the action is clear for OSMF servers - don't use v4.12.0, so this issue can be closed.