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