[Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

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