pg_stat_statements for Spatial Workloads

Within Spatial Performance Monitoring & Observability, pg_stat_statements is the instrument you reach for first, because it is the only source that tells you which spatial statements actually consume your database’s time rather than which ones you assume do. On a ride-hailing dispatch platform the query mix is deceptive: a single unbounded analytics query might look alarming in a log, while the real cost lives in a two-millisecond ST_DWithin lookup executed millions of times an hour against driver_positions. This page shows how to enable the extension correctly, filter it down to spatial calls, and read the timing columns so you rank hotspots by their true cumulative impact.

Prerequisites and Infrastructure Validation

pg_stat_statements is a contrib module that hooks the query executor, so it cannot be loaded on the fly the way a pure-SQL extension can. It must be present in shared_preload_libraries, which is only read at server start.

Add the library and restart

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000     # distinct statements to retain
pg_stat_statements.track = top     # track top-level statements
pg_stat_statements.track_utility = off
pg_stat_statements.save = on       # persist across restarts

After editing postgresql.conf, restart the instance. On a managed platform the equivalent is a parameter-group change plus a reboot; confirm the setting took effect before continuing:

SHOW shared_preload_libraries;

Create the extension

The CREATE EXTENSION step is online and per-database. Run it in the database your dispatch service connects to:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Confirm it is collecting

A quick sanity check that the view is populated and tracking timing:

SELECT count(*) AS statements_tracked,
       (SELECT setting FROM pg_settings WHERE name = 'pg_stat_statements.max') AS max_slots
FROM pg_stat_statements;

If statements_tracked grows as traffic flows, the module is live. If it stays at a handful of rows despite heavy load, track may be set to none or the extension was created in a different database than the one serving queries.

Confirm the spatial baseline

Because you will filter on function names, it helps to confirm PostGIS is present and that the hot table carries the SRID you expect:

SELECT PostGIS_Full_Version();

SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name IN ('driver_positions', 'pickup_requests', 'trips');

Every geometry here is geometry(Point,4326). Keeping that consistent matters for the profiling that follows: a query that reprojects geom into another SRID inside the predicate shows up as its own normalised statement, and mixing SRIDs is one of the patterns you are trying to catch.

Core Execution Workflow

Step 1 — Filter the View Down to Spatial Calls

The raw view contains every statement the server runs, including internal catalog queries. Narrow it to spatial work with an ILIKE filter on the function names your dispatch code uses:

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric / 1000, 1) AS total_sec,
    round(mean_exec_time::numeric, 3)         AS mean_ms,
    rows,
    left(regexp_replace(query, '\s+', ' ', 'g'), 90) AS query_sample
FROM pg_stat_statements
WHERE query ILIKE '%st_dwithin%'
   OR query ILIKE '%st_distance%'
   OR query ILIKE '%st_intersects%'
   OR query LIKE '%<->%'
ORDER BY total_exec_time DESC
LIMIT 25;

The <-> clause captures KNN nearest-driver queries, which are the other half of a dispatch workload alongside ST_DWithin radius filters. The regexp_replace collapses whitespace so the sample is readable on one line.

Step 2 — Read the Timing Columns Correctly

pg_stat_statements reports cumulative values since the last reset. The columns that drive decisions are:

The trap is ordering only by mean_exec_time. A nightly heatmap export over all of trips may top that list at 900 ms, but if it runs twice a day its total_exec_time is trivial. The ST_DWithin dispatch lookup at 2.4 ms mean but four million calls dominates cumulative time and is where a millisecond saved is worth the most. Compute a share-of-total column to make this explicit:

SELECT
    queryid,
    calls,
    round(mean_exec_time::numeric, 3) AS mean_ms,
    round(total_exec_time::numeric / 1000, 1) AS total_sec,
    round(100.0 * total_exec_time
          / sum(total_exec_time) OVER (), 1) AS pct_of_total
FROM pg_stat_statements
WHERE query ILIKE '%st_dwithin%' OR query LIKE '%<->%'
ORDER BY total_exec_time DESC
LIMIT 15;

Step 3 — Separate Cache-Bound from CPU-Bound Statements

Two spatial statements with the same mean time can have completely different causes. Add a hit-ratio column so you can tell whether a slow statement is grinding geometry on the CPU or waiting on disk:

