Spatial query benchmarks are easy to make impressive. Put a GiST index on a small table, run the same bounding-box query repeatedly and report the warm median. The number may be correct and the conclusion may still be useless.
A production map search includes viewport shape, data skew, result limits, connection behavior, serialization and the work required before the first request can run. The benchmark has to preserve those constraints.
Start with the request path
Measure the operation the product performs, not a generic distance query. For a draggable map, that usually means converting the visible viewport into a bounding box, applying business predicates and returning a capped result set.
The SQL can stay intentionally boring:
SELECT id, title, latitude, longitude
FROM job_ad_location
WHERE active = true
AND coordinates && ST_MakeEnvelope($1, $2, $3, $4, 4326)
ORDER BY published_at DESC
LIMIT 250;The bounding-box operator can use the spatial index. The other predicates and ordering still influence whether the database reads a small, useful set of pages or filters a large intermediate result.
Build a representative dataset
Uniform random points are convenient and usually misleading. Real geographic data clusters around cities, transport corridors and borders. Popular viewports are also not uniformly distributed.
A useful fixture set includes:
- dense urban boxes with many more matches than the result limit;
- sparse regional boxes;
- viewports crossing the antimeridian if the product can reach it;
- rows excluded by common business predicates;
- the actual ratio of active to inactive records;
- realistic row width and joined data.
Run cold enough to observe I/O behavior and warm enough to represent steady traffic. Keep those populations separate in the report.
Read the plan, not only the clock
Latency tells you what happened once. The plan helps explain what will change as the table, cache and predicates change.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id
FROM job_ad_location
WHERE active = true
AND coordinates && ST_MakeEnvelope(10.10, 36.70, 10.30, 36.90, 4326)
LIMIT 250;Record at least:
| Evidence | Why it matters |
|---|---|
| Estimated vs actual rows | Reveals selectivity errors |
| Shared hit and read blocks | Separates cache effects from CPU work |
| Rows removed by filter | Shows whether the index is selective enough |
| Planning time | Matters for high-frequency, short queries |
| Returned bytes | Connects database work to API latency |
Save the plans next to the benchmark inputs. A spreadsheet of p50 and p95 values without plans is difficult to investigate later.
Measure the architecture
The database query may be faster than an in-memory index and still produce a slower request because the API opens too many connections or serializes unnecessary fields. The inverse is also possible: an in-memory lookup is fast after initialization but introduces a large warm-up dependency and stale snapshots.
Measure four boundaries:
- database execution;
- database round trip through the pool;
- API handler including policy and serialization;
- user-visible request from the target network.
Also measure startup and refresh behavior. Removing a process-local spatial snapshot can reduce operational complexity even if the median query changes very little. Performance work is architecture work when it changes what the system must preload, refresh and keep consistent.
The benchmark is complete when it can answer a decision, not when it produces the smallest number.