[mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)

[Even Rouault]

Le mardi 04 novembre 2014 12:32:53, deduikertjes a écrit :
> Even,
> 
> Thank you.
> 
> Examples of slow and fast queries:

Marco,

OK, that explains it. Up to now only <ogc:BBOX> (and without the presence of 
any other spatial operator in the filter) used PostGIS spatial filtering. 
Operators like Intersects were indeed evaluated once the feature has been 
retrieved from the server. As said by Steve, MapServer 7.0 should fix this by 
translating the <ogc:Intersects> into a PostGIS ST_Intersects. In the 
meantime, a workaround would be to transform the <ogc:Intersects> into a BBOX 
filter and do the intersection side client side.

Even

> 
> Slow queries (not very complex) like:
> 
> <wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS"
> version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs
> http://schemas.opengis.net/wfs/1.1.0/wfs.xsd"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <wfs:Query typeName="hb:jan2014_high" srsName="EPSG:900913"
> xmlns:hb="http://maps.md2.com/schemas/hb_wfs.xsd">
> <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
> <ogc:Intersects>
> <ogc:PropertyName></ogc:PropertyName>
> <gml:Polygon xmlns:gml="http://www.opengis.net/gml" srsName="EPSG:900913">
> <gml:exterior>
> <gml:LinearRing>
> <gml:posList>493014.59839229 6795936.8942158 493014.59839229
> 6796022.885872601 493102.97870623 6796022.885872601 493102.97870623
> 6795936.8942158 493014.59839229 6795936.8942158</gml:posList>
> </gml:LinearRing>
> </gml:exterior>
> </gml:Polygon>
> </ogc:Intersects>
> </ogc:Filter>
> </wfs:Query>
> </wfs:GetFeature>
> 
> fast queries (only with small bbox) like:
> 
> <wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS"
> version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs
> http://schemas.opengis.net/wfs/1.1.0/wfs.xsd
> http://maps.md2.com/schemas/hb_wfs.xsd
> http://xxxxx/maps/xxxx/rc1/services/basic/default.map?service=WFS&version=1
> .1.0&request=DescribeFeatureType&typeName=jan2014_high"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <wfs:Query typeName="feature:jan2014_high" srsName="EPSG:900913">
> <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
> <ogc:And>
> <ogc:BBOX>
> <ogc:PropertyName>msGeometry</ogc:PropertyName>
> <gml:Envelope xmlns:gml="http://www.opengis.net/gml" srsName="EPSG:900913">
> <gml:lowerCorner>491667.6470239 6795223.3576443</gml:lowerCorner>
> <gml:upperCorner>494192.4576141 6796212.2616977</gml:upperCorner>
> </gml:Envelope>
> </ogc:BBOX>
> <ogc:PropertyIsLessThan>
> <ogc:PropertyName>value</ogc:PropertyName>
> <ogc:Literal>0</ogc:Literal>
> </ogc:PropertyIsLessThan>
> </ogc:And>
> </ogc:Filter>
> </wfs:Query>
> </wfs:GetFeature>
> 
> Marco
> 
> On 04-11-14 10:41, Even Rouault wrote:
> > Le lundi 03 novembre 2014 16:56:12, vous avez écrit :
> >> Hi List,
> >> 
> >> I'm experiencing real slow performance when doing WFS queries on a
> >> postgis table with about 1500000 records.
> >> 
> >> A simple spatial query (which features in a box) takes about 1 to 2
> >> minutes. In postgis the same query takes less than a second. Same story
> >> for attribute queries.
> >> Indeed on a postgis table with some 60000 records things are still very
> >> very slow.
> >> 
> >> WMS on the other hand is flying taking less than a second for rendering
> >> most tiles.
> >> 
> >> By using the debug functions I see that mapserver fetches all features
> >> in the map extent (set at map level) and then starts filtering feature
> >> by feature which takes a long time.
> >> 
> >> I found ticket http://trac.osgeo.org/mapserver/ticket/4049 which seems
> >> to be related.
> >> 
> >> Is there any way to speed up this kind of WFS queries? Is there any
> >> progress on the ticket?
> > 
> > Can you show the exact WFS request you send to mapserver and that is slow
> > to run ? BBOX requests should use postgis spatial filtering. Perhaps you
> > use something more complex ?
> > 
> >> Any help appreciated, MArco
> >> 
> >> 
> >> 
> >> --
> >> View this message in context:
> >> http://osgeo-org.1560.x6.nabble.com/WFS-query-slow-on-large-postgis-tabl
> >> e- and-small-table-disappointing-as-well-tp5170988.html Sent from the
> >> Mapserver - User mailing list archive at Nabble.com.
> >> _______________________________________________
> >> mapserver-users mailing list
> >> mapserver-users at lists.osgeo.org
> >> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com