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.