We've noticed that the time it takes to render a response for a GetCapabilities request is in the neighborhood of 3-4 minutes. Has anyone else had a similar experience? We have 5 FeatureTypes defined all backed by a PostGIS database. After sniffing queries being made during the GetCapabilities request, we found that the following queries are causing the bottleneck:
select astext(force_2d(envelope(extent(poly)))) from TABLE_NAME where true;
I understand that its gathering the bounding box for that layer. Is there a way that we can enter this information in the configuration of the FeatureType so that this type of query does not have to be made on each GetCapabilities request? The offending code is PostgisFeatureStore.getEnvelope(). If there is no workaround for this query within Geoserver, is there a way of tuning Postgresql/Postgis to improve the query as its doing a sequential scan.
Configuration:
- Geoserver 1.5
- Postgis 1.2.1
- Postgresql 8.2.4
Thanks,
--
__________________________________
Jeremy Nix
Senior Application Developer
Southwest Financial Services, Ltd.
(513) 621-6699
Hey, I've just yesterday realized the depth of this problem, and made it a blocker for 1.5.1: http://jira.codehaus.org/browse/GEOS-1070
In the meantime, since you're on PostGIS, there is something you can do. We added the ability to use estimated extents in PostGIS: http://jira.codehaus.org/browse/GEOS-973 for version 1.5.0-RC3
It should be one of the settings when you configure your datastore. Be sure to run a 'VACUUM ANALYZE' at least once on the table before its used, and regularly afterwards if your data is changing. There's a bit more info at: http://docs.codehaus.org/display/GEOSDOC/PostGIS+DataStore
best regards,
Chris
Jeremy Nix wrote:
We've noticed that the time it takes to render a response for a GetCapabilities request is in the neighborhood of 3-4 minutes. Has anyone else had a similar experience? We have 5 FeatureTypes defined all backed by a PostGIS database. After sniffing queries being made during the GetCapabilities request, we found that the following queries are causing the bottleneck:
select astext(force_2d(envelope(extent(poly)))) from TABLE_NAME where true;
I understand that its gathering the bounding box for that layer. Is there a way that we can enter this information in the configuration of the FeatureType so that this type of query does not have to be made on each GetCapabilities request? The offending code is PostgisFeatureStore.getEnvelope(). If there is no workaround for this query within Geoserver, is there a way of tuning Postgresql/Postgis to improve the query as its doing a sequential scan.
Configuration:
- Geoserver 1.5
- Postgis 1.2.1
- Postgresql 8.2.4
Thanks,
--
Chris Holmes
The Open Planning Project
http://topp.openplans.org