Why Your PostGIS Queries Are Slow: Common Anti-Patterns and Fixes

2026-03-11

Why Your PostGIS Queries Are Slow: Common Anti-Patterns and Fixes

The Problem

PostGIS queries fail silently. The SQL is valid, the results are correct, and the application works -- until someone notices that a proximity search takes 4 seconds instead of 4 milliseconds. The difference is almost always one of four anti-patterns that look perfectly reasonable in code review but bypass GiST index usage entirely, forcing PostgreSQL to compute geometry operations against every row in the table.

The most common offender is using ST_Distance for range filtering. A query like WHERE ST_Distance(geom, point) < 1000 computes the exact distance between the query point and every geometry in the table, then filters the results. On a table with 5 million rows, that is 5 million distance calculations before a single row is returned. The equivalent ST_DWithin(geom, point, 1000) uses the GiST index to eliminate candidates by bounding-box overlap first, typically reducing the computation to a few hundred geometries. Same result, three orders of magnitude less work.

The second pattern is wrapping geometry columns in transformation functions within WHERE clauses. WHERE ST_AsText(geom) LIKE 'POINT%' forces PostgreSQL to convert every geometry to text before comparison -- no index can help. The third is implicit cross-joins without a spatial predicate -- FROM a, b WHERE ST_Distance(a.geom, b.geom) < 100 produces a cartesian product before filtering. With 10,000 rows in each table, that is 100 million distance calculations. The fourth is running spatial predicates against complex geometries with millions of vertices when a simplified version would produce the same practical result.

Each of these patterns passes code review because the SQL is correct. The performance cost only becomes apparent at production data volumes.

How to Detect It

Query pg_stat_statements to find slow spatial queries, then check the tables they reference for sequential scan dominance:

-- Find the slowest spatial queries by mean execution time
SELECT query, calls, mean_exec_time, rows, total_exec_time
FROM pg_stat_statements
WHERE query ~* 'st_distance|st_dwithin|st_contains|st_intersects|st_within'
ORDER BY mean_exec_time DESC
LIMIT 15;

-- Check spatial tables for sequential scan dominance
SELECT relname, seq_scan, idx_scan, seq_tup_read
FROM pg_stat_user_tables
WHERE relname IN (SELECT f_table_name FROM geometry_columns)
  AND seq_scan > 0
ORDER BY seq_scan DESC;

A spatial table with high seq_scan and low or zero idx_scan is a table where every spatial query is doing a full table scan. Cross-reference the slow queries from pg_stat_statements with the high-sequential-scan tables -- the overlap identifies exactly which query-table combinations need attention.

For a specific query, EXPLAIN ANALYZE confirms the problem:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM locations
WHERE ST_Distance(geom, ST_SetSRID(ST_MakePoint(-73.98, 40.75), 4326)) < 0.01;

If the output shows Seq Scan instead of Index Scan using gist, the query is bypassing the spatial index.

How myDBA.dev Shows It

Queries page showing slow spatial queries with full scan patterns

myDBA.dev's Queries page surfaces spatial anti-patterns by sorting queries by total execution time. Spatial queries using ST_Distance in a WHERE clause appear near the top -- high mean execution time, high call count, and disproportionate total time. Statement type badges and performance grades make it straightforward to spot the worst offenders without running detection queries manually.

PostGIS page showing anti-pattern detection and health warnings

The PostGIS monitoring page goes further with automatic anti-pattern detection. It analyzes spatial query patterns against known performance traps -- ST_Distance range filtering, function-wrapped geometry comparisons, unindexed cross-joins -- and flags them with severity ratings and specific fix recommendations. Health check warnings highlight tables where spatial indexes exist but queries are not using them, distinguishing between missing indexes and poorly written queries.

How to Fix It

Anti-pattern 1: ST_Distance for range queries. This is the number one spatial performance killer. ST_Distance computes exact distance for every row. ST_DWithin uses the GiST index for bounding-box elimination first.

-- BAD: computes distance against every row (sequential scan)
SELECT * FROM locations
WHERE ST_Distance(geom, query_point) < 1000;

-- GOOD: uses GiST index for bounding-box filtering first
SELECT * FROM locations
WHERE ST_DWithin(geom, query_point, 1000);

If you need the actual distance value for sorting, compute it after the index-assisted filter:

SELECT *, ST_Distance(geom, query_point) AS distance
FROM locations
WHERE ST_DWithin(geom, query_point, 1000)
ORDER BY distance;

Anti-pattern 2: Function wrapping that defeats the index. Any function applied to the geometry column in a WHERE clause prevents index usage.

-- BAD: converts every geometry to text before comparison
WHERE ST_AsText(geom) LIKE 'POINT%'

-- GOOD: uses native geometry type checking (index-compatible)
WHERE GeometryType(geom) = 'POINT'

Anti-pattern 3: Cross-join without a spatial predicate. An implicit cross-join computes the cartesian product before any spatial filtering. Rewrite as an explicit join with ST_DWithin so the planner can use spatial indexes on both tables.

-- BAD: cartesian product -- 10K x 10K = 100M distance calculations
SELECT * FROM stores, customers
WHERE ST_Distance(stores.geom, customers.geom) < 100;

-- GOOD: explicit spatial join -- GiST indexes on both tables
SELECT * FROM stores
JOIN customers ON ST_DWithin(stores.geom, customers.geom, 100);

Anti-pattern 4: Complex geometries without simplification. Running ST_Contains against a polygon with 2 million vertices is expensive even with an index, because every candidate from the bounding-box filter still requires a full vertex-by-vertex containment test. Simplify the geometry when exact boundary precision is not needed.

-- BAD: full containment test against complex polygon (millions of vertices)
SELECT * FROM points
WHERE ST_Contains(complex_region, geom);

-- GOOD: simplify first -- 0.001 degree tolerance is ~100m at mid-latitudes
SELECT * FROM points
WHERE ST_Contains(ST_Simplify(complex_region, 0.001), geom);

Choose the simplification tolerance based on your accuracy requirements. For most geofencing and regional queries, the reduced vertex count delivers identical practical results at a fraction of the computation cost.

How to Prevent It

Establish ST_Distance in a WHERE clause as a code review red flag. It is almost never the right function for range-based filtering -- ST_DWithin exists specifically for this purpose and should be the default. Automate this with a linter rule or grep in your CI pipeline:

-- Monitor for ST_Distance anti-pattern in production queries
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query ~* 'where.*st_distance'
  AND mean_exec_time > 100
ORDER BY total_exec_time DESC;

Any match is a spatial query that is almost certainly doing a sequential scan. Fix it by replacing ST_Distance < threshold with ST_DWithin, and add the pattern to your code review checklist.

Monitor spatial query performance continuously with myDBA.dev. A spatial query whose mean execution time increases over time is a query that scales linearly with table size -- the hallmark of a missing or bypassed spatial index. Catching the trend early, before the table doubles in size, turns a five-minute fix into a prevented outage.