[postgis-devel] Have you seen this article

[Paul Ramsey]

I had some thoughts about it this morning: 

- One very odd finding was the overall size finding. PostGIS 10x larger than mySQL. There's one of two things going on here: he's just made a mistake in reporting one of the other sizes; the GIST index has way more overhead than a purpose-built spatial index might have in terms of space. Although, actually, he got the same 10x result for both the polygon and point layer, which argues against the second point, so that leaves me with wondering if he just made an error.

- The write-up was strange, in that it on the one hand noted the PostGIS linear scaling line as load went up and didn't note that MySQL started to choke under multiuser load, and concluded with "MySQL is faster", without adding "under a single user load scenario"

- The actual test was a bunch of singular point-in-polygon queries. We get no leverage from caching here, because each call just does a single point/polygon pairing. My ancient mysql-vs-postgis benchmark also showed mysql going faster (though only somewhat, not 5x) on simple single-statement queries (bbox queries in my case). The actual code path for the point/polygon test doesn't call into GEOS (good) and doesn't populate a cache until the second call (good) so there's no obvious postgis-level inefficiency on this workload

- The whole database is "in memory" he says. That leaves one item which could be problematic, particularly for bigger polygon geometries: external storage. We set our storage type such that geometries get compressed when they get too big, which means it's possible the system is spending time unzipping geometries before evaluating the call.

- Regina did ask if he put on indexes, hard to imagine he didn't.

- As mentioned, there's not quite enough info here to make a solid diagnosis or critique.

P. 

-- 
Paul Ramsey
http://cleverelephant.ca
http://postgis.net


On Thursday, July 18, 2013 at 11:32 AM, Paragon Corporation wrote:

> http://openlife.cc/blogs/2013/april/slides-spatial-functions-mysql-56-mariad
> b-55-postgis-20-and-others-percona-live
> 
> Claims PostGIS is the worst performa and MySQL is the best. Admittedly I
> haven't tried the new MySQL so perhaps they've finally caught up.
> 
> Thanks,
> Regina