0

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...

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

So my question is what can be causing that slow response from Geoserver while PostGIS can extract and transform the data very fastly? Geoserver need 5 sec just to render it? It may worth mentionning that the same Geoserver deliver tiles very fastly from others tables with fewer rows...

Thanks a lot

  • 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 – simplexio Dec 14 '13 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. – Etienne Desgagné Jan 8 '14 at 15:39
4

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.

  • 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")) – Etienne Desgagné Jan 8 '14 at 21:07
  • Now I need to find how to solve that problem... – Etienne Desgagné Jan 8 '14 at 21:08

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.