Ensure PostGIS tables have a spatial index
Spatial indices are paramount to ensure a good performance of queries and operations performed on a database. Database tables that contain layers served by Geoserver should have spatial indices.
To build a spatial index on a table with a geometry column, use the CREATE INDEX
function as follows:
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometrycolumn] );
That is, for a layer stored in a table named mylayer
and a geometry column the_geom
, the following query will create a spatial index:
CREATE INDEX idx_mylayer ON mylayer USING GIST ( the_geom );
To know more about spatial indices in PostGIS, check the PostGIS documentation