Spatial databases can contain millions of geographic objects. In some cases, a PostGIS query may take several seconds — or even several minutes — to execute.
This problem often occurs when:
- spatial indexes are not being used
- geometries are complex
- spatial queries are poorly designed
- or database statistics are outdated.
In this article, we will go through the standard steps used to diagnose and optimize a slow PostGIS query.
1. Start by Analyzing the Query
The first step is understanding how PostgreSQL executes the query.
For this, we use:
EXPLAIN ANALYZE
Example:
EXPLAIN ANALYZE
SELECT *
FROM routes r
JOIN communes c
ON ST_Intersects(r.geom, c.geom);
This command allows you to see:
- whether a spatial index is being used
- how many rows are scanned
- how long each step takes.
If you see:
Seq Scan
it means PostgreSQL is scanning the entire table.
2. Check for Spatial Indexes
Indexes are essential for spatial queries.
A spatial index is usually created like this:
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Indexes allow PostgreSQL to drastically reduce the number of geometries that need to be analyzed.
Without a spatial index, performance can become extremely poor.
3. Use Index-Friendly Spatial Operators
Some PostGIS functions automatically use spatial indexes.
For example:
- ST_Intersects
- ST_Within
- ST_Contains
However, it is often useful to add a bounding box filter:
geom && other_geom
Example:
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
The bounding box test is much faster.
4. Check Geometry Complexity
Highly detailed geometries can significantly slow down some operations.
In some cases, it may be useful to simplify geometries:
ST_Simplify(geom, tolerance)
or to create a simplified table for specific analyses.
5. Avoid Certain Functions in WHERE Clauses
Some functions prevent indexes from being used efficiently.
For example:
ST_Buffer(geom, 100)
inside a WHERE clause can dramatically slow down a query.
Bad example:
WHERE ST_Intersects(ST_Buffer(geom,100), zone)
It is often better to rewrite the query.
6. Check Database Statistics
PostgreSQL uses statistics to choose the execution plan.
After many data modifications, it is recommended to run:
ANALYZE
or
VACUUM ANALYZE
This helps PostgreSQL generate a better query plan.
7. Reduce the Amount of Data Being Processed
It is often useful to reduce the dataset before performing spatial operations.
For example, using an attribute filter:
WHERE type = ‘highway’
This reduces the number of objects being tested.
8. Test Different Approaches
In some cases, different query formulations can produce very different performance results.
It is therefore useful to test several strategies:
- spatial joins
- subqueries
- intermediate materialization.
9. The 5 Most Common PostGIS Mistakes That Slow Down Queries
Certain mistakes appear very frequently in spatial databases.
Missing Spatial Index
This is the most common cause.
Without a spatial index, PostgreSQL must scan every geometry in the table.
Create an index:
CREATE INDEX idx_routes_geom
ON routes
USING GIST (geom);
Poor Order of Operations
Some queries immediately execute an expensive spatial operation on the entire table.
Inefficient example:
SELECT *
FROM buildings
WHERE ST_Intersects(geom, ST_Buffer(:zone,100));
In this case, the ST_Buffer function is recalculated for every row.
Spatial Function Used Without a Spatial Filter
A spatial join can be very slow if no bounding box filter is used.
Recommended example:
SELECT *
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom);
The && operator uses the spatial index.
Overly Complex Geometries
Some geometries contain thousands of vertices.
In some cases, it may be useful to use:
ST_Simplify(geom, tolerance)
or store a simplified version.
Outdated PostgreSQL Statistics
If the table has been heavily modified, PostgreSQL may choose a poor execution plan.
Update statistics:
VACUUM ANALYZE
Real Optimization Example
Suppose we want to find all roads inside a municipality.
Initial query:
SELECT r.*
FROM routes r, communes c
WHERE ST_Intersects(r.geom, c.geom)
AND c.nom = ‘Paris’;
Execution time: 45 seconds
Optimized Version
SELECT r.*
FROM routes r
JOIN communes c
ON r.geom && c.geom
AND ST_Intersects(r.geom, c.geom)
WHERE c.nom = ‘Paris’;
With:
- spatial index
- bounding box filter
Execution time: less than one second
Quick Checklist for Diagnosing a Slow PostGIS Query
When a query takes too long, check:
✔ the query using EXPLAIN ANALYZE
✔ the presence of a spatial index
✔ the use of index-compatible operators
✔ geometry complexity
✔ the presence of an attribute filter
✔ PostgreSQL statistics (ANALYZE)
Conclusion
When a PostGIS query is slow, it is important to follow a step-by-step optimization process:
- analyze the query with EXPLAIN ANALYZE
- verify spatial indexes
- use index-compatible operators
- simplify geometries when necessary
- maintain up-to-date database statistics.
With these simple steps, it is often possible to reduce execution times from several minutes to just a few seconds.
In most cases, a slow PostGIS query is caused by a missing index or a poor execution plan. A methodical analysis using EXPLAIN ANALYZE will almost always help identify the problem.
