I have a PostGIS database with a table containing about 300 000 simple polygons (table has spatial index). I have a Geoserver installed on the same server delivering this table with WMS.

I have a response time of about 6 sec to receive a single WMS tile from that dataset. (I'm using firebug to get the getMap query url and parameters including the BBOX for a single tile)

When querying that postgis database directly for extracting the same data using the same BBOX with the following query I receive the result in less than 1sec...

    ST_Transform(a.geom, 3857)
    role.my_table a
    a.geom && ST_Transform(ST_MakeEnvelope(-8079088.141629988,5721158.693088872,-8077865.1491774265,5722381.685541436, 3857), 4326);

What can be causing that slow response from Geoserver while PostGIS can extract and transform the data very fastly?

Geoserver needs 5 sec just to render it. It may worth mentionning that the same Geoserver deliver tiles very fastly from others tables with fewer rows.

  • You tranfer geoms twice to geoserver, first on a.* (original geom column is there) and second time in ST_Transform(a.geom,3857) and you dont give column name, so it is possible that GeoServer does Transformation again or something? Try to define SQL query as SELECT a.1 , a.2 , ... ST_Trasform(geom,30857) as geom FROM ... It might help you Dec 14, 2013 at 11:48
  • Thanks for mentionning but that sql query was just a test to verify the speed of execution of extracting and converting data from the same table and BBOX of my WMS... It it not the actual query used by the WMS. Jan 8, 2014 at 15:39

1 Answer 1


A common problem is styling that uses other attributes in the table, and those other attributes may not be indexed.

What I would recommend is that you turn on GEOTOOLS_DEVELOPER_LOGGING and check the logs for the exact SQL statement being run against the database. Copy that statement into a psql prompt and do an EXPLAIN on it.

  • 1
    After turning on the developper logging as suggested I found what is causing the query to run so slow. When remplacing ST_SRID("geom") by the actual real value 4326 in the geoserver generated SQL it run in 12ms instead of 5 sec!! Here is the query: SELECT "id",encode(ST_AsBinary(ST_Force_2D("geom")),'base64') as "geom" FROM role."mat_uev_cr_s" 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")) Jan 8, 2014 at 21:07
  • Now I need to find how to solve that problem... Jan 8, 2014 at 21:08
  • ST_GeomFromText(..., ST_SRID("geom")) -- the first argument of that function appears to be in a contant CRS, so why not just use that as a constant for the second argument? It looks like 4326. Jul 3, 2023 at 2:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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