WFS query slow on large postgis table (and small table disappointing as well)

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
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?

Any help appreciated, MArco
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

Lime, Steve D (MNIT)
MapServer 7.0 should fix issues like this. A more general solution to the one presented in #4049 has been added to that version. You might try the development version and see how it goes.  I'm surprised simple queries aren't working well though since older versions of MapServer have optimizations specific to PostGIS for those simple cases.

Steve

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of deduikertjes
Sent: Monday, November 03, 2014 9:56 AM
To: [hidden email]
Subject: [mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)

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?

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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
Steve,

Thank you for the suggestion. Mapserver 7 is an interesting option
indeed. I cannot find a date when it is due. Do you have any knowledge
of that?

I'm not looking forward to build mapserver from sources. I had not so
good experiences with that in the past.
So I've been looking around in GitHub to find closed tickets which are
related to the way the backend is queried or performance is improved.
I couldn't find any. Could you please point me to the relevant ticket(s)?

Thank you, MArco




On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:

> MapServer 7.0 should fix issues like this. A more general solution to the one presented in #4049 has been added to that version. You might try the development version and see how it goes.  I'm surprised simple queries aren't working well though since older versions of MapServer have optimizations specific to PostGIS for those simple cases.
>
> Steve
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of deduikertjes
> Sent: Monday, November 03, 2014 9:56 AM
> To: [hidden email]
> Subject: [mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)
>
> 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?
>
> 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
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
In reply to this post by 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
>> [hidden email]
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

Even Rouault-2
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
> >> [hidden email]
> >> http://lists.osgeo.org/mailman/listinfo/mapserver-users

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
In reply to this post by Lime, Steve D (MNIT)
Hi Steve, List,

Hmm, I compiled mapserver from sources (GitHub Master). Actually that was not that bad, all tough I did not find out how to run the mapserver from ubuntu packages next to the one I compiled myself.

The new mapserver is working ... a bit.
The new mapserver doesn't support custom dimensions (yet ?) which I do use. So, for now I've removed that from the client side requests and getmap requests are fine .

Then do a simple query 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>

Resulting in an error:
msWFSGetFeature(): WFS server error. Invalid or Unsupported FILTER in GetFeature : <Filter ogc="http://www.opengis.net/ogc"> <Intersects> <PropertyName /> <Polygon gml="http://www.opengis.net/gml" srsName="EPSG:900913"> <exterior> <LinearRing> <posList>492858.14135005 6795805.518073499 492858.14135005 6796116.0435008 493197.33066301 6796116.0435008 493197.33066301 6795805.518073499 492858.14135005 6795805.518073499</posList> </LinearRing> </exterior> </Polygon> </Intersects> </Filter>
(Mapserver 6.4 accepts this query and returns a result (albeit taking long)).

A simple query like this works:
<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>

but that one was already reasonably fast.

Any pointers very much appreciated.

MArco



On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:
MapServer 7.0 should fix issues like this. A more general solution to the one presented in #4049 has been added to that version. You might try the development version and see how it goes.  I'm surprised simple queries aren't working well though since older versions of MapServer have optimizations specific to PostGIS for those simple cases. 

Steve

-----Original Message-----
From: [hidden email] [[hidden email]] On Behalf Of deduikertjes
Sent: Monday, November 03, 2014 9:56 AM
To: [hidden email]
Subject: [mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)

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?

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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users


_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

Even Rouault-2
Le mardi 04 novembre 2014 17:27:31, deduikertjes a écrit :

