[OSM-talk] osm2pgsql hstore (was: Wind turbines no longer rendered on mapnik layer)

[Jochen Topf]

On Fri, Feb 17, 2012 at 02:02:26PM +0000, Jukka Rahkonen wrote:
> Jochen Topf <jochen <at> remote.org> writes:
> 
> 
> > 
> > Much too slow. Requiring joins on every query is not a good idea. And having
> > the right indexes is important. You can't just index everything and hope it
> > would do the right thing. (The most important index btw is the geometry index
> > not the attribute indexes, although having the right attribute indexes here
> > or there can help.)
>  
> I sent by followup accidentally to osm-dev list. The main message was that
> perhaps there is a need to do some tests before saying if hstore or joined
> tables are faster.
> 
> Here is a Spatialite database for testing
> 
> http://latuviitta.org/documents/relation_test.zip
> 
> First trials suggest that joined tables with proper indexes are not at all
> slow and compared with the standard tables created by osm2pgsql the query
> times can be several times faster. This is not a fair comparison because
> osm2pgsql tables are missing the attribute indexes but so they normally do
> in the Mapnik chain. Query times vary, so try yourself. Data is osm_lines
> from Berlin from some months ago.

Sorry, but that kind of test doesn't really tell you anything about real
performance on a real system. Do this test with data for the whole planet with
one process updating the database and multiple readers accessing the db at the
same time. And results you get with sqlite do not tell you anything about
results for postgresql.

> By the way, recent development around GDAL suggests that Spatialite may be
> faster with spatial and attribute queries than PostGIS. Improvements in GDAL
> Spatialite driver made Mapserver about 20 times faster than it used to be and
> now Spatialite is faster that PostGIS and even shapefiles.
> 
> 
> query from osm_line from osm2pgsql
> =======================
> select geometry, highway from osm_line
> where highway='primary'
> 0.531 seconds
> 
> query from joined tables
> ===============================
> select * from line_geometry, tags
> where tags.tag='highway' and tags.value='primary'
> and tags.join_id=line_geometry.osm_id
> 0.059 seconds

These numbers are also irrelevant for our discussion here because Mapnik
doesn't do this kinds of queries. Mapnik always queries with a bounding box.

Jochen
-- 
Jochen Topf  jochen at remote.org  http://www.remote.org/jochen/  +49-721-388298