<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif; ">
<div>
<div>I am having a problem with the draw time of the following layer.</div>
<div><br>
</div>
<div>LAYER</div>
<div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>NAME "Daily Precip"</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>CONNECTION "user=XXXXXX password=XXXXXX dbname=swc host=www.fakepg.com port=5432"</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>CONNECTIONTYPE postgis</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>DATA "the_geom from (select (count(*)/((date '8-31-2013'- date '4-1-2013')+1)::numeric>=0.98) as list, round(sum(p.amount)::numeric,2) as label,s.site_id,s.the_geom,round(sum(p.amount)::numeric,2)
as amount from precip.station s join precip.precip_data p on p.site_id=s.site_id where the_geom is not null and p.measDate >= date '4-1-2013' and p.measDate <= date '8-31-2013' group by s.site_id,s.the_geom) as foo2 using SRID=2266 USING UNIQUE site_id"</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>GROUP "Water Resources"</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>TYPE POINT</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>STATUS ON</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>CLASS </div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>NAME "0"</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>STATUS ON</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>STYLE</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>SYMBOL 'circle'</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>COLOR 0 0 0</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>SIZE 8</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>END</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>LABEL</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>FONT helvetica</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>TYPE TRUETYPE</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>SIZE 8</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>COLOR 0 0 0</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>ANGLE AUTO</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>POSITION AUTO</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>END</div>
<div><span class="Apple-tab-span" style="white-space: pre; "></span>END</div>
<div>END</div>
</div>
<div><br>
</div>
The following command<br>
<div><span style="font-family: Menlo; font-size: 11px; "><span class="Apple-tab-span" style="white-space: pre; "></span>shp2img -m precip.map -o ~/precip.png</span></div>
<div>produces an image after 20+ seconds.</div>
<div><br>
</div>
<div>The debug output from mapserver is below.</div>
<div><br>
</div>
<div>
<div>[Tue Dec 3 08:53:02 2013].705076 msDrawMap(): rendering using outputformat named AGG_PNG (AGG/PNG).</div>
<div>[Tue Dec 3 08:53:02 2013].705678 msDrawMap(): WMS/WFS set-up and query, 0.000s</div>
<div>[Tue Dec 3 08:53:02 2013].715428 msDrawMap(): Layer 0 (North Dakota), 0.010s</div>
<div>[Tue Dec 3 08:53:02 2013].756964 msDrawMap(): Layer 1 (General Hydrography), 0.042s</div>
<div>[Tue Dec 3 08:53:02 2013].804922 msDrawMap(): Layer 2 (Counties), 0.048s</div>
<div>[Tue Dec 3 08:53:24 2013].166022 msDrawMap(): Layer 3 (Daily Precip), 21.361s</div>
<div>[Tue Dec 3 08:53:24 2013].166057 msDrawMap(): Drawing Label Cache, 0.000s</div>
<div>[Tue Dec 3 08:53:24 2013].166062 msDrawMap() total time: 21.463s</div>
<div>[Tue Dec 3 08:53:24 2013].192358 msSaveImage(/Users/xxxxx/precip.png) total time: 0.026s</div>
<div>[Tue Dec 3 08:53:24 2013].192398 msFreeMap(): freeing map at 0x7fb1d9010200.</div>
</div>
<div><br>
</div>
<div>The following psql statement executes in 218.802 ms from within psql connection. </div>
<div><br>
</div>
<div>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">select (count(*)/((date '8-31-2013'- date '4-1-2013')+1)::numeric>=0.98) as list, round(sum(p.amount)::numeric,2) as label,s.site_id,s.the_geom,round(sum(p.amount)::numeric,2) as amount from precip.station
s join precip.precip_data p on p.site_id=s.site_id where the_geom is not null and p.measDate >= date '4-1-2013' and p.measDate <= date '8-31-2013' group by s.site_id,s.the_geom;</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">Time: 218.802 ms</p>
</div>
<div><br>
</div>
<div>My question is why does it take mapserver so long to draw that layer, 21.463s when the select statement runs so much faster from psql? Is there anything I can do about the issue. </div>
<div><br>
</div>
<div><br>
</div>
<div>
<div>Versions</div>
<div><br>
</div>
<div>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">MapServer version 6.4.0 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT
SUPPORTS=WCS_SERVER SUPPORTS=GEOS INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE</p>
</div>
<div><br>
</div>
<div>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.3" LIBJSON="UNKNOWN" TOPOLOGY RASTER</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "><br>
</p>
</div>
</div>
<div>psql output sample</div>
<div><br>
</div>
<div>
<div> list | label | site_id | the_geom | amount </div>
<div>------+-------+---------+----------------------------------------------------+--------</div>
<div> t | 20.14 | 2 | 0101000020DA0800000000000074C737410000000090B6FB40 | 20.14</div>
<div> f | 13.04 | 3 | 0101000020DA08000000000000337036410000000098CB0741 | 13.04</div>
<div> t | 17.29 | 4 | 0101000020DA08000000000000ED2036410000000028BB0B41 | 17.29</div>
<div>...</div>
<div>...</div>
<div> f | 14.27 | 4667 | 0101000020DA08000000000000831038410000000004571B41 | 14.27</div>
<div> f | 14.49 | 4708 | 0101000020DA080000000000009A743A4100000000D0C81C41 | 14.49</div>
<div> f | 0.75 | 4709 | 0101000020DA0800000000000017674041000000009CC81241 | 0.75</div>
<div>(512 rows)</div>
<div><span style="font-family: Menlo; font-size: 11px; ">Time: 218.802 ms</span></div>
</div>
<div><span style="font-family: Menlo; font-size: 11px; "><br>
</span></div>
<div><span style="font-family: Menlo; font-size: 11px; ">Query plan from psql</span></div>
<div><span style="font-family: Menlo; font-size: 11px; "><br>
</span></div>
<div>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> QUERY PLAN </p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">---------------------------------------------------------------------------------------------------------------------------------------------------------</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> GroupAggregate (cost=33077.14..33523.12 rows=3577 width=44) (actual time=130.122..191.921 rows=512 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Sort (cost=33077.14..33135.07 rows=23174 width=44) (actual time=129.933..167.485 rows=66303 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Sort Key: s.site_id, s.the_geom</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Sort Method: external merge Disk: 3744kB</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Hash Join (cost=701.16..30683.00 rows=23174 width=44) (actual time=11.586..46.325 rows=66303 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Hash Cond: (p.site_id = s.site_id)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Bitmap Heap Scan on precip_data p (cost=495.49..30100.03 rows=23291 width=12) (actual time=7.969..20.132 rows=67868 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Recheck Cond: ((measdate >= '2013-04-01'::date) AND (measdate <= '2013-08-31'::date))</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Bitmap Index Scan on precip_data_measdate (cost=0.00..489.67 rows=23291 width=0) (actual time=7.899..7.899 rows=67868 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Index Cond: ((measdate >= '2013-04-01'::date) AND (measdate <= '2013-08-31'::date))</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Hash (cost=160.95..160.95 rows=3577 width=36) (actual time=3.592..3.592 rows=3444 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Buckets: 1024 Batches: 1 Memory Usage: 219kB</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> -> Seq Scan on station s (cost=0.00..160.95 rows=3577 width=36) (actual time=0.013..2.632 rows=3444 loops=1)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Filter: (the_geom IS NOT NULL)</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Rows Removed by Filter: 98</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; "> Total runtime: 217.698 ms</p>
<p style="margin: 0px; font-size: 11px; font-family: Menlo; ">(16 rows)</p>
</div>
</div>
<div><br>
</div>
</body>
</html>