> Hi Steve, List,
>
> Hmm, I compiled mapserver from sources (GitHub Master). Actually that
> was not that bad, all tough I did not find out how to run the mapserver
> from ubuntu packages next to the one I compiled myself.
>
> The new mapserver is working ... a bit.
> The new mapserver doesn't support custom dimensions (yet ?) which I do
> use. So, for now I've removed that from the client side requests and
> getmap requests are fine .
>
> Then do a simple query 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>
>
>
> Resulting in an error:
> msWFSGetFeature(): WFS server error. Invalid or Unsupported FILTER in
> GetFeature : <Filter ogc="http://www.opengis.net/ogc"> <Intersects>
> <PropertyName /> <Polygon gml="http://www.opengis.net/gml"
> srsName="EPSG:900913"> <exterior> <LinearRing> <posList>492858.14135005
> 6795805.518073499 492858.14135005 6796116.0435008 493197.33066301
> 6796116.0435008 493197.33066301 6795805.518073499 492858.14135005
> 6795805.518073499</posList> </LinearRing> </exterior> </Polygon>
> </Intersects> </Filter>
> (Mapserver 6.4 accepts this query and returns a result (albeit taking
> long)).

The issue is the empty content in <PropertyName>. Setting it to anything non
empty should fix it. We are perhaps more sensitive to that now that in previous
versions.

>
> A simple query like this works:
> <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>
>
> but that one was already reasonably fast.
>
> Any pointers very much appreciated.
>
> MArco
>
> On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:
> > MapServer 7.0 should fix issues like this. A more general solution to the
> > one presented in #4049 has been added to that version. You might try the
> > development version and see how it goes.  I'm surprised simple queries
> > aren't working well though since older versions of MapServer have
> > optimizations specific to PostGIS for those simple cases.
> >
> > Steve
> >
> > -----Original Message-----
> > From: [hidden email]
> > [mailto:[hidden email]] On Behalf Of
> > deduikertjes Sent: Monday, November 03, 2014 9:56 AM
> > To: [hidden email]
> > Subject: [mapserver-users] WFS query slow on large postgis table (and
> > small table disappointing as well)
> >
> > 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?
> >
> > 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
> > [hidden email]
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
Even,

Thank you. i'll try to convince my geoext viewer to set a non empty PropertyName. Up till now that proves to be not so easy.

Any hints on the custom dimensions?

Marco

--


Op 4 nov. 2014 om 18:20 heeft Even Rouault <[hidden email]> het volgende geschreven:

> Le mardi 04 novembre 2014 17:27:31, deduikertjes a écrit :
>> Hi Steve, List,
>>
>> Hmm, I compiled mapserver from sources (GitHub Master). Actually that
>> was not that bad, all tough I did not find out how to run the mapserver
>> from ubuntu packages next to the one I compiled myself.
>>
>> The new mapserver is working ... a bit.
>> The new mapserver doesn't support custom dimensions (yet ?) which I do
>> use. So, for now I've removed that from the client side requests and
>> getmap requests are fine .
>>
>> Then do a simple query 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>
>>
>>
>> Resulting in an error:
>> msWFSGetFeature(): WFS server error. Invalid or Unsupported FILTER in
>> GetFeature : <Filter ogc="http://www.opengis.net/ogc"> <Intersects>
>> <PropertyName /> <Polygon gml="http://www.opengis.net/gml"
>> srsName="EPSG:900913"> <exterior> <LinearRing> <posList>492858.14135005
>> 6795805.518073499 492858.14135005 6796116.0435008 493197.33066301
>> 6796116.0435008 493197.33066301 6795805.518073499 492858.14135005
>> 6795805.518073499</posList> </LinearRing> </exterior> </Polygon>
>> </Intersects> </Filter>
>> (Mapserver 6.4 accepts this query and returns a result (albeit taking
>> long)).
>
> The issue is the empty content in <PropertyName>. Setting it to anything non
> empty should fix it. We are perhaps more sensitive to that now that in previous
> versions.
>
>>
>> A simple query like this works:
>> <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>
>>
>> but that one was already reasonably fast.
>>
>> Any pointers very much appreciated.
>>
>> MArco
>>
>> On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:
>>> MapServer 7.0 should fix issues like this. A more general solution to the
>>> one presented in #4049 has been added to that version. You might try the
>>> development version and see how it goes.  I'm surprised simple queries
>>> aren't working well though since older versions of MapServer have
>>> optimizations specific to PostGIS for those simple cases.
>>>
>>> Steve
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of
>>> deduikertjes Sent: Monday, November 03, 2014 9:56 AM
>>> To: [hidden email]
>>> Subject: [mapserver-users] WFS query slow on large postgis table (and
>>> small table disappointing as well)
>>>
>>> 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?
>>>
>>> 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
>>> [hidden email]
>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

