[mapserver-users] WMS TIME parameter slow

Heiko Schröter schroete at iup.physik.uni-bremen.de
Thu Mar 11 04:39:13 EST 2010


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
-----------------------------------------------------------------------


More information about the mapserver-users mailing list