1

When investigating why Geoserver was so slow to render my WMS tiles from a POSTGIS database I found that the following Geoserver generated SQL query run almost 500x faster when remplacing the ST_SRID("geom") function by its actual value 4326.

SELECT "id",encode(ST_AsBinary(ST_Force_2D("geom")),'base64') as "geom" FROM "my_table" WHERE  "geom" && ST_GeomFromText('POLYGON ((-73.29392552375793 45.63803979207905, -73.29392552375793 45.63901497630168, -73.29253077507019 45.63901497630168, -73.29253077507019 45.63803979207905, -73.29392552375793 45.63803979207905))', ST_SRID("geom"))

Is there anything I can do in Geoserver settings or anywhere else to counter that issue?

I'm running POSTGIS 2.1.0 with Postgresql 9.3 on Windows Server 2008 with Geoserver 2.4.1.

*UPDATE: * Running an EXPLAIN on the queries show me that using ST_SRID force POSTGIS to do a seq scan instead of an index scan... that explain the slow response time but I'm still looking for a solution to this issue...

Thanks a lot

Etienne

  • 1
    Its probably breaking the indexing on the postgis service. Try an EXPLAIN on the query to see how it is working. – BradHards Jan 9 '14 at 0:09
  • you could try add srid contraint to table if possible. ALTER TABLE zzzz ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326) – simplexio Jan 9 '14 at 8:27
  • An EXPLAIN on the query gave me: Seq Scan on my_table (cost=0.00..49082.27 rows=31 width=577) – Etienne Desgagné Jan 9 '14 at 13:20
  • Adding the suggested contraint did not solved the problem. – Etienne Desgagné Jan 9 '14 at 13:34
1

Your GeoServer layer definition probably doesn't know what SRID it is, so it's trying to figure it out on the fly with the ST_SRID call. Make sure the SRID is defined on the layer, and hopefully then GeoServer will start using the literal instead of trying to read it off the column.

I'm not sure of the internals of GeoServer, but one thing to check is that your column actually advertises its SRID in the metadata. Does 4326 show up as the SRID in the geometry_columns table? If not, try altering the table definition so the SRID is explicit.

ALTER TABLE mytable 
ALTER COLUMN geom TYPE Geometry(Point,4326) 
USING ST_SetSRID(geom,4326);
  • Geoserver was effectively not able to detect the correct SRID... everything was ok on the database metadata... The problem was in the geoserver datastore configuration... I was letting the db schema empty to be able to publish layers from differents schemas... That was working but this empty schema was causing the srid undetection... Thanks a lot – Etienne Desgagné Jan 27 '14 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.