[mapserver-users] PostGIS date queries performance issues

[Eichner, Andreas - SID]

Besides comparing the execution plans for both query versions, you could try:
1. omit the outer ORDER BY clause (not present in MS6 and the inner one is not relevant for the result, so might be optimized away by the planner)
2. try the MS7 SQL without the superfluous st_intersects() part (it might not use the spatial index in the same way as the "&&" operator)
3. replace the time comparision part with the MS6 BETWEEN construct
4. try all at once and/or combinations of them

At least one should help you identify the cause. If you've found it post it here so that one could search the generating code.

HTH

> -----Ursprüngliche Nachricht-----
> Von: mapserver-users [mailto:mapserver-users-bounces at lists.osgeo.org] Im
> Auftrag von Kralidis, Tom (EC)
> Gesendet: Freitag, 18. November 2016 03:14
> An: mapserver-users at lists.osgeo.org
> Betreff: [mapserver-users] PostGIS date queries performance issues
> 
> Hi all: using 7.0.2 against a PostgreSQL 9.3/PostGIS 2.2 instance we're
> getting very long (timeouts even) response times when doing WFS filters
> against PostgreSQL date types.
> 
> I've posted a Gist at [1] to help in reporting.  Note that this
> functionality used to work with acceptable performance in 6.4.x.
> 
> Looking deeper (see Gist) it appears that the SQL queries that MapServer
> crafts for PostgreSQL are different.  When I try the 7.0.2 based SQL in a
> psql prompt, the same very long response time results.
> 
> Any idea what could be going on here?
> 
> Thanks
> 
> ..Tom
> 
> [1] https://gist.github.com/tomkralidis/006594382d6339c2047b441108a3991b
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users