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