Re: [mapserver-users] Layer based on a view renders faster than if based on a table
Daniel, I already set this in the mapfile DEBUG 5 CONFIG MS_ERRORFILE /ms4w/logs/mapserver.log I don't get the SQL query in the log though. [Tue Dec 08 09:33:56 2009].961000 msDrawMap(): Layer 17 (RSSincidCumul), 0.390s [Tue Dec 08 09:33:56 2009].992000 msDrawMap(): Drawing Label Cache, 0.031s [Tue Dec 08 09:33:56 2009].992000 msDrawMap() total time: 0.421s [Tue Dec 08 09:33:57 2009].8000 msSaveImage() total time: 0.016s [Tue Dec 08 09:33:57 2009].24000 mapserv request processing time (msLoadMap not incl.): 0.453s [Tue Dec 08 09:33:57 2009].24000 msFreeMap(): freeing map at 01516C50. Steve Toutant, M. Sc. Analyste en géomatique Secteur environnement Direction de la santé environnementale et de la toxicologie Institut national de santé publique du Québec 945, avenue Wolfe Québec, Qc G1V 5B3 Tél.: (418) 650-5115 #5281 Fax.: (418) 654-3144 steve.tout...@inspq.qc.ca http://www.inspq.qc.ca Daniel Morissette dmorisse...@mapgears.com@lists.osgeo.org Envoyé par : mapserver-users-boun...@lists.osgeo.org 04/12/2009 05:05 PM A mapserver-users@lists.osgeo.org cc Objet Re: [mapserver-users] Layer based on a view renders faster than if based on a table steve.tout...@inspq.qc.ca wrote: I thought that a layer definition based on a table would be much faster than on a view. What should be the expected behavior please? Please tell me is this should be asked to postgis forum. I'd suggest you capture the exact SQL query that is run in each case (using DEBUG/MS_ERRORFILE) and check that the difference in time indeed comes form the PostGIS queries. If that's the case then you could submit a question to the postgis list based on that, taking MapServer out of the equation. If on the other hand the queries take about the same time and the time difference is in MapServer's code (which I doubt) then we'd need more details (ideally a testcase) to be able to reproduce and troubleshoot the issue. Daniel -- Daniel Morissette http://www.mapgears.com/ ___ 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] Layer based on a view renders faster than if based on a table
Steve, Have you put the DEBUG 5 directive at the layer level too ? Alan On December 8, 2009 09:36:27 am steve.tout...@inspq.qc.ca wrote: Daniel, I already set this in the mapfile DEBUG 5 CONFIG MS_ERRORFILE /ms4w/logs/mapserver.log I don't get the SQL query in the log though. [Tue Dec 08 09:33:56 2009].961000 msDrawMap(): Layer 17 (RSSincidCumul), 0.390s [Tue Dec 08 09:33:56 2009].992000 msDrawMap(): Drawing Label Cache, 0.031s [Tue Dec 08 09:33:56 2009].992000 msDrawMap() total time: 0.421s [Tue Dec 08 09:33:57 2009].8000 msSaveImage() total time: 0.016s [Tue Dec 08 09:33:57 2009].24000 mapserv request processing time (msLoadMap not incl.): 0.453s [Tue Dec 08 09:33:57 2009].24000 msFreeMap(): freeing map at 01516C50. Steve Toutant, M. Sc. Analyste en géomatique Secteur environnement Direction de la santé environnementale et de la toxicologie Institut national de santé publique du Québec 945, avenue Wolfe Québec, Qc G1V 5B3 Tél.: (418) 650-5115 #5281 Fax.: (418) 654-3144 steve.tout...@inspq.qc.ca http://www.inspq.qc.ca Daniel Morissette dmorisse...@mapgears.com@lists.osgeo.org Envoyé par : mapserver-users-boun...@lists.osgeo.org 04/12/2009 05:05 PM A mapserver-users@lists.osgeo.org cc Objet Re: [mapserver-users] Layer based on a view renders faster than if based on a table steve.tout...@inspq.qc.ca wrote: I thought that a layer definition based on a table would be much faster than on a view. What should be the expected behavior please? Please tell me is this should be asked to postgis forum. I'd suggest you capture the exact SQL query that is run in each case (using DEBUG/MS_ERRORFILE) and check that the difference in time indeed comes form the PostGIS queries. If that's the case then you could submit a question to the postgis list based on that, taking MapServer out of the equation. If on the other hand the queries take about the same time and the time difference is in MapServer's code (which I doubt) then we'd need more details (ideally a testcase) to be able to reproduce and troubleshoot the issue. Daniel -- Alan Boudreault Mapgears http://www.mapgears.com ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Layer based on a view renders faster than if based on a table
Thanks Alan, I didn't know that. Steve Steve Toutant, M. Sc. Analyste en géomatique Secteur environnement Direction de la santé environnementale et de la toxicologie Institut national de santé publique du Québec 945, avenue Wolfe Québec, Qc G1V 5B3 Tél.: (418) 650-5115 #5281 Fax.: (418) 654-3144 steve.tout...@inspq.qc.ca http://www.inspq.qc.ca Alan Boudreault aboudrea...@mapgears.com@lists.osgeo.org Envoyé par : mapserver-users-boun...@lists.osgeo.org 08/12/2009 09:39 AM A mapserver-users@lists.osgeo.org cc steve.tout...@inspq.qc.ca, Daniel Morissette dmorisse...@mapgears.com, mapserver-users-boun...@lists.osgeo.org Objet Re: [mapserver-users] Layer based on a view renders faster than if based on a table Steve, Have you put the DEBUG 5 directive at the layer level too ? Alan On December 8, 2009 09:36:27 am steve.tout...@inspq.qc.ca wrote: Daniel, I already set this in the mapfile DEBUG 5 CONFIG MS_ERRORFILE /ms4w/logs/mapserver.log I don't get the SQL query in the log though. [Tue Dec 08 09:33:56 2009].961000 msDrawMap(): Layer 17 (RSSincidCumul), 0.390s [Tue Dec 08 09:33:56 2009].992000 msDrawMap(): Drawing Label Cache, 0.031s [Tue Dec 08 09:33:56 2009].992000 msDrawMap() total time: 0.421s [Tue Dec 08 09:33:57 2009].8000 msSaveImage() total time: 0.016s [Tue Dec 08 09:33:57 2009].24000 mapserv request processing time (msLoadMap not incl.): 0.453s [Tue Dec 08 09:33:57 2009].24000 msFreeMap(): freeing map at 01516C50. Steve Toutant, M. Sc. Analyste en géomatique Secteur environnement Direction de la santé environnementale et de la toxicologie Institut national de santé publique du Québec 945, avenue Wolfe Québec, Qc G1V 5B3 Tél.: (418) 650-5115 #5281 Fax.: (418) 654-3144 steve.tout...@inspq.qc.ca http://www.inspq.qc.ca Daniel Morissette dmorisse...@mapgears.com@lists.osgeo.org Envoyé par : mapserver-users-boun...@lists.osgeo.org 04/12/2009 05:05 PM A mapserver-users@lists.osgeo.org cc Objet Re: [mapserver-users] Layer based on a view renders faster than if based on a table steve.tout...@inspq.qc.ca wrote: I thought that a layer definition based on a table would be much faster than on a view. What should be the expected behavior please? Please tell me is this should be asked to postgis forum. I'd suggest you capture the exact SQL query that is run in each case (using DEBUG/MS_ERRORFILE) and check that the difference in time indeed comes form the PostGIS queries. If that's the case then you could submit a question to the postgis list based on that, taking MapServer out of the equation. If on the other hand the queries take about the same time and the time difference is in MapServer's code (which I doubt) then we'd need more details (ideally a testcase) to be able to reproduce and troubleshoot the issue. Daniel -- Alan Boudreault Mapgears http://www.mapgears.com ___ 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] Layer based on a view renders faster than if based on a table
Hi, I have a layer that renders faster when DATA is defined on a view than on a table with a clustered index. Here is the layer definition CONNECTIONTYPE postgis CONNECTION host= dbname=xx user=xx password=xx port=5432 DATA geom900913 FROM prevalence using unique id using srid=900913 PROCESSING CLOSE_CONNECTION=DEFER PROJECTION init=epsg:900913 END My MAP projection is also 900913 If prevalence is a Postgis View: 0.469s (2.6 seconds in OpenLayers) Postgis table: 0.781s (3.6 seconds in OpenLayers) Postgis table+index+cluster: 0.782s ( 3.4 seconds in OpenLayers) I thought that a layer definition based on a table would be much faster than on a view. What should be the expected behavior please? Please tell me is this should be asked to postgis forum. thanks Steve ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] Layer based on a view renders faster than if based on a table
steve.tout...@inspq.qc.ca wrote: I thought that a layer definition based on a table would be much faster than on a view. What should be the expected behavior please? Please tell me is this should be asked to postgis forum. I'd suggest you capture the exact SQL query that is run in each case (using DEBUG/MS_ERRORFILE) and check that the difference in time indeed comes form the PostGIS queries. If that's the case then you could submit a question to the postgis list based on that, taking MapServer out of the equation. If on the other hand the queries take about the same time and the time difference is in MapServer's code (which I doubt) then we'd need more details (ideally a testcase) to be able to reproduce and troubleshoot the issue. Daniel -- Daniel Morissette http://www.mapgears.com/ ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
