WFS on PostGIS: poor performance

[Arnulf Christl]

Bart van den Eijnden wrote:
> I know Arnulf was having troubles with WFS performance on Oracle Spatial recently, I was tesing PostGIS performance using Mapserver WFS since performance on file-based data was getting too slow, and I figured that would be caused mainly by the lack of attribute indexes. So I switched to PostGIS hoping for a huge increase in performance.

Hi Bart,
we have been told that this is not true. But we are still pretty sure 
that PotstGIS can be a lot faster than shapefiles, as most always it 
depends on too many parameters to generalize.

> But the improvement of using PostGIS is still not enough for our performance demands (I created all indexes). There are a few things I can do as a WFS client to get better performance, like not sending a condition like nummer like * when the user does not fill in anything for that condition, but that's about all I can do in optimization.
> 
> One thing I noticed is that:
> 
> * the geometry is collected twice, first of all with the first cursor which returns all records (mycursor), and then with every cursor for every fid (mycursor2). Is this really necessary?

No it is not, and this bug is still open. Shame on me, we wanted to 
report from experiences accessing Oracle but we never did, because the 
project was postponed for other reasons. Now we are at it with GeoServer.

> My conclusion so far is, that Mapserver WFS cannot be used with large datasets unfortunately. I hope to be proven wrong though.

When requesting simple GML for a set bounding box MS is fast as ever.

But as soon as you do *not* add a bbox filter it slows to a crawl.

We switched to GeoServer for a different eason though. MS sometimes 
returns dirty GML, we are still verifying why - again this might be a 
data problem. Sometimes features with empty geometries were returned 
although the geometry looks fine in Oracle.

> Are there any plans left to do something about the Mapserver query mechanism, so that it will work more efficient with database sources?
> 
> Arnulf, did you get better performance out of Geoserver, or how did you solve your problems?

Still solving... GeoServer performance is still somewhat disappointing 
but we are still tuning. We will let you know asap.

Best, Arnulf.

> Btw I am using latest CVS.
> 
> Best regards,
> Bart
> 
> 
> Bart van den Eijnden
> Syncera IT Solutions
> Postbus 270
> 2600 AG  DELFT
> 
> tel.nr.: 015-7512436
> email: BEN at Syncera-ITSolutions.nl