postgis-query-explain

Paste a PostGIS EXPLAIN plan. Find out which node is eating your latency, and what index or rewrite would fix it.

A zero-dependency static web app that turns the output of EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) into a readable time breakdown, a navigable plan tree, and a prioritised list of concrete, spatial-aware findings — each with copy-pasteable SQL.

No build step. No npm install. No server. No account. The plan never leaves your browser.

▶ Try the live demo — or open the example above already analysed.

---

What it does

You give it this:

[{"Plan": {"Node Type": "Seq Scan", "Relation Name": "parcels", "Plan Rows": 833,
  "Actual Rows": 18432, "Actual Total Time": 4180.221, "Filter": "st_intersects(p.geom, ...)",
  "Rows Removed by Filter": 2481568, "Shared Read Blocks": 132456, ...}}]

It gives you this:

1. An overview strip — execution time, planning time, node count, rows returned, buffer hit/read split, and the single slowest node by exclusive time.
2. A findings panel, grouped critical → high → medium → info. Every finding names the node it fired on, explains why the pattern is expensive in plain language, shows the numbers it based that on, and gives you SQL you can copy straight into psql.
3. A flat time profile — one horizontal bar per node, ranked by exclusive time (the node's own cost, with its children's time subtracted). This is what answers "where do I start?".
4. An expandable plan tree — estimated versus actual rows with a colour-coded error badge, loop counts, per-node buffers, filter and index conditions, sort methods, heap block counts. Fully keyboard navigable.

Concretely, on the shipped samples/seq-scan-bbox.json it reports:

=== seq-scan-bbox.json  (json, 1 node)
   [critical] seq-scan-spatial-filter    @ Seq Scan on parcels
   [high    ] row-estimate-way-off       @ Seq Scan on parcels
   [medium  ] buffers-read-heavy         @ Seq Scan on parcels
   [medium  ] parallelism-unused         @ Seq Scan on parcels
   [medium  ] unbounded-result-set       @ Seq Scan on parcels
   [info    ] brin-candidate             @ Seq Scan on parcels

A full walkthrough of that plan, with the actual generated text, is in the worked example below.

It understands two input dialects:

• FORMAT JSON (recommended) — parsed exactly. Array-rooted or object-rooted, with or without psql's QUERY PLAN header, ASCII table rules, (N rows) footer, markdown code fences, or a {"QUERY PLAN": "<json string>"} wrapper.
• Classic text EXPLAIN ANALYZE — parsed best-effort from the indentation tree and the (cost=…) / (actual time=…) annotations. Enough for most rules, but a few metrics (sort space, exact heap block counts) are only reliably available in JSON.

Why it exists

The plan is right there in the output, and it already contains the answer. The problem is that reading it correctly requires holding several non-obvious conventions in your head at once:

• Actual Total Time is per loop, not total. A node showing 5.882 ms with loops=1842 cost you 10.8 seconds. The same is true of Actual Rows. This is the single most common misreading of an EXPLAIN plan, and it is why a genuinely catastrophic nested loop can look cheap.
• Times are inclusive. A node's reported time includes all of its children. Ranking nodes by their reported time just tells you the tree is a tree. What you want is exclusive time.
• Buffer counters are cumulative too, so summing Shared Hit Blocks over every node double-counts massively. The root node already holds the total.
• Row estimates matter more than they look. A node that returns 50,000 rows where the planner expected 833 is not a small problem with that node — every join method and sort decision above it was chosen from that wrong number.

On top of that, PostGIS adds failure modes that generic plan viewers know nothing about. They will happily tell you a sequential scan is slow. They will not tell you that ST_Distance(geom, $1) < 5000 can never use an index and ST_DWithin(geom, $1, 5000) can, that a Filter: st_intersects(...) with no matching Index Cond usually means an SRID mismatch, that ST_Transform(geom, 3857) in a predicate silently disables the index on geom, or that ORDER BY geom <-> point resolved by a Sort node means your KNN query is sorting the entire candidate set instead of walking an index outward from the query point.

This tool encodes those specifics. It is opinionated about PostGIS on purpose.

