[mapserver-users] Strange performance problem in mapserver

[Carlos Ruiz]

OK, 
Make sure to add ORDER BY [DESC] to the Mapserver subquery to be sure that both queries have the same arguments (750) and to always get the same result.
(SELECT * FROM roronline.settings ORDER BY notice_radius DESC LIMIT 1)Mapserver also does the encode of geometry, that is why it takes longer than executing just the buffer. Now, if you want to know exactly why one query is faster than the other, use EXPLAIN ANALYSE and compare both execution plans:
EXPLAIN ANALYSE SELECT encode(ST_AsBinary(ST_Force_2D("point"),'NDR'),'hex') as geom,"id" from (SELECT n.id AS id,ST_Buffer(n.location_3067, 750) AS point FROM roronline.notices AS n WHERE (n.valid_from < (extract(epoch from now())) AND n.valid_to > (extract(epoch from now())))) as reind where point && ST_GeomFromText('POLYGON((490679.742759776 7231264.00430746,490679.742759776 7250039.99749315,505982.257240184 7250039.99749315,505982.257240184 7231264.00430746,490679.742759776 7231264.00430746))',3067)
EXPLAIN ANALYSE SELECT encode(ST_AsBinary(ST_Force_2D("point"),'NDR'),'hex') as geom,"id" from (SELECT n.id AS id,ST_Buffer(n.location_3067, s.notice_radius) AS point FROM roronline.notices AS n, (SELECT * FROM roronline.settings LIMIT 1) AS s WHERE (n.valid_from < (extract(epoch from now())) AND n.valid_to > (extract(epoch from now())))) as reind where point && ST_GeomFromText('POLYGON((490679.742759776 7231264.00430746,490679.742759776 7250039.99749315,505982.257240184 7250039.99749315,505982.257240184 7231264.00430746,490679.742759776 7231264.00430746))',3067)
Cheers,


On Wednesday, September 5, 2018, 11:56:27 PM CDT, ahlah <ahti.lahtela at pp.inet.fi> wrote:
 
 
 s.notice_radius is also 750.



--
Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-User-f4226646.html
_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20180906/f053b061/attachment-0001.html>