Even Rouault-2
Le mardi 04 novembre 2014 20:39:51, mdprive a écrit :
> Even,
>
> Thank you. i'll try to convince my geoext viewer to set a non empty
> PropertyName. Up till now that proves to be not so easy.
>
> Any hints on the custom dimensions?

I'm not sure what you mean with "custom dimensions"

>
> Marco
>
> --
>
> Op 4 nov. 2014 om 18:20 heeft Even Rouault <[hidden email]> het
volgende geschreven:

> > Le mardi 04 novembre 2014 17:27:31, deduikertjes a écrit :
> >> Hi Steve, List,
> >>
> >> Hmm, I compiled mapserver from sources (GitHub Master). Actually that
> >> was not that bad, all tough I did not find out how to run the mapserver
> >> from ubuntu packages next to the one I compiled myself.
> >>
> >> The new mapserver is working ... a bit.
> >> The new mapserver doesn't support custom dimensions (yet ?) which I do
> >> use. So, for now I've removed that from the client side requests and
> >> getmap requests are fine .
> >>
> >> Then do a simple query 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>
> >>
> >> Resulting in an error:
> >> msWFSGetFeature(): WFS server error. Invalid or Unsupported FILTER in
> >> GetFeature : <Filter ogc="http://www.opengis.net/ogc"> <Intersects>
> >> <PropertyName /> <Polygon gml="http://www.opengis.net/gml"
> >> srsName="EPSG:900913"> <exterior> <LinearRing> <posList>492858.14135005
> >> 6795805.518073499 492858.14135005 6796116.0435008 493197.33066301
> >> 6796116.0435008 493197.33066301 6795805.518073499 492858.14135005
> >> 6795805.518073499</posList> </LinearRing> </exterior> </Polygon>
> >> </Intersects> </Filter>
> >> (Mapserver 6.4 accepts this query and returns a result (albeit taking
> >> long)).
> >
> > The issue is the empty content in <PropertyName>. Setting it to anything
> > non empty should fix it. We are perhaps more sensitive to that now that
> > in previous versions.
> >
> >> A simple query like this works:
> >> <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&versio
> >> n=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>
> >>
> >> but that one was already reasonably fast.
> >>
> >> Any pointers very much appreciated.
> >>
> >> MArco
> >>
> >> On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:
> >>> MapServer 7.0 should fix issues like this. A more general solution to
> >>> the one presented in #4049 has been added to that version. You might
> >>> try the development version and see how it goes.  I'm surprised simple
> >>> queries aren't working well though since older versions of MapServer
> >>> have optimizations specific to PostGIS for those simple cases.
> >>>
> >>> Steve
> >>>
> >>> -----Original Message-----
> >>> From: [hidden email]
> >>> [mailto:[hidden email]] On Behalf Of
> >>> deduikertjes Sent: Monday, November 03, 2014 9:56 AM
> >>> To: [hidden email]
> >>> Subject: [mapserver-users] WFS query slow on large postgis table (and
> >>> small table disappointing as well)
> >>>
> >>> 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?
> >>>
> >>> Any help appreciated, MArco
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context:
> >>> http://osgeo-org.1560.x6.nabble.com/WFS-query-slow-on-large-postgis-tab
> >>> l e-and-small-table-disappointing-as-well-tp5170988.html Sent from the
> >>> Mapserver - User mailing list archive at Nabble.com.
> >>> _______________________________________________
> >>> mapserver-users mailing list
> >>> [hidden email]
> >>> http://lists.osgeo.org/mailman/listinfo/mapserver-users

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
Even,

I use functionality as described on:
http://mapserver.org/ogc/wms_dimension.html
I referred to it by 'custom dimensions' to distinguish from wms time and
elevation dimensions.

