Why ST_SRID cause Geoserver generated SQL query to run about 500x slower?

Question

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.

---

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.

Answer 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);