Reading EXPLAIN ANALYZE for Spatial Query Optimization

Reading EXPLAIN ANALYZE for spatial query optimization means verifying that PostGIS bounding-box pre-filters (&&) are hitting GiST indexes, that exact geometry predicates (ST_DWithin, ST_Intersects) run as efficient post-filters, and that actual time aligns with your API latency SLAs. Spatial queries routinely mislead developers because the PostgreSQL planner inflates costs for VOLATILE geometry functions. The truth lives in the execution node tree, Rows Removed by Filter, and buffer hit ratios. If your plan shows a Seq Scan on large geometry columns, missing Index Cond, or high shared read counts, your spatial index is either unused, poorly clustered, or bypassed by implicit type casts.

This workflow extends standard Query Plan Analysis & Index Tuning practices, but PostGIS requires explicit attention to operator selectivity, index-only scan limitations, and the mandatory two-phase evaluation pattern.

# Core Metrics That Matter for Spatial Plans

When PostgreSQL executes EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), isolate these spatial-specific signals:

Understanding the two phases is what makes the recheck line in a plan meaningful rather than alarming.

# The Two-Phase Execution Pattern

PostGIS evaluates spatial predicates in two distinct passes:

1. Bounding Box Pre-Filter (&&): The planner uses the GiST index to quickly discard geometries whose extents don’t intersect the query window. This step is cheap and index-driven.
2. Exact Geometry Post-Filter: Only candidates that pass the bounding box check are evaluated with expensive topology functions (ST_Intersects, ST_DWithin).

When reading the plan, the Index Cond line represents phase one. The Filter line represents phase two. A healthy spatial query shows a low Rows Removed by Filter count relative to the total rows scanned. If Filter removes >80% of rows, your bounding box isn’t selective enough, or you’re querying across mismatched SRIDs, forcing on-the-fly transformations that bypass the index. For deeper index mechanics, consult the official PostGIS GiST Indexing documentation.

# FastAPI Integration for Plan Diagnostics

This endpoint captures the execution plan, runs the spatial query, and returns structured diagnostics without exposing raw SQL to clients. It uses asyncpg for high-concurrency connection pooling and parses the JSON plan output safely.

from fastapi import FastAPI, HTTPException, Query
from contextlib import asynccontextmanager
import asyncpg
import json
from typing import Any, Dict, List

_pool: asyncpg.Pool | None = None

@asynccontextmanager
async def lifespan(app: FastAPI):
    global _pool
    _pool = await asyncpg.create_pool(dsn="postgresql://user:pass@localhost:5432/gisdb")
    yield
    await _pool.close()

app = FastAPI(lifespan=lifespan)

@app.get("/api/v1/venues/nearby/analyze")
async def analyze_spatial_query(
    lat: float = Query(..., ge=-90, le=90),
    lon: float = Query(..., ge=-180, le=180),
    radius_m: float = Query(1000.0, gt=0)
):
    query = """
        EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
        SELECT id, name, ST_AsText(geom)
        FROM venues
        WHERE ST_DWithin(
            geom,
            ST_SetSRID(ST_MakePoint($1, $2), 4326),
            $3
        );
    """
    
    async with _pool.acquire() as conn:
        try:
            rows = await conn.fetch(query, lon, lat, radius_m)
            # EXPLAIN (FORMAT JSON) returns a single row containing a JSON array string
            plan_json = json.loads(rows[0][0])
            
            # Extract key metrics for API response
            scan_node = plan_json[0]["Plan"]
            return {
                "plan_type": scan_node.get("Node Type"),
                "index_condition": scan_node.get("Index Cond"),
                "filter_condition": scan_node.get("Filter"),
                "rows_removed_by_filter": scan_node.get("Rows Removed by Filter", 0),
                "actual_total_time_ms": scan_node.get("Actual Total Time"),
                "shared_buffers_hit": scan_node.get("Shared Hit Blocks", 0),
                "shared_buffers_read": scan_node.get("Shared Read Blocks", 0),
                "planning_time_ms": plan_json[0]["Planning Time"],
                "execution_time_ms": plan_json[0]["Execution Time"]
            }
        except asyncpg.PostgresError as e:
            raise HTTPException(status_code=500, detail=f"Database execution failed: {e}")

A spatial plan is long, but only a handful of lines carry the diagnosis.

# Diagnosing Common Spatial Plan Failures

Implicit Type Casts Bypass Indexes If your geom column is geometry(Point, 3857) but you pass a geometry literal in 4326 without explicit casting, PostgreSQL may perform a sequential scan. Always match SRIDs in your query or create functional indexes on transformed columns.

Missing CLUSTER on High-Read Tables GiST indexes store bounding boxes, but heap pages remain physically scattered. Over time, shared read counts climb as the database performs random I/O. Run CLUSTER venues USING venues_geom_idx; periodically to physically reorder heap rows to match the index order. This dramatically improves buffer hit ratios for hotspot queries.

Index-Only Scans Are Rare for Geometries Unlike B-tree indexes, GiST indexes cannot satisfy Index Only Scans for geometry columns because the index stores compressed bounding boxes, not full geometries. The heap must be visited for exact evaluation. Focus on minimizing Rows Removed by Filter rather than chasing index-only optimizations.

