Can anyone point me to the point in the source where these queries are made
to determine the times for the time dimensions for WMS GetCapabilities and
postgis stores? The problem described in my earlier post continues to
perplex me ... and I can't find the location in the source to look for
myself. I have tried!
Another oddity on this is that, if I rename my SQL VIEW layer, the sql
query changes to the VERY SLOW SELECT "timestamp" instead of SELECT
distinct("timestamp"). If I change the name of the view-based layer back
to its original name, the queries revert back to the VERY FAST SELECT
distinct("timestamp") ... with no other changes made besides the name.
As I have added more times to my time dimension, the disparity in the
distinct/non-distinct query for the time dimension is even worse ... seeing
about 8+ seconds for GetCapabilities for the non-sql-view layer, with <1
second for the sql view layer.
Thanks,
Mike Grogan
On Wed, Jul 31, 2013 at 10:12 AM, Mike Grogan <d.michael.gro...@gmail.com>wrote:
> I forgot to copy my response to the list ...
>
>
> ---------- Forwarded message ----------
> From: Mike Grogan <d.michael.gro...@gmail.com>
> Date: Wed, Jul 31, 2013 at 7:13 AM
> Subject: Re: [Geoserver-users] GetCapabilities (& other requests) very
> slow for time-enabled PostGIS layers
> To: Simone Giannecchini <simone.giannecch...@geo-solutions.it>
>
>
> Hello, Simone.
>
> Yes, time dimension is explicitly enabled.
>
> Based on logs, the issue seems to be that geoserver is querying the
> database for times from all rows instead of asking for distinct or max
> timestamp. But this is just for layers created without a SQL view in
> geoserver. For layers with a view (even ones that don't really filter or
> reduce the data in any way), the database is queried for distinct or max
> times appropriately and get capabilities and other requests return quickly.
>
> When you get a chance this is detailed in depth in the logs and writeup.
>
> Thanks again,
>
> Mike Grogan
> On Jul 31, 2013 4:01 AM, "Simone Giannecchini" <
> simone.giannecch...@geo-solutions.it> wrote:
>
>> Ciao Mike,
>> quick question before reading more in depth the email. When you say
>> "PostGIS-stored layers with a time dimension enabled" this means you
>> explicitly enable the TIME dimension in the Dimension tab for the
>> Layer page?
>>
>> If so which strategy did you set for the Dimension Presentation? If
>> you are using List this might be a bit slow (It might be that the
>> queries need to be optimized further) as we usually recommend using
>> Continuos Interval or interval. As I have more time I will try to look
>> into the rest of the email with more time.
>>
>> Regards,
>> Simone Giannecchini
>> ==
>> Our support, Your Success! Visit http://opensdi.geo-solutions.it for
>> more information.
>> ==
>>
>> Ing. Simone Giannecchini
>> @simogeo
>> Founder/Director
>>
>> GeoSolutions S.A.S.
>> Via Poggio alle Viti 1187
>> 55054 Massarosa (LU)
>> Italy
>> phone: +39 0584 962313
>> fax: +39 0584 1660272
>> mob: +39 333 8128928
>>
>> http://www.geo-solutions.it
>> http://twitter.com/geosolutions_it
>>
>> -------------------------------------------------------
>>
>>
>> On Wed, Jul 31, 2013 at 3:18 AM, Mike Grogan <d.michael.gro...@gmail.com>
>> wrote:
>> > Hello everyone,
>> >
>> > I am having an issue where WMS GetCapabilities (and other WMS requests)
>> are
>> > running very slow for PostGIS-stored layers with a time dimension
>> enabled.
>> > Note that I DO have an index on the database field for my time
>> dimension.
>> > On the surface, this seems similar to the thread found in:
>> >
>> http://osgeo-org.1560.x6.nabble.com/WMS-request-getcapabilities-takes-a-loooooooooooooong-time-to-return-td5003095.html
>> > but, again, I ** DO ** have my time field indexed. What I am seeing
>> looks
>> > like geoserver (or geotools) is not even asking for distinct or max/min
>> on
>> > the time field in some cases.
>> >
>> > Here is the setup:
>> >
>> > 1. Geoserver Version: 2.3.3 running on ubuntu with Postgres 9.1 and
>> postgis
>> > 1.5
>> > 2. Simple database table "latest" with geometry point and time
>> > field/dimension called "timestamp".
>> > 3. About 15 distinct timestamps with approx 20,000 points at each
>> timestamp
>> > for +/- 300,000 rows.
>> >
>> > Here is what I am seeing:
>> >
>> > 1. Calling GetCapabilities when only a layer using "latest" table (I
>> will
>> > call this NO SQL VIEW) is enabled takes many seconds. Enabling GEOTOOLS
>> > level debugging shows an SQL statement of SELECT "timestamp" FROM
>> > "public"."latest" when the database is being queried to gather the
>> range of
>> > available times. It does NOT look like the query is asking for DISTINCT
>> > timestamps at all ... but instead it is asking for ALL of them. Is that
>> > correct?
>> >
>> > 2. Calling GetCapabilities when only a layer using "latest" table is
>> > enabled ... but that layer is defined via a SQL VIEW ... returns
>> extremely
>> > fast! This layer is exactly the same using exactly the same data and
>> store
>> > setup as the NON-SQL-VIEW layer in #1, but just with a view defined. It
>> > returns all of the same timestamps. In fact, I am just doing a SELECT *
>> > from "latest" as my SQL VIEW ... not even filtering by any viewparams.
>> > However, in this case, I see the SQL request for determining the range
>> of
>> > available timestamps as SELECT distinct("timestamp") FROM ... so it
>> looks
>> > like the database is being queried only for distinct timestamps, not
>> ALL of
>> > them as #1 appears.
>> >
>> > 3. When calling GetMap on the NO SQL VIEW layer in #1 above, but
>> without
>> > specifying the time (meaning it will default to the most recent time
>> and
>> > has to search the database for it), I again see SELECT "timestamp" FROM
>> > "public"."latest" as the query. It does not seem to ask for the MAX.
>> This
>> > takes 4 seconds.
>> >
>> > 4. When calling GetMap on the SQL VIEW version of the layer, again
>> without
>> > specifying the time, the SQL statement is SELECT max("timestamp") FROM
>> > (SELECT * from latest) as "vtable", so the database is being asked for
>> the
>> > MAX timestamp, not all of them. This is sub-second response.
>> >
>> > 5. I see the same behavior for GetFeatureInfo WMS requests when the
>> time
>> > defaults to current. The database has to be searched for the default
>> (most
>> > recent) time. The standard NO SQL VIEW layer queries the db without
>> asking
>> > for the MAX timestamp, whereas the SQL VIEW version asks for the MAX
>> > timestamp. The NO SQL VIEW version takes 4 seconds, whereas the SQL
>> VIEW
>> > version is sub-second.
>> >
>> > Why the difference? Most importantly, is this the expected and/or
>> actual
>> > behavior for Postgis-enabled layers without a view defined in
>> geoserver? Is
>> > the expected behavior that all times in the table are queried, not
>> distinct
>> > and max times as appropriate?
>> >
>> > Excerpts from my logs are provided below ... numbered according to the
>> > descriptions above.
>> >
>> > I appreciate any help and response on this!
>> >
>> >
>> > Thanks,
>> >
>> >
>> > Mike Grogan
>> >
>> >
>> >
>> >
>> > SCENARIO #1: GetCapabilities ... NO SQL VIEW
>> >
>> > 31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
>> > "public"."latest"
>> > 31 Jul 00:22:28 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> > SCENARIO #2: GetCapabilities ... SQL VIEW
>> >
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - Gathering sql view result
>> > structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - SELECT distinct("timestamp")
>> > FROM (SELECT * from latest) as "vtable"
>> > 31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> >
>> > SCENARIO #3 ... GetMap ... time not specified (use default/current) ...
>> NO
>> > SQL VIEW
>> >
>> > 31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
>> > "public"."latest"
>> > 31 Jul 00:32:51 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> > SCENARIO #4 ... GetMap ... time not specified (use default/current) ...
>> SQL
>> > VIEW
>> >
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
>> > structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
>> > structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - SELECT max("timestamp") FROM
>> > (SELECT * from latest) as "vtable"
>> > 31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> >
>> > SCENARIO #5 ... Seeing the same behavior for GetFeatureInfo request when
>> > time not specified
>> >
>> > 31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
>> > "public"."latest"
>> > 31 Jul 00:35:29 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> >
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
>> > structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
>> > structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - SELECT max("timestamp") FROM
>> > (SELECT * from latest) as "vtable"
>> > 31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
>> >
>> >
>> >
>> >
>> ------------------------------------------------------------------------------
>> > Get your SQL database under version control now!
>> > Version control is standard for application code, but databases havent
>> > caught up. So what steps can you take to put your SQL databases under
>> > version control? Why should you start doing it? Read more to find out.
>> >
>> http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk
>> > _______________________________________________
>> > Geoserver-users mailing list
>> > Geoserver-users@lists.sourceforge.net
>> > https://lists.sourceforge.net/lists/listinfo/geoserver-users
>> >
>>
>
>
------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
