Index for high_road SQL?

[RadekHavelka]

Hi, I use your toner style mixed into the openstreetmap-carto docker, with good success, but I realised I'm missing some indexes, as the function high_road calls are running very slow (on zoom 10 it may take minutes, sometimes more to finish the query). EXPLAIN command is not usefull at all in this case, as it is a function.

Can you help recommend what kind of indexes on tables might help to speed up these calls? Thank you

gis=# EXPLAIN SELECT ST_AsTWKB(ST_Simplify(ST_RemoveRepeatedPoints("way",10),10,true),-1) AS geom,"is_link","kind" FROM (SELECT * FROM high_road(1.09196e+06, ST_SetSRID('BOX3D(-4422340.708469531 -1917652.16561953, 3717897.055792969 -1213208.5129 42969)'::box3d, 3857), 'kind!=''highway''')) AS _;
                              QUERY PLAN
----------------------------------------------------------------------
 Function Scan on high_road  (cost=0.25..30010.25 rows=1000 width=96)

[Nakaner]

The query uses the function highroad_z10 which queries planet_osm_line using the following WHERE condition: highway IN ('motorway', 'trunk', 'primary', 'secondary', 'tertiary'). Therefore I would suggest to add the following index:

CREATE INDEX planet_osm_line_highroad_z10 ON planet_osm_line USING gist(way) WHERE highway IN ('motorway', 'trunk', 'primary', 'secondary', 'tertiary');

Does this help?

To be honest, when I modified the Toner style to be useable with an Osm2pgsql database, I just tried to get it working. Performance was not critical. If you want to help, I suggest to replace the usage of to the high_road function by direct queries to the views highroad_z10, highroad_z11 etc. (i.e. I welcome such a pull request). Once this function in front of a view is removed, EXPLAIN will return useful information as you expected to see.

[RadekHavelka]

Thanks for quick reply and for the work you've done!

Will try, but Postg is not my preferred database, I worked with MySQL so I am trying to catch up and learn this new environment.

I am not sure what "normal" speed of generating tiles should be, I see some taking 0,2 seconds, I guess thats tiles from oceans, and some taking up to 10 minutes (on zoom 10), tiles are generated 8x8 at a time

renderd[139]: DEBUG: Sending render cmd(3 ajt 10/552/352) with protocol version 2 to fd 6
renderd[139]: DEBUG: Got incoming request with protocol version 2
renderd[139]: DEBUG: Got command RenderBulk fd(6) xml(ajt), z(10), x(552), y(368), mime(image/png), options()
renderd[139]: DEBUG: START TILE ajt 10 552-559 368-375, new metatile
renderd[139]: Rendering projected coordinates 10 552 368 -> 1565430.339281|5322463.153556 1878516.407137|5635549.221413 to a 8 x 8 tile
renderd[139]: DEBUG: DONE TILE ajt 10 552-559 360-367 in 579.555 seconds
debug: Creating and writing a metatile to /var/lib/mod_tile/ajt/10/0/0/33/38/136.meta

the hardware should be sufficient, the db is on array of disks, indexes on PCI SSD card, 2xcpu each 12 threads, 128Gb RAM

[Nakaner]

It becomes a bit off-topic here but I will answer it nevertheless.

Tiles up to zoom level 12 are usually pre-rendered at setup. Therefore, their rendering performance is not that important. Performance is important at zoom level >= 13 when they are rendered live while the client is waiting for their request to be answered if the tile is not available in the cache.

Ten minutes is a bit long at zoom level 10 but you will have difficulties to get it below one minute.

the hardware should be sufficient, the db is on array of disks, indexes on PCI SSD card, 2xcpu each 12 threads, 128Gb RAM

Mind that the whole database should be on SSD/NVMe storage. Just having parts (tablespace or indexes only) on fast storage is not sufficient. I suggest to mount /var/lib/postgresql (or wherever your distribution's database directory is) from fast storage (or symlink it).

[RadekHavelka]

the index you suggested did indeed help! the time for that above mentioned explain fell down from

Execution Time: 172915.863 ms to Execution Time: 7009.486 ms

which makes quite a difference :) I will add another index for the z11 as there are more values in the highway filter and probably one more for z15+ as there is also a railway in it

I'm aware for the need of SSD for the whole postgres DB, but I simply dont have so much space, the whole planet import took more than I expected :( so I created tablespaces for the indexes at least, and will add more SSDs to the server in the future to move the db there.

Thank you for the reference times, it is hard to get the idea what is "normal" when doing this for the first time :)