It is also deliberately client-side only. Query plans leak schema names, column names, literal geometries and business logic. Pasting one into a hosted analyser is a data-egress decision that often has not been thought through. Here there is nothing to think through: there is no backend, and the shareable-link feature encodes the plan into the URL fragment, which browsers never send to a server.

Try it locally

The hosted demo is the same code served from GitHub Pages, and analysis runs entirely client-side there too — nothing you paste is uploaded anywhere. Run it locally if you would rather not paste production plans into a page you did not serve yourself.

There is nothing to install. Clone the repository and serve it:

git clone https://github.com/geospatial-api/postgis-query-explain.git
cd postgis-query-explain
node dev-server.mjs

postgis-query-explain serving /path/to/postgis-query-explain
  → http://127.0.0.1:8080/
Press Ctrl+C to stop.

Open that URL and either paste your own plan or pick one from the Load a sample plan… dropdown.

node dev-server.mjs --port 3000        # different port
node dev-server.mjs --host 0.0.0.0     # reachable from another machine on your LAN

dev-server.mjs uses only the Node standard library. Any other static file server works equally well — python3 -m http.server, npx serve, nginx, whatever you already have.

Why does it need a server at all? The app is built from native ES modules, and browsers refuse to load ES modules over file:// URLs for security reasons. Opening index.html by double-clicking it will show an explanatory message rather than a blank page. Any HTTP origin — including 127.0.0.1 — works fine.

Getting a plan out of PostgreSQL

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, ST_AsGeoJSON(geom)
FROM parcels
WHERE ST_Intersects(geom, ST_MakeEnvelope(-35.0, 44.0, -31.5, 46.0, 4326));

ANALYZE actually runs the query, so use a read-only replica or wrap it in a transaction you roll back if the statement writes. BUFFERS is what makes the cache-related findings possible, and FORMAT JSON is what makes the rest of the metrics reliable.

To capture it straight to a file:

psql -X -A -t -d yourdb -f query.sql > plan.json

Deploying

The repository root is the site. There is no build output because there is no build.

GitHub Pages — a ready workflow is at .github/workflows/pages.yml. Enable Pages for the repository (Settings → Pages → Source: GitHub Actions) and push to main. The workflow copies index.html, src/ and samples/ into an artifact, adds a .nojekyll marker so Pages does not try to process the files, and deploys.

Any static host — upload these and nothing else:

