Geoserver OSM data Imported via osm2pgsql - most efficient tables to use to style roads

Question

The Osm2pgsql importer for OSM data to Postgres results in two tables for ways, one called planet_osm_roads and a second one called planet_osm_lines

The Osm2pgsql documentation states that the planet_osm_roads is a subset of planet_osm_lines and is useful to display roads a zoomed out levels to reduce the postgres server workload on lookups on the much larger planet_osm_lines

There are two approaches to styling the roads using these two tables:

1. Use planet_osm_roads until a certain zoom level and have the SLD stop styling at that sale, say a minimum of 250K then start styling from a maximum scale 250K down using planet_osm_lines
2. Use planet_osm_roads for styling Motorway, Trunk, Primary and Secondary Roads down to the minimum scale and use planet_osm_lines to style all the other roads except for Motorway, Trunk, Primary and Secondary starting from a maxscale of 250K

(Note: Both layers presented to the end user via a layer group)

The first option requires accessing planet_osm_roads THEN planet_osm_lines The second option requires accessing both tables all the time

My Question is:

Which is the most effective approach assuming that no data can be removed from the tables because of the minutely diff updates and considering the following two objectives:

• Efficiency in interpreting SLD in Geoserver
• Efficiency in table geometry lookups on the postgres server

Answer 1

I fear it is impossible to give an exact answer without testing in a real environment with your own SLD styles and hardware. However, here comes my educated guess. Some background first. If information is wrong then someone will correct me:

1. osm_roads is much smaller table than osm_lines
2. osm2pgsql creates only spatial indexes on geometries but no ordinary filters on attributes like "highway", "bridge", "tunnel" etc. which are still heavily used in styling
3. it is poorly documented which OSM features go to osm_roads table. Ok, it is deocumented in the source code https://github.com/openstreetmap/osm2pgsql/blob/master/tagtransform.c but it did not help me to understand is all motorway, trunk, primary and secondary roads are inserted there for sure

Because of 1) and 2) it should be faster to select the OSM features which are needed for rendering from the osm_roads table. This is true especially at small scale when the spatial index is not very selective. In addition, without making some comparisons in the database I would not trust that osm_roads contains all the roads even in the highest highway classes (motorway, trunk...). For making rendering fast it might make sense to drop out short line segments from this table but I could not read from the code if osm2pgsql is doing this optimization. Anyway, I would select to do the same than rendering with Mapnik does and go with your alternative 1.