[OSM-dev] about Carto and SQL

[sav123]

1) if all the SQL requests required to produce a map are in the style file
or else if there are other SQL requests not if in this file ( and where they
are ).


>> Part of SQL is wrapping and table discovery logic in Mapnik's PostGIS 
>> driver, look for all text strings in 
>> https://github.com/mapnik/mapnik/blob/333ef9fde145f88339eaccba810305707bae9b0e/plugins/input/postgis/postgis_datasource.cpp 
>> starting from line 170, and nearby files.

I found the exact role of each added variable.

While the implicit Cartocss database is 20% bigger before min max scales 
redutions, it is faster.
There are many levels of optimization for a production database ( not for 
dev ) ...

More than half of the requests are made in subsets of size lower than 100 Mb 
( compared to the 90 Gb of the polygon table of osm2pgsql ) :
under 10 Mb : 25% , from 10 Mb to 100 Mb : 26% , 100 Mb to 1 Gb : 22% , 1 Gb 
to 10 Gb : 20% and over 10 Gb : 7%.

Having 75 tables ( and 1 view, 2 tables being identical ) allows to better 
dispatch data on the resources. And to get indexes faster.

For the postgres table partitioning, who can claim that a 50 Gb "building" 
table is difficult to geo-split in 4 to 10 parts and more ?
Or the 9 Gb "addresses" table ? There are also things to do with the 30 Gb 
roads_casing and the 20 Gb landcover.
Note that planet_osm_roads is already a subset of planet_osm_line.

It is useless to sort the new tables as specified in the queries but to keep 
the initial geom order of the source tables.
After geo cuts, sort is fast. Final adjustments...

Are also interesting :
- the intersections and inclusions relations between the subsets
- the (14) constant queries sequences sent by Mapnik.
- the balance of the tags never queried for rendering but perhaps useful for 
searches.

I'll come back in a few weeks with some scripts.
Thank you again ...