index.html
src/app.js  src/parser.js  src/rules.js  src/share.js  src/format.js  src/styles.css
samples/*.json  samples/*.txt

Netlify, Cloudflare Pages, S3 + CloudFront, or a directory behind nginx all work with no configuration. The only server-side requirement is that .js files are served with a JavaScript content type, which every static host does by default.

Internally — because it is fully static and stores nothing, it is safe to drop behind an internal auth proxy for a team that would rather not paste production plans into a public site at all.

How the analysis works

Stage 1 — parse (src/parser.js)

The input is first stripped of decoration: QUERY PLAN headers, ASCII rules, leading pipes, (N rows) footers, markdown fences. What survives is either scanned for a balanced JSON document (so leading prose and trailing noise are harmless) or handed to the text parser.

The JSON path locates the object holding the Plan key — breadth-first, so it copes with bare arrays, {"QUERY PLAN": [...]} wrappers, and plans nested inside a JSON string. The text path reads the indentation of each -> marker to rebuild the tree, then attaches the Filter:, Index Cond:, Buffers: and similar attribute lines to whichever node they follow.

Both paths produce the same normalised node, with the per-loop-versus-total trap already resolved:

Field	Meaning
actualRows, planRows	per loop, exactly as PostgreSQL reports them
totalRows, estimatedRows	multiplied out by loops — the numbers you actually want
actualTotalTime	per loop, in ms
totalTime	actualTotalTime × loops
exclusiveTime	totalTime minus the sum of the children's totalTime
errorRatio, errorDirection	symmetric estimation error, and which way it went
sharedHitBlocks, sharedReadBlocks	cumulative, as reported; exclusiveBuffers() de-cumulates
filter, indexCond, recheckCond, orderBy, sortKey	the raw expressions
lossyBlocks, exactBlocks, sortMethod, workersLaunched	when reported

Exclusive time is clamped at zero. It can legitimately go slightly negative from measurement noise, and substantially negative for a Limit above a CTE, because a CTE's time is counted both in its own subtree and inside the consumer that reads it. Clamping is honest here; inventing a correction would not be.

Estimation error is symmetric and floors both sides at 1 row:

ratio = max(estimated, 1) / max(actual, 1)   when the planner over-estimated
        max(actual, 1) / max(estimated, 1)   when it under-estimated

Flooring keeps a 0-row node from producing an infinity in the UI. A node estimated at 1 row that returned 5,000 scores 5,000× — which is the right order of alarm.

Stage 2 — apply rules (src/rules.js)

Each rule is an independent function over the whole normalised plan. It returns zero or more findings, each carrying a severity, the node it fired on, an explanation, an action, evidence rows, and a deep link to the relevant guide.

Three design choices are worth calling out, because they are what keeps the output usable:

• Thresholds, everywhere. A rule that fires on every plan is worse than no rule. The spatial sequential-scan rule ignores tables under 500 rows. The BRIN hint needs a million. The buffer rule needs 5,000 block accesses before it will say anything. On a small, already-fast query the tool correctly says nothing at all.
• Suppression of duplicates and known artifacts. A bad row estimate propagates up the tree, so it is reported only at the deepest node where it originates, not once per level. An over-estimate beneath a LIMIT is not reported at all, because a top-N sort returning fewer rows than estimated is the LIMIT working correctly. A CTE is reported on its consumer, not on both the consumer and the definition. A Filter: st_intersects(...) sitting above a Recheck Cond: (geom && …) is the correct shape for a healthy GiST plan and is never flagged.
• Rule failures are contained. If one rule throws, it is reported as an error against that rule id and the rest still run. A half-working analyser beats a blank page.

Findings are then sorted by severity, and within a severity by the exclusive time of the node they point at — so the most expensive critical problem is always first.

Rule reference

20 rules. Every id is stable and safe to reference in a bug report.

Spatial access paths

Rule id	Severity	Fires when
seq-scan-spatial-filter	critical	A Seq Scan over 500+ rows has a PostGIS predicate as its Filter. Suggests a CREATE INDEX … USING GIST (geom) naming the actual table and best-guess geometry column, plus the follow-up ANALYZE.
st-distance-threshold	critical	The filter contains ST_Distance(…) < x, which is a scalar comparison and can never use an index. Gives the ST_DWithin rewrite.
spatial-predicate-not-index-backed	high	ST_Intersects / ST_DWithin / ST_Contains and friends appear as a post-scan Filter on an index scan with no spatial index involved anywhere. Usually an SRID mismatch or a missing GiST index; suggests a Find_SRID() check.
function-wrapped-geometry	high	ST_Transform / ST_Buffer / ST_Centroid / ST_Simplify and similar wrap the column in a predicate, defeating the index on it. Suggests a GENERATED ALWAYS AS … STORED column or a matching expression index.
knn-order-by-without-index	high	A Sort node is keyed by the <-> or <#> distance operator, meaning the whole candidate set was materialised and sorted instead of being walked in order from an index.
spgist-candidate-for-points	info	An index scan over what looks like point geometries is using GiST. SP-GiST partitions space rather than storing degenerate bounding boxes, and is often smaller and faster — presented as a benchmark suggestion, not a directive.
brin-candidate	info	A Seq Scan reads a million-plus rows with a filter. If the table is append-only and physically correlated with the filter column, BRIN costs kilobytes where a B-tree costs gigabytes. Includes the pg_stats correlation check to run first.

Row estimates and filtering

Rule id	Severity	Fires when
row-estimate-way-off	high	Actual and estimated rows differ by 10× or more, at the deepest node where the error originates, excluding LIMIT-truncated over-estimates. Suggests ANALYZE and a raised statistics target.
high-filter-discard-rate	high	10,000+ rows were read and 90%+ of them were discarded by the Filter. Suggests a covering or partial index.
bitmap-heap-lossy	medium	A Bitmap Heap Scan reports lossy heap blocks: the TID bitmap outgrew work_mem and degraded to page granularity, forcing an exact-geometry recheck of every tuple on those pages.

Join and execution strategy

Rule id	Severity	Fires when
nested-loop-high-iterations	high	A branch under a Nested Loop ran 1,000+ times. Points at the outer-side estimate as the real culprit and offers enable_nestloop = off strictly as a diagnostic.
sort-spills-to-disk	high	Sort Method reports an external merge on disk. Computes a concrete work_mem suggestion from the reported spill size and recommends scoping it per-role rather than globally.
parallelism-unused	medium	A 200,000-row-plus, 100 ms-plus Seq Scan ran single-threaded with no Gather above it. Notes that a non-PARALLEL SAFE function anywhere in the query disables parallelism for the entire plan.
cte-materialization-overhead	medium	A CTE Scan produced 50,000+ rows or cost 50 ms+. Explains the pre-12 optimisation fence and the NOT MATERIALIZED option.
trigger-overhead	medium	Trigger time is 10%+ of execution. Suggests generated columns and statement-level triggers over per-row geometry validation.
planning-time-dominates	info	Planning took longer than execution. Points at prepared statements and the PgBouncer settings needed to keep them working in transaction pooling mode.

Result shape and I/O

Rule id	Severity	Fires when
unbounded-result-set	medium	The top of the plan emits 5,000+ rows with no Limit anywhere. Gives a keyset pagination template.
deep-offset-pagination	high	A Limit discarded far more rows than it returned — the signature of a large OFFSET, whose cost grows without bound as clients page deeper.
aggregate-over-large-scan	medium	An aggregate consumed 500,000+ input rows, reported once at the outermost aggregate of a chain. Gives a materialised view template with the unique index that REFRESH … CONCURRENTLY requires.
buffers-read-heavy	medium	40%+ of 5,000+ buffer accesses missed shared_buffers. Includes a pg_buffercache query to see what is actually resident.

Each rule links to the guide that covers the underlying technique in depth — see Further reading.

A worked example

Load samples/seq-scan-bbox.json. It is a single-node plan: a bounding-box query against a 2.5-million-row parcels table.

The plan, trimmed to the fields that matter:

{
  "Node Type": "Seq Scan",
  "Relation Name": "parcels",
  "Plan Rows": 833,
  "Actual Total Time": 4180.221,
  "Actual Rows": 18432,
  "Actual Loops": 1,
  "Filter": "st_intersects(p.geom, '0103000020E61000000100...'::geometry)",
  "Rows Removed by Filter": 2481568,
  "Shared Hit Blocks": 8192,
  "Shared Read Blocks": 132456
}

The overview reads: execution time 4.23 s, planning time 0.412 ms, 1 plan node, 18.4k rows returned, buffers 8,192 hit / 132,456 read.

Note the ratio in that last figure before reading any further — 94% of the pages this query touched came from disk. That is already the story.

Finding 1 — seq-scan-spatial-filter (critical)

PostgreSQL read all 2,500,000 rows of parcels and evaluated the PostGIS predicate on every one of them, keeping only 18,432. A GiST index over the geometry column lets the planner discard almost all of those rows using bounding boxes before any exact geometry maths runs.

Evidence
Rows scanned	2,500,000
Rows kept	18,432
Time in this node	4.18 s

CREATE INDEX CONCURRENTLY idx_parcels_geom_gist
    ON parcels USING GIST (geom);

ANALYZE parcels;

Finding 2 — row-estimate-way-off (high)

The planner estimated 833 rows here but got 18,432 — far more than expected, a factor of 22.1x. Every decision above this node was made from that wrong number. Spatial predicates are especially prone to this: the planner estimates them from a geometry histogram that goes stale quickly after bulk loads.

ANALYZE parcels;

-- If that is not enough, collect finer-grained stats:
ALTER TABLE parcels ALTER COLUMN geom SET STATISTICS 1000;
ANALYZE parcels;

Finding 3 — buffers-read-heavy (medium)

132,456 of 140,648 buffer accesses (94.2%) missed shared_buffers and had to go to the OS cache or the disk. At the default 8kB block size this query touched roughly 1.1 GB of pages.

Finding 4 — parallelism-unused (medium) — a 4.18 s scan over 2.5M rows ran on one core with no Gather node above it.

Finding 5 — unbounded-result-set (medium) — 18,432 rows are being returned with no LIMIT; links to keyset pagination.

Finding 6 — brin-candidate (info) — at 2.5M rows, if the table is append-only and correlated with the filter column, BRIN is worth measuring.

Reading it as a whole. Findings 3–6 are all consequences of finding 1. Fix the missing GiST index and the scan stops touching 1.1 GB of pages, so the buffer problem evaporates; there is no longer a large scan to parallelise; and the row estimate becomes tractable because the index gives the planner something selective to reason about. The tool ranks by severity precisely so this reads top-down: the first finding is the one to act on, and the rest are corroboration.

Try the other samples for different shapes:

• samples/knn-nearest-stores.json — nearest-store search using ST_Distance(…) < 5000 plus an ORDER BY geom <-> point resolved by a Sort. Two critical findings.
• samples/bitmap-lossy-join.json — a healthy GiST bitmap scan (correctly not flagged) that is nonetheless being driven 1,842 times by a nested loop, feeding a sort that spills 41 MB to disk.
• samples/aggregate-offset-cte.json — a hex-bin aggregate over 3.2M rows, page 421 of an OFFSET pagination scheme, with a materialised CTE.
• samples/text-format-plan.txt — classic text output with parallel workers, loops=3, and an ST_Transform wrapped around the indexed column.

Sharing a plan

Clicking Copy shareable link compresses the plan with deflate-raw via the browser's built-in CompressionStream, encodes it as URL-safe base64, and puts it in the URL fragment. Plan JSON is extremely repetitive, so this typically lands around 10–15% of the original size.

The fragment is never transmitted to a web server by any browser, so a shared link discloses the plan only to whoever you send it to.

Links are capped at 8,000 characters. Browsers themselves tolerate more, but proxies, chat clients and mail readers routinely truncate longer URLs, and a silently truncated plan is worse than a refusal — so oversized plans get an explicit error telling you to share the file instead. On a platform without CompressionStream the app falls back to plain base64 and the cap simply trips sooner.

Limitations

Worth knowing before you trust any single finding:

• It sees the plan, not your database. It cannot know which indexes already exist, the real cardinality or SRID of a column, your work_mem, or whether a table is append-only. Suggestions such as spgist-candidate-for-points and brin-candidate are explicitly framed as things to measure, not things to do.
• Column names in generated SQL are a best guess. The geometry column is inferred from the predicate text, matching common conventions (geom, the_geom, way, geography, …) and falling back to geom. Read the SQL before you run it — that is why it is shown rather than executed.
• The text parser is best-effort. Deeply unusual formatting, non-English locale output, or clients that reflow long lines can confuse it. FORMAT JSON is exact; prefer it. Sort space and exact heap block counts are only reliably available in JSON, so a few rules stay quiet on text input.
• Without BUFFERS there is no I/O analysis. buffers-read-heavy cannot fire, and the overview says "not reported" rather than pretending.
• Without ANALYZE there are no timings. A plain EXPLAIN parses fine and the structural rules still work, but the time profile is empty and anything threshold-based on time stays quiet.
• Gather nodes make exclusive time approximate. Parallel workers overlap in wall-clock time, so time attributed across a parallel subtree does not sum the way a serial one does.
• CTE time is double-counted by PostgreSQL itself, appearing in both the CTE subtree and its consumer. Exclusive time is clamped at zero rather than corrected.
• It does not parse SQL. It reasons about the plan's expression strings, not your query text. It can tell you ST_Distance(...) < x appears in a filter; it cannot rewrite your query for you.
• 20 rules is not exhaustive. No hash-batch spill detection, no partition-pruning analysis, no JIT accounting, no Index Only Scan heap-fetch analysis yet.

Testing

node --test "test/**/*.test.js"

