PostGIS spatial indexes: measure before optimizing
Use EXPLAIN, GiST and queries with correct units to evaluate a reproducible improvement.
Editorial review: 2026-09-23
A spatial index reduces candidates; it does not eliminate all geometry work. Improvement depends on distribution, selectivity, caching and the query. Do not publish an acceleration percentage without reproducible data, hardware and procedure. The following laboratory generates synthetic points and compares plans.
Prepare a controlled dataset
Use a test database with PostGIS. The SQL creates a temporary table removed when the session closes; its coordinates do not represent real locations.
CREATE TEMP TABLE bench_points AS
SELECT i AS id,
ST_SetSRID(ST_MakePoint((i % 1000)::double precision,
(i / 1000)::double precision), 3857) AS geom
FROM generate_series(1, 100000) AS i;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM bench_points
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(500,50),3857), 10);
CREATE INDEX bench_points_gix ON bench_points USING gist (geom);
ANALYZE bench_points;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM bench_points
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(500,50),3857), 10);
EXPLAIN ANALYZE executes the query. Use it here on disposable data; do not apply it to an operational write without understanding its effects. Compare returned rows, discarded rows, buffers and time rather than just the index name.
Interpret the plan
PostgreSQL may choose a sequential scan for a small table or a query returning much of the dataset. That does not establish index failure. Change the radius and observe selectivity. Repeat executions and distinguish initial reads from later cached runs.
ST_DWithin can use indexes for proximity selection. Transforming every geometry in a column may prevent use of the simple index on that column. For frequent queries in another CRS, evaluate a prepared column or matching expression index; measure storage and update costs.
Precision and units
This example uses synthetic EPSG:3857 coordinates to illustrate planning, not official distance measurement. For a real project, choose a suitable projection or geography and build the corresponding index. Fast computation with incorrect units remains incorrect.
Maintain statistics after substantial loads and observe actual queries before adding indexes. Each index consumes storage and increases write work. Test attribute filters alongside spatial filters because the best strategy may combine indexes.
Evidence of improvement
Retain the query, dataset size, version, plans and results from multiple runs. Functional output must be identical before and after optimization. If feature counts or boundary-inclusion rules change, you are not measuring performance alone. Validate next with representative copied data and expected concurrency.
Read a query plan as a sequence of decisions
Start with correctness: record the exact result count and a few boundary cases. Then compare the number of rows PostgreSQL expected with the number it actually processed. A large difference suggests statistics or distribution deserve attention. Inspect whether time is spent finding candidates, testing geometry, sorting, transferring many rows or serializing a response outside the database.
An index can accelerate candidate selection while the end-to-end map remains slow because the application requests too much geometry. Measure database execution, service serialization, response bytes and browser rendering separately. These are different bottlenecks and need different fixes.
Run a useful experiment matrix
Use the same synthetic table and repeat the query with radii 1, 10 and 100. Keep the point, server configuration and query text otherwise unchanged. Run each case several times and label the first execution separately. This exercise explores selectivity and caching; do not compare its timings with an Esri service and call the difference a product benchmark.
| Variable | Hold constant or record | Why it matters |
|---|---|---|
| Result size | Rows and geometry complexity | Returning most of the table may favor a scan |
| Cache state | First and repeated executions | Memory hits differ from storage reads |
| Concurrent activity | Active queries and maintenance | Other work changes timing |
| Data distribution | Extent and clustering | Uniform synthetic points are not urban parcels |
| Output form | Count, rows, GeoJSON or tiles | Serialization and transfer costs differ |
Report medians and ranges when enough runs are available. Keep the raw measurements rather than choosing only the fastest run. For an operational target, define a response-time budget with the product owner and measure under expected concurrency; a single isolated query does not establish it.
Keep indexed columns recognizable
Consider a table storing metric geometry with a GiST index. If requests arrive as longitude and latitude, transforming the small query geometry to the table's CRS often allows reuse of that index. Transforming every stored feature can instead require an expression index or a different plan. Verify the actual plan; do not infer index use from how the SQL reads.
Similarly, asking ST_Distance for every feature and then filtering distances may do more work than using an appropriate spatial predicate first. Retrieve the bounded candidate set, then compute exact values needed for presentation. Check ordering and ties when implementing nearest-feature behavior.
Diagnose slow maps in the right layer
| Observation | Likely investigation |
|---|---|
| Fast SQL, slow API | Serialization, joins, pagination or service configuration |
| Fast API, slow browser | Payload size, geometry complexity or styling |
| Slow first request only | Cache, connection initialization or storage reads |
| Slow only at wide extent | Large result set; use scale-dependent publication |
| Inserts slowed after tuning | Index maintenance and transaction contention |
After each change, rerun the original correctness checks. Simplifying geometry or switching to tiles may improve the map but changes the output contract. Keep analytical geometry separate from display geometry when exact measurements matter. For publication design, continue with vector tiles and PMTiles.
Reference: PostGIS spatial query planning.
Sources and documentation
Next step
Continue in the Open GIS collection. For a specific project, use the total-cost calculator and request an assessment.