Re: [mapserver-users] PostGIS date queries performance issues
Steve: info can be found at https://gist.github.com/tomkralidis/006594382d6339c2047b441108a3991b > -Original Message- > From: Lime, Steve D (MNIT) [mailto:steve.l...@state.mn.us] > Sent: 18 November 2016 13:10 > To: Kralidis, Tom (EC); mapserver-users@lists.osgeo.org > Subject: RE: PostGIS date queries performance issues > > Tom: There were definitely changes in this area. Can you share the WFS filter > plus the resulting SQL in each version. This is the type of thing that should > have > gotten better in 7.0 as WFS filters are translated more completely into native > SQL. It’s possible I introduced an inefficiency in that translation beyond > what > was done for PostGIS in mapogcfilter.c in 6.4. -Steve > > -Original Message- > From: mapserver-users [mailto:mapserver-users-boun...@lists.osgeo.org] On > Behalf Of Kralidis, Tom (EC) > Sent: Thursday, November 17, 2016 8:14 PM > To: mapserver-users@lists.osgeo.org > Subject: [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@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] PostGIS date queries performance issues
Tom: There were definitely changes in this area. Can you share the WFS filter plus the resulting SQL in each version. This is the type of thing that should have gotten better in 7.0 as WFS filters are translated more completely into native SQL. It’s possible I introduced an inefficiency in that translation beyond what was done for PostGIS in mapogcfilter.c in 6.4. -Steve -Original Message- From: mapserver-users [mailto:mapserver-users-boun...@lists.osgeo.org] On Behalf Of Kralidis, Tom (EC) Sent: Thursday, November 17, 2016 8:14 PM To: mapserver-users@lists.osgeo.org Subject: [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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] PostGIS date queries performance issues
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-boun...@lists.osgeo.org] Im > Auftrag von Kralidis, Tom (EC) > Gesendet: Freitag, 18. November 2016 03:14 > An: mapserver-users@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@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/mapserver-users ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
