[mapserver-users] WMS TIME parameter slow
Paul Ramsey
pramsey at cleverelephant.ca
Thu Mar 11 05:35:53 PST 2010
Good job getting this far on your own! Create a SQL wrapper function
on top of date_trunc which *is* marked as immutable and you'll be able
to build a functional index on it.
P.
On Thu, Mar 11, 2010 at 1:39 AM, Heiko Schröter
<schroete at iup.physik.uni-bremen.de> wrote:
> Am Mittwoch 10 März 2010 16:52:28 schrieb Heiko Schröter:
>
> Done what has been suggested. The 'date_trunc' function forces sequential scans instead of using the indexed scans.
>
> I've tried and failed:
> sc_gis=# CREATE INDEX o_v20_y2007_date_trunc ON o_v20_y2007 (date_trunc('day', datum at time zone 'GMT'));
> ERROR: functions in index expression must be marked IMMUTABLE
>
> Probably the MS list is not the right place to ask.
> But may be a kind soul has some pointers to more infos.
> I admit that db's are not my field of experience and i'am still learning.
>
> On the other hand it would be nice to have a hint about the indexing issue inside the MS docs when using the WMS Time extension.
>
> Heiko
>
>
> sc_gis=# explain analyze select "datum","farbe",encode(AsBinary(force_collection(force_2d("rechteck")),'NDR'),'hex') as geom,"datum" from o_v20 where rechteck
> && GeomFromText('POLYGON((-134.90506329114 -78.2278481012662,-134.90506329114 78.2278481012663,134.90506329114
> 78.2278481012663,134.90506329114 -78.2278481012662,-134.90506329114 -78.2278481012662))',-1) and (datum >= '2007-12-10' and datum <= '2007-12-30');
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=0.00..585.37 rows=4420 width=450) (actual time=0.083..35.821 rows=5322 loops=1)
> -> Append (cost=0.00..541.17 rows=4420 width=450) (actual time=0.067..13.852 rows=5322 loops=1)
> -> Seq Scan on o3_v20 (cost=0.00..16.65 rows=1 width=55) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> -> Index Scan using o3_v20_y2002_datum on o3_v20_y2002 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.012..0.012 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2003_datum on o3_v20_y2003 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.006..0.006 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2004_datum on o3_v20_y2004 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.005..0.005 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2005_datum on o3_v20_y2005 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.005..0.005 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2006_datum on o3_v20_y2006 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.005..0.005 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2007_datum on o3_v20_y2007 o3_v20 (cost=0.00..458.27 rows=4411 width=450) (actual time=0.031..10.732 rows=5322 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2008_datum on o3_v20_y2008 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.010..0.010 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2009_datum on o3_v20_y2009 o3_v20 (cost=0.00..8.28 rows=1 width=450) (actual time=0.006..0.006 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> -> Index Scan using o3_v20_y2010_datum on o3_v20_y2010 o3_v20 (cost=0.00..8.27 rows=1 width=450) (actual time=0.005..0.005 rows=0 loops=1)
> Index Cond: ((datum >= '2007-12-10'::date) AND (datum <= '2007-12-30'::date))
> Filter: (rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
> Total runtime: 37.760 ms
> (32 rows)
>
> sc_gis=# explain analyze select "datum","farbe",encode(AsBinary(force_collection(force_2d("rechteck")),'NDR'),'hex') as geom,"datum" from o_v20 where rechteck
> && GeomFromText('POLYGON((-134.90506329114 -78.2278481012662,-134.90506329114 78.2278481012663,134.90506329114
> 78.2278481012663,134.90506329114 -78.2278481012662,-134.90506329114 -78.2278481012662))',-1) and ((date_trunc('day', datum) >= '2007-12-10' AND date_trunc('day', datum) <= '2007-12-30'));
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=0.00..55305.53 rows=2873 width=450) (actual time=1368.129..2371.436 rows=5322 loops=1)
> -> Append (cost=0.00..55276.80 rows=2873 width=450) (actual time=1368.100..2339.593 rows=5322 loops=1)
> -> Seq Scan on o3_v20 (cost=0.00..20.45 rows=1 width=55) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2002 o3_v20 (cost=0.00..2228.21 rows=123 width=450) (actual time=79.684..79.684 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2003 o3_v20 (cost=0.00..6587.07 rows=349 width=450) (actual time=238.174..238.174 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2004 o3_v20 (cost=0.00..7417.62 rows=378 width=450) (actual time=260.250..260.250 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2005 o3_v20 (cost=0.00..7328.16 rows=379 width=450) (actual time=264.246..264.246 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2006 o3_v20 (cost=0.00..7299.65 rows=383 width=450) (actual time=263.991..263.991 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2007 o3_v20 (cost=0.00..7642.97 rows=397 width=450) (actual time=261.744..297.122 rows=5322 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2008 o3_v20 (cost=0.00..8583.06 rows=439 width=450) (actual time=472.648..472.648 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2009 o3_v20 (cost=0.00..6961.72 rows=361 width=450) (actual time=394.525..394.525 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> -> Seq Scan on o3_v20_y2010 o3_v20 (cost=0.00..1207.89 rows=63 width=450) (actual time=65.417..65.417 rows=0 loops=1)
> Filter: ((rechteck && '0103000000010000000500000035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry) AND (date_trunc('day'::text, (datum)::timestamp with time zone) >= '2007-12-10 00:00:00+01'::timestamp with time zone) AND (date_trunc('day'::text, (datum)::timestamp with time zone) <= '2007-12-30 00:00:00+01'::timestamp with time zone))
> Total runtime: 2374.403 ms
> (23 rows)
>
>
>
>> Am Mittwoch 10 März 2010 16:42:02 schrieben Sie:
>>
>> Thanks for the hint.
>> Hm, i thought 4000 out of 4.5 Millions datasets for the whole year in 5 sec would be not that bad ....
>> The timing figure shall just show the huge difference between the two types of requests.
>>
>> I will follow your advice and test the db. And yes the db is indexed.
>>
>> Heiko
>>
>> > Firstly, 2 seconds is not fast, so you have another performance troll
>> > beyond this one.
>> >
>> > Secondly, it's impossible to know what's going on inside the database
>> > without seeing the actual SQL being generated. Turn on statement
>> > loggging in PgSQL and extract the SQL that is being run against the
>> > database. Then you can put that into PgAdmin or psql and get EXPLAIN
>> > ANALYZE results which will tell you why there's a problem, hopefully.
>> >
>> > P.
>> >
>> > On Wed, Mar 10, 2010 at 7:22 AM, Heiko Schröter
>> > <schroete at iup.physik.uni-bremen.de> wrote:
>> > > Hello,
>> > >
>> > > using mapserv (5.6.1) with PostGIS (8.3) and accessing ten-thousands of geo locations in the PostGIS db results in very slow speed when using the WMS TIME parameter:
>> > > (in this example the number is about 4000 datapoints for this day, timing measurements with DEBUG OFF).
>> > >
>> > > 1) Very fast using FILTER, approx. 5 seconds:
>> > > ....
>> > > CONNECTION "user={BF961C25A57BA226} password={C9748CA35D48D049} dbname={6B40E65B12B92106} host=localhost"
>> > > DATA "geocoord from xyz_ref using unique datum"
>> > > VALIDATION
>> > > 'filter' "^datum >= '[0-9]{4}-[0-9]{2}-[0-9]{2}' and datum <= '[0-9]{4}-[0-9]{2}-[0-9]{2}'"
>> > > END
>> > > FILTER "datum >= '2007-12-10' and datum < '2007-12-11'"
>> > > ....
>> > >
>> > > 2) Very slow using WMS TIME, approx 210 and more seconds:
>> > > (Leaving the VALIDATION/FILTER on or leaving out 'using unique datum' doesn't change a thing)
>> > > ...
>> > > METADATA
>> > > "wms_timeextent" "1970-01-01/2030-12-31"
>> > > "wms_timeitem" "datum"
>> > > "wms_timedefault" "2007-12-10/2007-12-11"
>> > > "wms_title" "xyz_r"
>> > > "wms_srs" "init=epsg:-1"
>> > > END
>> > > ...
>> > >
>> > > The MS ERRORFILE is very noisy spilling out all datapoints when running 2). This does not happen when running 1).
>> > > Database is the same. No other changes have been made.
>> > >
>> > > Why is mapserver so slow when using the WMS TIME parameter ?
>> > >
>> > > It would be nice to stick to the "official" WMS configuration and not to introduce a "vendor specific" workaround.
>> > >
>> > > Thanks and Regards
>> > > Heiko
>> > >
>> > > # ######################################################################################################
>> > >
>> > > /var/www/localhost/cgi-bin/mapserv -v
>> > > MapServer version 5.6.1 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WFS_SERVER SUPPORTS=WCS_SERVER INPUT=EPPL7 INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
>> > >
>> > > A complete Layer definition:
>> > >
>> > > LAYER
>> > > EXTENT -180 -90 180 90
>> > > CONNECTIONTYPE POSTGIS
>> > > NAME "SACURA_REF_R"
>> > > PROCESSING "CLOSE_CONNECTION=DEFER"
>> > > OPACITY 60
>> > > STATUS ON
>> > > METADATA
>> > > # "wms_timeextent" "1970-01-01/2030-12-31"
>> > > # "wms_timeitem" "datum"
>> > > # "wms_timedefault" "2007-12-10/2007-12-11"
>> > > "wms_title" "xyz_ref_r"
>> > > "wms_srs" "init=epsg:-1"
>> > > END
>> > > PROJECTION
>> > > "init=epsg:4326"
>> > > END
>> > > CONNECTION "user={BF961C25A57BA226} password={C9748CA35D48D049} dbname={6B40E65B12B92106} host=localhost"
>> > > DATA "geocoord from xyz_ref using unique datum"
>> > > VALIDATION
>> > > 'filter' "^datum >= '[0-9]{4}-[0-9]{2}-[0-9]{2}' and datum <= '[0-9]{4}-[0-9]{2}-[0-9]{2}'"
>> > > END
>> > > FILTER "datum >= '2007-12-10' and datum < '2007-12-11'"
>> > >
>> > > TYPE POLYGON
>> > > SIZEUNITS meters
>> > > CLASS
>> > > DEBUG OFF
>> > > NAME "xyz_ref_class"
>> > > STYLE
>> > > OUTLINECOLOR 0 0 0
>> > > COLOR [rfarbe]
>> > > END
>> > > END
>> > > END # Layer
>> > > _______________________________________________
>> > > mapserver-users mailing list
>> > > mapserver-users at lists.osgeo.org
>> > > http://lists.osgeo.org/mailman/listinfo/mapserver-users
>> > >
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>
>
> --
> -----------------------------------------------------------------------
> Dipl.-Ing. Heiko Schröter
> Institute of Environmental Physics (IUP) phone: ++49-(0)421-218-62092
> Institute of Remote Sensing (IFE) fax: ++49-(0)421-218-4555
> University of Bremen (FB1)
> P.O. Box 330440 email: schroete at iup.physik.uni-bremen.de
> Otto-Hahn-Allee 1
> 28359 Bremen
> Germany
> -----------------------------------------------------------------------
> _______________________________________________
> 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