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

[deduikertjes]

Even,

Thank you.

Examples of slow and fast queries:

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-table-
>> 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