[mapserver-users] postgis layer draws much slower than psql select statement
Moen, Paul T.
pmoen at nd.gov
Tue Dec 3 10:20:48 PST 2013
Perfect answer Thomas!
After adding DEBUG 2 in my layer, I see that the actual select statement
is
select "label",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as
geom,"site_id" 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 where the_geom &&
ST_GeomFromText('POLYGON((1050185.01508599
53641.4216216207,1050185.01508599 1290206.76019656,2987470.71218674
1290206.76019656,2987470.71218674 53641.4216216207,1050185.01508599
53641.4216216207))',2266)
My new data value in the layer uses the !BOX! substitution.
DATA "the_geom from (select precip.*,station.the_geom from (select * from
precip.station s where the_geom && !BOX!) station inner join (select
(count(*)/((date '8-31-2013'- date '4-1-2013')+1)::numeric>=0.98) as list,
round(sum(p.amount)::numeric,2) as label,round(sum(p.amount)::numeric,2)
as amount,p.site_id from precip.precip_data p where p.measDate >= date
'4-1-2013' and p.measDate <= date '8-31-2013' group by p.site_id) precip
on precip.site_id=station.site_id) as foo2 using SRID=2266 USING UNIQUE
site_id"
This creates the following select statement which takes 5 seconds instead
of 20.
select "label",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as
geom,"site_id" from (select precip.*,station.the_geom from (select * from
precip.station s where the_geom &&
ST_GeomFromText('POLYGON((1050185.01508599
53641.4216216207,1050185.01508599 1290206.76019656,2987470.71218674
1290206.76019656,2987470.71218674 53641.4216216207,1050185.01508599
53641.4216216207))',2266)) station inner join (select (count(*)/((date
'8-31-2013'- date '4-1-2013')+1)::numeric>=0.98) as list,
round(sum(p.amount)::numeric,2) as label,round(sum(p.amount)::numeric,2)
as amount,p.site_id from precip.precip_data p where p.measDate >= date
'4-1-2013' and p.measDate <= date '8-31-2013' group by p.site_id) precip
on precip.site_id=station.site_id) as foo2
Hopefully I can clean it up a little more to make it faster.
Thanks for your help,
Paul
On 12/3/13 10:54 AM, "thomas bonfort" <thomas.bonfort at gmail.com> wrote:
>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-meth
>od
>)
>
>--
>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