Re: [mapserver-users] WMS TIME parameter slow
Hello,
i have setup a test db with a 'timestamp' date field (-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
schro...@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');
snipsnap
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'));
snipsnap
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'));
snipsnap
Total runtime: 2329.380 ms
(23 rows)
Good job getting this far on your own! Create a SQL
Re: [mapserver-users] WMS TIME parameter slow
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
schro...@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');
snipsnap
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'));
snipsnap
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'));
snipsnap
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
schro...@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
Re: [mapserver-users] WMS TIME parameter slow
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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::geometry)
-
Re: [mapserver-users] WMS TIME parameter slow
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
schro...@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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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
'0103000100050035884A47F6DC60C038D93310958E53C035884A47F6DC60C03FD93310958E534035884A47F6DC60403FD93310958E534035884A47F6DC604038D93310958E53C035884A47F6DC60C038D93310958E53C0'::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))
Re: [mapserver-users] WMS TIME parameter slow
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');
snipsnap
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'));
snipsnap
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'));
snipsnap
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
schro...@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
Re: [mapserver-users] WMS TIME parameter slow
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
schro...@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');
snipsnap
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'));
snipsnap
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'));
snipsnap
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
schro...@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
[mapserver-users] WMS TIME parameter slow
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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WMS TIME parameter slow
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
schro...@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WMS TIME parameter slow
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
schro...@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