ℹ tests 118
ℹ suites 0
ℹ pass 118
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0

No dependencies, no database, no network, no fixtures to generate. Node 20 or newer.

The suite covers four areas:

• test/parser.test.js — every shipped sample; tree shape and depth; the per-loop-to-total conversions; sort-method recombination; CTE attachment; array-rooted, object-rooted, padded, fenced and psql-wrapped JSON; exclusive-time arithmetic including the clamp-at-zero case; estimation-error symmetry and the floor-at-1 behaviour; buffer de-cumulation; the text dialect including never executed, TIMING OFF, three-level nesting, triggers and the Buffers: line; and every failure path (empty, non-string, malformed JSON, truncated JSON, JSON with no Plan, and prose).
• test/rules.test.js — a fire and a no-fire case for all 20 rules, plus the engine behaviours: severity ordering, count consistency, containment of a throwing rule, determinism across runs, the shape of every emitted finding, and the artifact suppressions (propagated estimate errors, LIMIT-truncated over-estimates, duplicated CTE findings, and the healthy bbox-then-recheck plan that must never be flagged).
• test/share.test.js — encode/decode round trips including non-ASCII; URL-safety of the payload; that compression actually engages and helps; the oversize refusal; malformed payload rejection; and the display formatters.
• test/app-wiring.test.js — static contract checks that catch the errors a DOM-less test suite otherwise misses: every named import in app.js exists in its module, every element id it looks up exists in index.html, every asset referenced by the HTML exists on disk, samples/ and the dropdown agree, no source file carries a TODO marker, package.json declares no dependencies, and every geospatial-api.com link in the repository is checked against a list of real pages so a typo cannot ship as a 404.

