[OSM-dev] Spatial vs. multi-column indexes for points

[Florian Lohoff]

On Thu, Sep 11, 2008 at 01:49:55PM +0200, Andreas Kalsch wrote:
> Subject: [OSM-dev] Spatial vs. multi-column indexes for points
> 
> Hey,
> 
> last week I made some experiments with huge datasets of lat/lon
> points. I use MySQL 5.0, which partially support GIS extensions,
> including R-trees. But it is still not able to make queries based
> on the GIS features, so I have to use the normal way - multi-column
> indexes on lat/lon columns. It works well but probably there is a
> way to make it even quicker ;)
> 
> Has anybody used GIS successfully in MySQL or PGSQL and can tell me
> how the performance compares between the two techniques?

I have 2 dbs - one mysql with the main api schema that means the
quadtile approach and the index on that column, and a postgres with
a modified simple schema from osmosis which basically matches the same
tables and organisation as the mysql. Both contain the minutely updated
planet. The postgres uses ~163G the mysql ~196G.

Both run on the very same hardware (Fujitsu Siemens RX/300, Dual Xeon
3.2Ghz 4GB Ram, 4x73GB Disk, Raid0 for the db).

Here are some numbers - to be honest - a part of the time is used in
the perl cgi to convert the database output. In case of the mysql there
is even more done in the perl code to not let the database shuffle the
data together as i dont trust mysql on transactions, temp tables and
stuff.

Its tried via the map api call on the mentioned bbox - The bbox contains

	nodes:145100 ways:13695 relations:399

Postgres 8.3, postgis 1.3.3, kernel 2.6.26, Debian/Lenny

	time wget -q -O pgsql http://tiles-two.lab.rfc822.org/api/0.5/map?bbox=2.768555,42.671457,2.944336,42.757990

	real    1m26.179s
	user    0m0.156s
	sys     0m0.940s

	ls -la pgsql 
	-rw-r--r-- 1 flo mways 38277716 2008-09-11 16:53 pgsql

mysql 5.0.32-7etch5, kernel 2.6.18, Debian/Sarge

	time wget -q -O mysql http://tiles-one.lab.rfc822.org/api/0.5/map?bbox=2.768555,42.671457,2.944336,42.757990

	real    2m9.143s
	user    0m0.068s
	sys     0m0.456s
	ls -la mysql 
	-rw-r--r-- 1 flo mways 30658375 2008-09-11 16:28 mysql

The postgres export is larger by 8MByte as it exports usernames and
timestamps which the mysql variant does not do ... The mysql queries are
much simpler than the postgres ones. They dont contain subselects,
temporary tables or unions as the postgres ones do.

My conclusion is that postgres is faster. I dont think these numbers are
easily comparable as the method is different but these can be used as a
hint.

To see the cgi scripts look here:
Mysql: http://tiles-one.lab.rfc822.org/~flo/map.cgi
pgsql: http://tiles-two.lab.rfc822.org/~flo/map-pgsql.cgi

The pgsql is much simpler as i shifted most of the work of selection,
exclusion and merging into the database temp tables.

Flo
-- 
Florian Lohoff                  flo at rfc822.org             +49-171-2280134
	Those who would give up a little freedom to get a little 
          security shall soon have neither - Benjamin Franklin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20080911/f283a775/attachment.pgp>