MArco



On 04-11-14 20:55, Even Rouault wrote:
> Le mardi 04 novembre 2014 20:39:51, mdprive a écrit :
>> Even,
>>
>> Thank you. i'll try to convince my geoext viewer to set a non empty
>> PropertyName. Up till now that proves to be not so easy.
>>
>> Any hints on the custom dimensions?
> I'm not sure what you mean with "custom dimensions"
>

_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes
Ok, with all the help I came to the following results:

Compile the current development version (6.5-dev) from GitHub & Install.
Results:

    Simple Spatial WFS queries require a  non-empty  <ogc:PropertyName></ogc:PropertyName>.    
    Setting it too <ogc:PropertyName>foo</ogc:PropertyName> proved to be sufficient.
    Query speed is indeed very fast now.

    Using WMS-dimensions as described on http://mapserver.org/ogc/wms_dimension.html does work in
    6.4 but not in 6.5-dev. As I have need for them I recompiled mapserver with a
    -DCMAKE_INSTALL_PREFIX=/usr/local/bin switch so I can run mapserver 6.4 en 6.5-dev next to each
    other. GetMap requests are routed to mapserver 6.4 and GetFeature requests are routed to 6.5-dev.

    Using the WMS-dimensons on 6.5-dev yields the following error:

<ServiceExceptionReport version="1.1.1"><ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named 'jan2014_low'.
msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:  operator does not exist: >= numeric
LINE 1: ...32.505600397 6790506.21879132))',900913) and (((( >= 0.90000...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 
</ServiceException></ServiceExceptionReport>

Its seems that the dimension parameter is not inserted in the layer filter expression.
Should I create a bug report for this?

MArco
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

tbonfort
Yes Marco, Please open a ticket for this.

thanks,
thomas

On 7 November 2014 09:34, deduikertjes <[hidden email]> wrote:

> Ok, with all the help I came to the following results:
>
> Compile the current development version (6.5-dev) from GitHub & Install.
> Results:
>
>     Simple Spatial WFS queries require a  non-empty
> <ogc:PropertyName></ogc:PropertyName>.
>     Setting it too <ogc:PropertyName>foo</ogc:PropertyName> proved to be
> sufficient.
>     Query speed is indeed very fast now.
>
>     Using WMS-dimensions as described on
> http://mapserver.org/ogc/wms_dimension.html does work in
>     6.4 but not in 6.5-dev. As I have need for them I recompiled mapserver
> with a
>     -DCMAKE_INSTALL_PREFIX=/usr/local/bin switch so I can run mapserver 6.4
> en 6.5-dev next to each
>     other. GetMap requests are routed to mapserver 6.4 and GetFeature
> requests are routed to 6.5-dev.
>
>     Using the WMS-dimensons on 6.5-dev yields the following error:
>
> <ServiceExceptionReport version="1.1.1"><ServiceException>
> msDrawMap(): Image handling error. Failed to draw layer named 'jan2014_low'.
> msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:
> operator does not exist: >= numeric
> LINE 1: ...32.505600397 6790506.21879132))',900913) and (((( >= 0.90000...
>                                                              ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> </ServiceException></ServiceExceptionReport>
>
> Its seems that the dimension parameter is not inserted in the layer filter
> expression.
> Should I create a bug report for this?
>
> 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-tp5170988p5171806.html
> Sent from the Mapserver - User mailing list archive at Nabble.com.
> _______________________________________________
> mapserver-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

deduikertjes


Done, opened ticket: https://github.com/mapserver/mapserver/issues/5032

thomas bonfort wrote
Yes Marco, Please open a ticket for this.

thanks,
thomas
Reply | Threaded
Open this post in threaded view
|

Re: WFS query slow on large postgis table (and small table disappointing as well)

Martin Icking
In reply to this post by Lime, Steve D (MNIT)
Hi Steve,
Is this resolved in MapServer 7.0 for Oracle, too? Or just for PostGIS?
Tx
Martin