SELECT
    queryid,
    calls,
    round(mean_exec_time::numeric, 3) AS mean_ms,
    round(100.0 * shared_blks_hit
          / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
    shared_blks_read
FROM pg_stat_statements
WHERE query ILIKE '%st_dwithin%' OR query LIKE '%<->%'
ORDER BY total_exec_time DESC
LIMIT 15;

A proximity statement with cache_hit_pct near 100 but a high mean is CPU-bound — likely evaluating the exact ST_Distance recheck on too many candidates, which points at index selectivity. A statement with a sagging hit ratio is paying disk I/O, which points at cache pressure or a bloated index inflating the working set.

Step 4 — Snapshot and Reset Around Deploys

Because the counters are cumulative, a single reading blends every plan the statement has ever used. Reset after each deployment so the numbers describe the code that is actually running:

SELECT pg_stat_statements_reset();

For trend data, snapshot the view on an interval and difference consecutive snapshots. A psycopg poller that captures the spatial slice is enough to feed a dashboard:

import psycopg

SNAPSHOT_SQL = """
    SELECT queryid, calls, total_exec_time, mean_exec_time,
           rows, shared_blks_hit, shared_blks_read
    FROM pg_stat_statements
    WHERE query ILIKE '%st_dwithin%'
       OR query ILIKE '%st_distance%'
       OR query LIKE '%<->%'
    ORDER BY total_exec_time DESC
    LIMIT 50;
"""

def take_snapshot(dsn: str) -> dict[int, dict]:
    """Return spatial statements keyed by queryid for delta computation."""
    with psycopg.connect(dsn, connect_timeout=5) as conn:
        with conn.cursor(row_factory=psycopg.rows.dict_row) as cur:
            cur.execute(SNAPSHOT_SQL)
            return {row["queryid"]: row for row in cur.fetchall()}


def delta(prev: dict[int, dict], curr: dict[int, dict]) -> list[dict]:
    """Per-interval rate: calls and total time added since the previous poll."""
    out = []
    for qid, now in curr.items():
        before = prev.get(qid)
        if not before:
            continue
        d_calls = now["calls"] - before["calls"]
        d_total = now["total_exec_time"] - before["total_exec_time"]
        if d_calls <= 0:
            continue
        out.append({
            "queryid": qid,
            "interval_calls": d_calls,
            "interval_total_ms": round(d_total, 1),
            "interval_mean_ms": round(d_total / d_calls, 3),
        })
    return sorted(out, key=lambda r: r["interval_total_ms"], reverse=True)

The per-interval mean derived from deltas is far more useful than the lifetime mean, because it reacts to a regression the moment it appears instead of being diluted by history. Drilling from a flagged queryid down to the exact statement text, the endpoint that issued it, and a scheduled snapshot table is the subject of finding slow ST_ function calls with pg_stat_statements.

Performance Considerations

The extension itself adds a small, bounded overhead — a hash lookup and counter increment per statement — that is negligible next to the cost of geometry evaluation. The settings that matter are about retention, not speed.

pg_stat_statements.max caps how many distinct normalised statements are retained. When the table is full, the least-executed entries are evicted, and a low ceiling on a diverse workload can silently drop your spatial statements before you read them. Ten thousand is a safe floor for a dispatch service; check the deallocation counter to see whether eviction is happening:

SELECT dealloc, stats_reset
FROM pg_stat_statements_info;

A steadily climbing dealloc means the ceiling is too low and rare-but-important statements are being lost between polls. Either raise pg_stat_statements.max or shorten your snapshot interval so you capture entries before eviction.

One normalisation subtlety affects spatial profiling specifically. Two queries that differ only in literal coordinates normalise to the same queryid, which is what you want. But a query that passes a geometry as a bound parameter and one that inlines ST_GeomFromText('POINT(-73.98 40.7)', 4326) as a literal may normalise differently depending on server version, splitting one logical pattern across two rows. Parameterising geometry from the application — which the Python examples above do — keeps a pattern collapsed into a single, readable statistic and, as query plan analysis with EXPLAIN shows, also keeps the planner’s type inference clean so the GiST index stays eligible.

Common Failure Modes and Fixes

Extension Created but the View Is Empty

Symptom: SELECT count(*) FROM pg_stat_statements returns a tiny, static number despite heavy traffic.

Diagnosis:

SHOW shared_preload_libraries;      -- must list pg_stat_statements
SHOW pg_stat_statements.track;      -- must be 'top' or 'all', not 'none'
SELECT current_database();          -- must match the app's database

Fix: If shared_preload_libraries does not list the module, add it and restart — CREATE EXTENSION alone does not load the hook. If track is none, set it to top. If the extension lives in a different database than the one serving queries, create it in the correct database.

Spatial Statements Missing After a Busy Period

Symptom: A proximity statement you saw yesterday has vanished.

Diagnosis: Rising dealloc in pg_stat_statements_info means the retention table overflowed and evicted low-frequency entries.

Fix: Raise pg_stat_statements.max, reduce statement variety by parameterising literals, or snapshot more frequently so entries are captured before eviction.

One Pattern Split Across Many queryids

Symptom: The same ST_DWithin shape appears as dozens of rows, each with a low call count, so no single row looks expensive.

Diagnosis: The application is inlining coordinates or geometry as literal SQL text instead of binding parameters, defeating normalisation. Inspect the query column — if you see raw coordinates in the stored text, they were not parameterised.

Fix: Bind geometry and radius as query parameters (%s in psycopg, $1 in the SQL) so every call collapses into one queryid. This also improves plan caching and index eligibility.

Timing Columns Are All Zero

Symptom: total_exec_time and mean_exec_time are zero while calls increments.

Diagnosis: Execution-time tracking depends on track_io_timing and the platform clock; on some builds pg_stat_statements timing requires compute_query_id = on.

Fix: Confirm compute_query_id is on (or auto), and verify the server was restarted after adding the module — timing hooks only attach at load.

Verification

After enabling and filtering, confirm the pipeline works end to end.

The module is loaded and tracking:

SELECT
    (SELECT setting FROM pg_settings WHERE name = 'shared_preload_libraries') AS preload,
    (SELECT setting FROM pg_settings WHERE name = 'pg_stat_statements.track') AS track,
    count(*) AS rows_tracked
FROM pg_stat_statements;

preload must contain pg_stat_statements, track should be top, and rows_tracked should climb under load.

Your dispatch pattern is captured as one row: run a few proximity lookups with different coordinates, then confirm they aggregate:

SELECT queryid, calls, round(mean_exec_time::numeric, 3) AS mean_ms
FROM pg_stat_statements
WHERE query ILIKE '%st_dwithin%'
ORDER BY calls DESC
LIMIT 5;

A single high-calls row for the ST_DWithin shape confirms normalisation is working. Several near-identical rows with low counts indicate literals are not being parameterised.

Deltas produce sane rates: take two snapshots a minute apart with the Python poller above and confirm interval_calls matches the request rate your application logs report for the dispatch endpoint. When those two numbers agree, the database-side and application-side views of the workload are consistent and you can trust the dashboards built on top.

Frequently Asked Questions

Does enabling pg_stat_statements require a restart?

Yes. The module hooks the executor and must be loaded through shared_preload_libraries, which only takes effect at server start. The CREATE EXTENSION step afterward is online, but the initial library load needs a restart, so plan it into a maintenance window rather than reaching for it mid-incident. On managed platforms the equivalent is a parameter-group change followed by a reboot.

Why do all my ST_DWithin queries collapse into one row?

That is normalisation working as intended. The extension replaces literal constants such as coordinates and radii with placeholders and hashes the resulting query shape into one queryid. Every dispatch proximity lookup with different points aggregates into a single row, which is exactly what lets you see the pattern’s total cost instead of drowning in per-request noise. If a shape splits across many rows, the application is inlining literals instead of binding parameters.

Should I order spatial statements by mean or total execution time?

Use both for different questions. Order by total_exec_time to find where the fleet spends cumulative wall-clock time — usually the high-frequency proximity path. Order by mean_exec_time to find individual heavy statements such as an unbounded-radius report. The statement that most deserves attention is often high-total but modest-mean: cheap on each call yet executed millions of times, so it quietly dominates the database’s total work.

Related Topics

• Spatial Performance Monitoring & Observability — parent section on instrumenting, measuring, and alerting on PostGIS performance
• Finding Slow ST_ Function Calls With pg_stat_statements — the exact query and psycopg helper to surface and map the slowest spatial statements
• Detecting GiST Index Bloat — sibling topic explaining why a profiled statement’s I/O may be climbing
• Autovacuum Tuning for Geometry Tables — sibling topic on the vacuum and statistics health behind stable plans
• Query Plan Analysis with EXPLAIN — take a flagged statement and confirm at the plan level why it is slow