[OSM-dev] osm2pgsql hstore support

[Peter Körner]

> Very cool this. What's the speed to access the hstore column, compared 
> to the generic columns we now have?

I did some benchmarks and the answer is - sometimes :)

Without any indexes using a seqscan hstore is slower (see 
hstore-vs-columns-noindex.txt)

hstore does not support (at least the docs does not explicitly name) 
equality-indexes but they can handle has-key indexes using GIN
or GiST [1]. You can see my results in hstore-vs-columns-index.txt:

A "not .. is null" or "(tags ? '...')" query is faster on the hstore 
table because it's using the index.

The ".. = '..'" or "(tags->'..'= '..')" query is faster on the btree 
index of the regular column.

All tests have been made on a berlin extract and thus on a relatively 
small dataset. The size of the index is also interesting - the 
hstore-index (on all possible tags!) is only 4.5 times larger then the 
index on the amenity column (only one tag):

hstore-rendering=> SELECT relname, reltuples, pg_size_pretty(relpages * 
8 * 1024) as size FROM pg_class ORDER BY relname;

  berlin_osm_dual_point_amenity     |       50284 | 1192 kB
  berlin_osm_dual_point_tags        |       50284 | 5440 kB




In a conclusion I'd say that the performance of the hstore is good 
enough for rendering, when a lot of the queries are "(tags ? '...')" 
queries and the filtering is later done by mapnik. This is ok for 
single-machine setups but may fail if the db-server and the rendering 
server(s) are located on different machines, as more data needs to be 
transferred via LAN (Florian may be able to tell sth. about this).

Where it perfectly fit's in, is the rendering of special maps like the 
translates maps of wikimedia or a overlay-map of all surveillance 
cameras or such things.

Thanks to Sven Geggus, who developed and checked in this patch for 
osm2pgsql.

Peter



[1] <http://www.postgresql.org/docs/current/static/hstore.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: hstore-vs-columns-noindex.txt
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20100316/6efdbab5/attachment.txt>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: hstore-vs-columns-index.txt
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20100316/6efdbab5/attachment-0001.txt>