CI (.github/workflows/ci.yml) runs the suite on Node 20, 22 and 24, syntax-checks every file with node --check, then starts dev-server.mjs and fetches every asset to prove there are no 404s and that directory traversal is refused.

Further reading

The rule explanations deep-link into a set of guides on building production geospatial APIs with FastAPI and PostGIS. The ones most relevant to what this tool reports:

• Query plan analysis and index tuning — the long-form version of what this tool automates: reading EXPLAIN output, choosing index types, and diagnosing bad estimates.
• Bounding-box and spatial index queries — how && and the GiST index actually cooperate, and why SRID mismatches quietly disable them.
• K-nearest-neighbour routing algorithms — index-backed <-> ordering, and when SP-GiST beats GiST.
• Spatial pagination and cursor strategies — keyset pagination for spatial result sets, and why OFFSET degrades without bound.
• Materialised views for spatial aggregations — precomputing choropleths and hex-bin summaries, and refreshing them concurrently.
• Redis caching for spatial queries — what to cache above the database once the query itself is as fast as it will get.
• Connection pooling and PgBouncer setup — keeping prepared statements alive in transaction pooling mode.
• Async PostGIS transaction patterns — write paths, and moving per-row trigger work out of the transaction.

Broader context on the surrounding architecture: core geospatial API architecture with FastAPI and PostGIS, spatial resource modeling patterns, and GeoJSON versus GeoParquet serialization.

Project layout

index.html                  the whole UI, no framework
dev-server.mjs              Node-stdlib static server for local use
src/parser.js               EXPLAIN JSON + text parsing → normalised node tree
src/rules.js                the 20 rules and the analysis engine
src/format.js               display formatting (testable without a DOM)
src/share.js                deflate + base64url URL-fragment encoding
src/app.js                  DOM wiring and rendering
src/styles.css              light/dark theming, responsive layout
samples/                    four JSON plans and one text plan
test/                       node --test suites

Contributing

New rules are welcome, particularly ones grounded in a real plan you have debugged. A rule needs a stable kebab-case id, a severity, a docsUrl from the DOCS map in src/rules.js, a threshold that keeps it quiet on healthy plans, and both a fire and a no-fire test. If you can add a representative plan to samples/, better still — the wiring test will hold you to keeping the dropdown in sync.

License

MIT — see LICENSE. Copyright (c) 2026 geospatial-api.com.