[mapserver-users] postgis layer draws much slower than psql select statement
thomas bonfort
thomas.bonfort at gmail.com
Tue Dec 3 08:54:53 PST 2013
Paul,
if you add a "DEBUG 2" to your LAYER definition, you'll also get some
debugging info as to what is happening inside mapserver's postgis
driver, along with the query that is actually sent by mapserver to
postgis (as mapserver adds some spatial filtering to account for the
current map view). Knowing that you might be able to rewrite your
query to speed things up, and/or eventually add your own !BOX!
filtering to make a more efficient use of indexes (c.f. near the end
of http://mapserver.org/input/vector/postgis.html#data-access-connection-method
)
--
thomas
On 3 December 2013 17:45, Moen, Paul T. <pmoen at nd.gov> wrote:
> I am having a problem with the draw time of the following layer.
>
> LAYER
> NAME "Daily Precip"
> CONNECTION "user=XXXXXX password=XXXXXX dbname=swc host=www.fakepg.com
> port=5432"
> CONNECTIONTYPE postgis
> 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"
> GROUP "Water Resources"
> TYPE POINT
> STATUS ON
> CLASS
> NAME "0"
> STATUS ON
> STYLE
> SYMBOL 'circle'
> COLOR 0 0 0
> SIZE 8
> END
> LABEL
> FONT helvetica
> TYPE TRUETYPE
> SIZE 8
> COLOR 0 0 0
> ANGLE AUTO
> POSITION AUTO
> END
> END
> END
>
> The following command
> shp2img -m precip.map -o ~/precip.png
> produces an image after 20+ seconds.
>
> The debug output from mapserver is below.
>
> [Tue Dec 3 08:53:02 2013].705076 msDrawMap(): rendering using outputformat
> named AGG_PNG (AGG/PNG).
> [Tue Dec 3 08:53:02 2013].705678 msDrawMap(): WMS/WFS set-up and query,
> 0.000s
> [Tue Dec 3 08:53:02 2013].715428 msDrawMap(): Layer 0 (North Dakota),
> 0.010s
> [Tue Dec 3 08:53:02 2013].756964 msDrawMap(): Layer 1 (General
> Hydrography), 0.042s
> [Tue Dec 3 08:53:02 2013].804922 msDrawMap(): Layer 2 (Counties), 0.048s
> [Tue Dec 3 08:53:24 2013].166022 msDrawMap(): Layer 3 (Daily Precip),
> 21.361s
> [Tue Dec 3 08:53:24 2013].166057 msDrawMap(): Drawing Label Cache, 0.000s
> [Tue Dec 3 08:53:24 2013].166062 msDrawMap() total time: 21.463s
> [Tue Dec 3 08:53:24 2013].192358 msSaveImage(/Users/xxxxx/precip.png) total
> time: 0.026s
> [Tue Dec 3 08:53:24 2013].192398 msFreeMap(): freeing map at
> 0x7fb1d9010200.
>
> The following psql statement executes in 218.802 ms from within psql
> connection.
>
> 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;
>
> Time: 218.802 ms
>
>
> 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.
>
>
> Versions
>
> 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
>
>
> 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
>
>
> psql output sample
>
> list | label | site_id | the_geom
> | amount
> ------+-------+---------+----------------------------------------------------+--------
> t | 20.14 | 2 | 0101000020DA0800000000000074C737410000000090B6FB40
> | 20.14
> f | 13.04 | 3 | 0101000020DA08000000000000337036410000000098CB0741
> | 13.04
> t | 17.29 | 4 | 0101000020DA08000000000000ED2036410000000028BB0B41
> | 17.29
> ...
> ...
> f | 14.27 | 4667 | 0101000020DA08000000000000831038410000000004571B41
> | 14.27
> f | 14.49 | 4708 | 0101000020DA080000000000009A743A4100000000D0C81C41
> | 14.49
> f | 0.75 | 4709 | 0101000020DA0800000000000017674041000000009CC81241
> | 0.75
> (512 rows)
> Time: 218.802 ms
>
> Query plan from psql
>
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>
> GroupAggregate (cost=33077.14..33523.12 rows=3577 width=44) (actual
> time=130.122..191.921 rows=512 loops=1)
>
> -> Sort (cost=33077.14..33135.07 rows=23174 width=44) (actual
> time=129.933..167.485 rows=66303 loops=1)
>
> Sort Key: s.site_id, s.the_geom
>
> Sort Method: external merge Disk: 3744kB
>
> -> Hash Join (cost=701.16..30683.00 rows=23174 width=44) (actual
> time=11.586..46.325 rows=66303 loops=1)
>
> Hash Cond: (p.site_id = s.site_id)
>
> -> 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)
>
> Recheck Cond: ((measdate >= '2013-04-01'::date) AND
> (measdate <= '2013-08-31'::date))
>
> -> 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)
>
> Index Cond: ((measdate >= '2013-04-01'::date) AND
> (measdate <= '2013-08-31'::date))
>
> -> Hash (cost=160.95..160.95 rows=3577 width=36) (actual
> time=3.592..3.592 rows=3444 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 219kB
>
> -> Seq Scan on station s (cost=0.00..160.95 rows=3577
> width=36) (actual time=0.013..2.632 rows=3444 loops=1)
>
> Filter: (the_geom IS NOT NULL)
>
> Rows Removed by Filter: 98
>
> Total runtime: 217.698 ms
>
> (16 rows)
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
More information about the mapserver-users
mailing list