Update: Further investigation revealed that my GiST indexes werenâ€™t built correctly and so i have since updated the timings on the postgis results. Thanks to Sean for pointing out my boo boo.
I have noticed a bit of interest especially on PostGISâ€˜ new implementation of a GiST spatial index to speed up its performance. What i think is lacking is that there is little to no documentation on how â€œmuchâ€ faster spatial indexing (not to be confused with attribute indexes) performs.
So here we go, a simple benchmark in less than 15 mins
First up, my datasets. For the base data, i will use the roads dataset from my previous routing article.
The benchmark tests in this case will be a simple, arbitrary bbox on the following datasets interfacing with Mapserver 4.6 (win32).
Word of warning: Please dont take these results as gospel, its merely to highlight the performance differences across the board.
- All data is in CRS 4326 (WGS84)
- The timings will be extracted using the debug output from mapserver
- Note the number of features in each query, the first obviously not being very realistic
- A WMS requests are for single layers only with the BBOX values below
- 115.69402,-32.1273,116.09642,-31.86770 Features: 57564
- 115.82508,-32.0358,115.93653,-31.97567 Features: 8408
- 115.81400,-32.0493,115.86604,-32.02131 Features: 1389
- 115.82171,-32.0405,115.84112,-32.03009 Features: 338
|Shapefile (no index)||Shapefile (qix index^)||PostGIS 8.1 (no index)||PostGIS 8.1 (GiST index)*|
- ^ Created with the standard quadtree sizes determine by the shptree utility
- * Created with â€œCREATE INDEX roadsindex ON roadsindex USING GIST( the_geom GIST_GEOMETRY_OPS );â€ as per postgis documentation
I think the table speaks for itself, but im a little bit cautious about drawing too many conclusions. Most people are aware that PostGIS is slower than shapefiles, but thats a given. Most users who use PostGIS are using it for other reasons (such as for its geoprocessing functions).
The PostGIS guys have claimed about a 10% performance loss over shapefiles. In this little test it was indeed more than this, but i have no doubts that in the hands of a more experienced postGIS expert that they could certainly narrow down the gap further.
Fact of the matter is, spatial indexing is an important part of performance optimisation but should be considered with other methods such as view scale limiting, simplified symbolisation and feature generalisation. Unfortunately i dont have access to a mapserver hooked up with ArcSDE 9 or Oracle Spatial â€¦ could of made things interesting
Please, i make no attempts at claiming i am the master of all that is postgis and mapserver. If there is something glaringly obvious that i did not configure or did not include, please let me know and i will be happy to update the results.