Buffer Exhaustion Under Load Spatial indexes easily exceed default shared_buffers. When shared read dominates shared hit, your API latency will spike during concurrent requests. Monitor pg_stat_user_indexes and scale memory allocation or implement application-level caching for static spatial boundaries. For broader strategies on reducing database round-trips and caching hot query paths, review High-Performance Caching & Query Optimization.

Distinguishing these four states is the practical goal of reading a plan at all.

# What the estimate tells you that the timing does not

The most useful line in a spatial plan is often not the duration but the gap between estimated and actual row counts. PostgreSQL chooses a plan from the estimate; if the estimate is wrong the plan is wrong, and the timing merely records how wrong.

For spatial predicates the estimate comes from PostGIS statistics gathered by ANALYZE — a sample of geometry bounding boxes summarised into a histogram of the data’s spatial distribution. When those statistics are missing or stale, the planner falls back to a fixed selectivity guess, and on a bounding-box predicate that guess is usually far too pessimistic. The visible symptom is a plan that chooses a sequential scan or a materialised hash join over an index scan, on a query that would have been fast either way six months ago.

The rule of thumb is that estimated and actual rows should agree within about an order of magnitude. Beyond that, the fix is almost never to rewrite the query:

-- Refresh the spatial statistics for one table
ANALYZE features;

-- Increase the sample size for a highly skewed geometry column
ALTER TABLE features ALTER COLUMN geom SET STATISTICS 500;
ANALYZE features;

Raising the statistics target costs a slower ANALYZE and a larger histogram, and it pays for itself on any table where the data is unevenly distributed — which describes almost every real spatial dataset, since features cluster around towns, roads and coastlines rather than spreading uniformly.

# Buffers are the other half of the story

EXPLAIN (ANALYZE, BUFFERS) reports how many blocks came from the cache and how many from disk. Two runs of the same query with identical plans can differ by an order of magnitude purely on that split, which is why a plan captured on a warm cache says nothing useful about production at 03:00.

Read shared hit and shared read together: a plan dominated by reads is describing an index that no longer fits in memory, and no amount of query rewriting will fix it. That is a capacity or a partitioning conversation, not a tuning one. Conversely, a plan that is almost all hits and still slow is doing genuine computational work — usually a geometry operation on complex shapes — and that is where simplification or a pre-aggregated table earns its keep.

The practical habit is to capture plans twice: once cold, immediately after a restart or a cache drop, and once warm. The difference between the two is the size of the problem that a cache is currently hiding.

# What the estimate tells you that the timing does not

The most useful line in a spatial plan is often not the duration but the gap between estimated and actual row counts. PostgreSQL chooses a plan from the estimate; if the estimate is wrong the plan is wrong, and the timing merely records how wrong.

For spatial predicates the estimate comes from PostGIS statistics gathered by ANALYZE — a sample of geometry bounding boxes summarised into a histogram of the data’s spatial distribution. When those statistics are missing or stale, the planner falls back to a fixed selectivity guess, and on a bounding-box predicate that guess is usually far too pessimistic. The visible symptom is a plan that chooses a sequential scan or a materialised hash join over an index scan, on a query that would have been fast either way six months ago.

The rule of thumb is that estimated and actual rows should agree within about an order of magnitude. Beyond that, the fix is almost never to rewrite the query:

-- Refresh the spatial statistics for one table
ANALYZE features;

-- Increase the sample size for a highly skewed geometry column
ALTER TABLE features ALTER COLUMN geom SET STATISTICS 500;
ANALYZE features;

Raising the statistics target costs a slower ANALYZE and a larger histogram, and it pays for itself on any table where the data is unevenly distributed — which describes almost every real spatial dataset, since features cluster around towns, roads and coastlines rather than spreading uniformly.

# Buffers are the other half of the story

EXPLAIN (ANALYZE, BUFFERS) reports how many blocks came from the cache and how many from disk. Two runs of the same query with identical plans can differ by an order of magnitude purely on that split, which is why a plan captured on a warm cache says nothing useful about production at 03:00.

Read shared hit and shared read together: a plan dominated by reads is describing an index that no longer fits in memory, and no amount of query rewriting will fix it. That is a capacity or a partitioning conversation, not a tuning one. Conversely, a plan that is almost all hits and still slow is doing genuine computational work — usually a geometry operation on complex shapes — and that is where simplification or a pre-aggregated table earns its keep.

The practical habit is to capture plans twice: once cold, immediately after a restart or a cache drop, and once warm. The difference between the two is the size of the problem that a cache is currently hiding.

Keep the captured plans somewhere durable — a comment on the ticket, a file in the repository — rather than in a terminal scrollback. A plan from six months ago is the only reliable way to answer whether today’s behaviour is a regression or has always been like this.

# Validation Checklist

Before shipping spatial endpoints, verify:

• EXPLAIN shows Index Cond with && operator
• Rows Removed by Filter is < 30% of total scanned rows
• Actual Total Time matches your FastAPI p95 SLA
• shared hit > shared read on production workloads
• No implicit SRID conversions in the predicate

Reading execution plans for spatial workloads requires ignoring planner cost estimates and focusing on actual buffer behavior and post-filter efficiency. When bounding-box selectivity is high and heap access is localized, PostGIS scales linearly even on multi-million-row tables.