Hi,
i’m trying to compute centroids of clusters of geoms via a postgis query.
something like this:
select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom
from assoc3_house
This query is used in a sql-layer (WFS) and gives reasonable results. 
BUT: the performance is very, very poor. Tablesize is about 460.000 nodes and there is a spatial index on geom. Runtime 3-4 minutes.
In PSQL the same query (of course with the corrisponding bbox) is running in 2-3 seconds giving the same results.
Any idea what is going on and what i can do?
regards
walter
···
–
My projects:
Admin Boundaries of the World
Missing Boundaries
Emergency Map
Postal Code Map (Germany only)
Fools (QA for zipcodes in Germany)
Postcode Boundaries of Germany
If you turn the logging level up you will be able to see the actual query that GeoServer is running in the log file. Running explain on that may give more clues.
Ian
···
Ian Turton
Hi Walter
Le 25/03/2019 à 01:18, wambacher@anonymised.com a écrit :
select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom
from assoc3_house
This query is used in a sql-layer (WFS) and gives reasonable results.
BUT: the performance is very, very poor. Tablesize is about 460.000 nodes and there is a spatial index on geom. *Runtime 3-4 minutes.*
In PSQL the same query (of course with the corrisponding bbox) is running in 2-3 seconds giving the same results.
Have you rewritten the query, or have you copy/pasted it from GeoServer's log ?
Geoserver wraps the query inside a subquery and then applies the bounding box's where clause on the subquery. Maybe this is what causes the different behaviour.
Note that if you do :
select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom from assoc3_house where geom && bbox
you will filter on the base geometry and therefore use your spatial index on the "geom" column.
But if you do :
SELECT * FROM (select st_centroid(unnest(st_clusterwithin(geom,0.00045))) geom from assoc3_house) AS vtable WHERE geom && bbox
you will filter on the centroid geometry which is the subqueries output and therefore NOT the index on the geom column.
Both queries wont give exactly the same results, for clusters that don't entirely lie within your bbox for instance.
Cheers !
--
Arnaud