[mapserver-users] WMS TIME parameter slow
Heiko Schröter
schroete at iup.physik.uni-bremen.de
Fri Mar 19 01:33:50 PDT 2010
Hello,
i have setup a test db with a 'timestamp' date field (YYYY-MM-DD HH:MM:SS). Tests have been done with various request patterns i.e. leaving out the HH:MM:SS part etc.
This works just fine and you allways get fast INDEXED SCANS on this. Requests with an interval to seconds is possible and fast, even within millions of data sets.
So to me it looks like that you don't need 'date_trunc' at all. But as said i'am not a db/sql guru.
Just two observations:
1) Postgres default is to cast the request to '::timestamp without time zone' in Version 5.3:
i.e.
Index Scan using bro_v32_timed_y2003_datum on bro_v32_timed_y2003 bro_v32_timed (cost=0.00..8.28 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((datum > '2007-12-10 10:00:00'::timestamp without time zone) AND (datum < '2007-12-10 13:00:15'::timestamp without time zone))
So it might be an idea to cast the request explicitly and not to rely on POSTGRES defaults (or let the user specify a time zone).
I have done no tests with time zones set.
2) The HH:MM:SS part if present (it can be left out) needs to be regexped because POSTGRES won't accept 'HH' or 'HH:' only. A minimum of HH:M is required.
Just my 2ct. I have patched MS 5.6.1 with the above for our setup and it works just fine.
Thanks for working this over in MS6.
Regards
Heiko
> Sorry to jump late on this.
>
> The use of the date_trunc function for postgis layers (described
> http://mapserver.org/ogc/wms_time.html) was from what I remember to
> allow queries (on a timestamp field)
> like time=2001-01-01 expecting to get back all values of the day
> regardless of the time set. I have entered a bug on it
> (http://trac.osgeo.org/mapserver/ticket/3382) to be able to review it
> before the 6.0 release.
>
> regards,
>
>
> Paul Ramsey wrote:
> > Sorry, there's not much more I can offer, you've done a great job
> > debugging, but at the end of the day the SQL being generated by the
> > WMS Time module is not great. Make sure to run 'ANALYZE' on your
> > database so the planner has the latest stats.
> >
> > P.
> >
> > On Thu, Mar 11, 2010 at 5:59 AM, Heiko Schröter
> > <schroete at iup.physik.uni-bremen.de> wrote:
> >
> >> Am Donnerstag 11 März 2010 14:35:53 schrieben Sie:
> >>
> >> Paul,
> >>
> >> thanks for staying at it. These timings i get with the variation of the theme. The second best is to cast 'datum::timestamp'. But still beeing 32times slower than just a trivial compare on the index as in 1). The slowest run is the brute force 'date_trunc' without cast 3). So to me this is a bit voodo why postgres refuses to scan the indices in proper speed in 2) and 3).
> >> The maximum index scan i get with 'date_trunc' is a 'Bitmap Heap Scan' on the index with scenario 2).
> >> Of what i have found inside the docs/google is that even when casting 'datum' inside postgres the request, here generated by MS, should or have to be casted as well which is not (at least by now).
> >>
> >> Heiko
> >>
> >>
> >> 1) The brute force compare:
> >> 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-11');
> >> <snip><snap>
> >> Total runtime: 41.018 ms
> >> (32 rows)
> >>
> >> Now Create Immutable Index on our play table:
> >> CREATE INDEX o_v20_y2007_date_trunc ON o_v20_y2007 (date_trunc('day', datum::timestamp without time zone));
> >> 2)
> >> sc_gis=# CREATE INDEX o3_v20_y2007_date_trunc ON o3_v20_y2007 (date_trunc('day', datum::timestamp without time zone));
> >> ERROR: relation "o3_v20_y2007_date_trunc" already exists
> >> scia_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::timestamp without time zone) >= '2007-12-10' AND date_trunc('day', datum::timestamp without time zone) <= '2007-12-11'));
> >> <snip><snap>
> >> Total runtime: 1250.679 ms
> >> (26 rows)
> >>
> >> 3)
> >> 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-11'));
> >> <snip><snap>
> >> Total runtime: 2329.380 ms
> >> (23 rows)
> >>
> >>
> >>> 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
> >>>>>
More information about the MapServer-users
mailing list