PostgreSQL Plan Signatures: Quick Reference
A practical guide for PostgreSQL and TimescaleDB
PostgreSQL Plan Signatures: Quick Reference
Reading a PostgreSQL plan is usually a shape-recognition task, not an analytical one. Once you have seen enough of them, a Seq Scan on a 50-million-row table with a selective filter tells you what to do without thinking. Same with a Hash Join spilling to 17 batches, or a Sort going to external merge. You glance, you recognise, you fix.
This article is the cheatsheet for building that recognition. It is the reference companion to the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series — the long-form articles explain why; this one gives you the pattern → fix mapping fast. Bookmark it, open it when you have an EXPLAIN output in front of you and a hunch to confirm.
Three tables below:
- Plan-node signatures — "when you see this, do that."
- SQL anti-patterns — "if your code looks like this, replace with that."
- MyDBA analyzer rules — severity, trigger condition, and the article that covers each.
If a row points to a deeper article, that's where the full explanation with captured EXPLAIN examples lives.
1. Plan-node signatures
A "signature" is a field or combination of fields you can spot in an EXPLAIN plan at a glance.
| Plan signature | What it means | Fix | Deep dive |
Seq Scan on table > 10k rows with selective filter | Missing or unusable index | Add an index on the filter column, or see non-sargable cases below | Index usage |
Seq Scan + Filter: fn(col) = ... | Function on column disables index | Normalise on write, or add expression index ON t (fn(col)) | WHERE clause |
Seq Scan with Rows Removed by Filter >> Actual Rows | Filter running after scan instead of index | Add index matching the filter; check if filter is sargable | WHERE clause |
Index Scan with large Heap Fetches: | Index not covering SELECT list | Add INCLUDE columns to index | Index usage |
Index Only Scan with Heap Fetches: 0 | Optimal — the visibility map is working | No action; keep autovacuum healthy | Reading EXPLAIN |
Nested Loop with outer > 1,000 and no Memoize | Quadratic join on large inputs | Add index on inner join column; nested_loop_large rule | Joins |
Hash Join with Batches > 1 | Hash table spilled to disk | Raise work_mem per-session, or add index for different strategy | Joins |
Hash node Memory Usage > work_mem | Will spill on next execution | Same: raise work_mem or change join strategy | Joins |
Sort with Sort Method: external merge | Sort didn't fit in work_mem | Raise work_mem or add index providing sorted input | Aggregate/window |
Sort with Sort Method: top-N heapsort | Good — only N rows kept in memory | No action; verify LIMIT is reasonable | Reading EXPLAIN |
HashAggregate with Batches > 1 or Disk Usage > 0 | Aggregate spill | Raise work_mem or enable GroupAggregate with sorted index | Aggregate/window |
actual rows=r loops=l where l >> 1 | Node executed per outer-loop iteration | Usually a nested loop or SubPlan; see whether rewrite is possible | Joins, Subquery/CTE |
SubPlan N appearing under an outer node | Correlated subquery executed per row | Rewrite as JOIN, aggregating JOIN, or LATERAL | Subquery/CTE |
CTE Scan | Materialised CTE, predicates can't push in | NOT MATERIALIZED if referenced once | Subquery/CTE |
Plan Rows vs Actual Rows off by 10×+ | Stale statistics → bad plan | ANALYZE table, consider extended statistics | Reading EXPLAIN |
Workers Planned > Workers Launched | Parallel-worker pool exhausted | Raise max_parallel_workers, check for contention | Reading EXPLAIN |
Lossy Heap Blocks > 50% on Bitmap Heap Scan | Bitmap exceeded work_mem, fell back to page-level | Raise work_mem | Reading EXPLAIN |
Gather / Gather Merge above every scan | Parallelism engaged; check worker count is optimal | Usually fine; tune max_parallel_workers_per_gather if I/O-bound | Joins |
Buffers: shared read >> shared hit on hot path | Working set doesn't fit in cache | Raise shared_buffers, check for too-small cache | Reading EXPLAIN |
Memoize with near-zero hits | Cache isn't paying off (no repeated keys) | No action; negligible cost | Joins |
Run Condition: on WindowAgg | PG 15+ optimisation — window function short-circuited | Working as intended | Aggregate/window |
Incremental Sort with Presorted Key: | Partial index order let sort be localised | Working as intended; cheaper than full sort | Aggregate/window |
2. SQL anti-patterns and replacements
When you recognise one of these in code review, the replacement is usually a mechanical substitution.
| Anti-pattern | Why it's bad | Replacement |
SELECT * from wide table | Disables Index Only Scan; bloats network payload | Name the columns you actually need |
WHERE text_col = 123 | Implicit cast on column disables index | WHERE text_col = '123' |
WHERE lower(col) = 'x' | Function on column disables index | Normalise on write, or CREATE INDEX ON t (lower(col)) |
WHERE col NOT IN (SELECT ...) | NULL-unsafe; returns 0 rows on NULLs | WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...) |
WHERE col = 'a' OR col = 'b' | Usually fine, but harder to index-plan | WHERE col IN ('a', 'b') |
OFFSET N LIMIT M with large N | Reads and discards N rows | Keyset pagination with composite cursor |
SELECT DISTINCT ... ORDER BY ... for top-1-per-group | Ambiguous — any row, not the first | SELECT DISTINCT ON (key) ... ORDER BY key, ... |
Loop of one-row INSERTs | 1 round-trip per row | COPY FROM STDIN, or multi-row VALUES, or INSERT ... SELECT |
SELECT then INSERT upsert | Race condition; two round trips | INSERT ... ON CONFLICT (col) DO UPDATE |
DELETE FROM t WHERE old_date < ... on massive table | Single huge lock; WAL storm; autovacuum blocked | Chunked loop of DELETE ... WHERE id IN (SELECT ... LIMIT 10000) with COMMIT per chunk |
| N+1 from ORM loops | 1 + N round trips; N plan-and-execute cycles | Eager load with JOIN (joinedload, includes, prefetch_related) |
count(*) on huge tables | Full table scan | reltuples estimate, trigger-maintained counter, or redesign UI to not need the total |
date_trunc('day', col) = '2024-01-15' | Function disables index on col | col >= '2024-01-15' AND col < '2024-01-16' |
| Storing dates/numbers/booleans as text | Every type-aware query non-sargable | ALTER TABLE to the right type |
WHERE clause with NOW() wrapped by user-defined function marked VOLATILE | Re-evaluated per row | Mark UDF STABLE or IMMUTABLE if semantics allow |
| Transactions held open across external calls | Autovacuum blocked, bloat | Finish SQL before external calls; keep transactions short |
SELECT ... FOR UPDATE on big ranges | Locks every row returned | Use SELECT with SKIP LOCKED for worker queues; narrow the SELECT |
3. MyDBA analyzer rules
The 15-rule first-pass analyzer in frontend/src/utils/explain-plan-analyzer.ts. Use this as a reference for what each rule means and where the full treatment lives in the series.
| Rule ID | Severity | Trigger condition | Article |
seq_scan_large | warning / critical | Seq Scan with Plan Rows > 10,000 (critical above 100,000) | Index usage |
excessive_filter_rows | warning | Rows Removed by Filter / Actual Rows > 10 and Rows Removed > 1000 | WHERE clause |
nested_loop_large | warning | Nested Loop with outer > 1,000 and inner > 100 rows | Joins |
sort_on_disk | warning | Sort Space Type = Disk | Aggregate/window |
hash_batches_spill | warning | Hash Batches > 1 on Hash or Hash Join | Joins |
row_estimate_inaccurate | warning / critical | actual_rows / plan_rows ratio > 10 or < 0.1 (critical at 100 / 0.01) | Reading EXPLAIN |
lossy_bitmap_scan | warning | Lossy Heap Blocks / Total > 50% on Bitmap Heap Scan | Reading EXPLAIN |
cte_materialized | info | CTE Scan node present | Subquery/CTE |
correlated_subplan | warning | Node's JSON has a non-empty Subplan Name | Subquery/CTE |
parallel_workers_missing | info | Workers Launched < Workers Planned | Reading EXPLAIN |
high_cache_miss_rate | warning | shared_read / (read + hit) > 50% and read > 1000 blocks | Reading EXPLAIN |
temp_blocks_written | warning | Temp Written Blocks > 100 | Aggregate/window |
very_high_total_cost | warning | Total Cost > 1,000,000 | Reading EXPLAIN |
deep_plan_tree | info | Plan has > 30 nodes | Reading EXPLAIN |
no_index_usage | warning | No Index* nodes, at least one Seq Scan, > 2 total nodes | Index usage |
Severity meanings:
- critical: plan is almost certainly broken for this query size.
- warning: plan has a specific, known problem; investigate.
- info: worth noting, not necessarily actionable.
The analyzer runs on EXPLAIN plans in JSON format. If you paste a text-format plan into the MyDBA EXPLAIN visualiser, some rules (specifically the ones that rely on fields the text parser doesn't extract — such as Hash Batches or Temp Written Blocks) may not fire even when the underlying condition is present. Prefer capturing plans with EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) for the most reliable analysis.
How to use this reference
- Capture the plan with
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)in psql, or via the MyDBA EXPLAIN visualiser which captures it automatically. - Scan for the dominant signature — the single biggest cost in the plan, usually the node with the highest
actual time × loops. - Look up the signature in table 1. If the fix points to a deeper article, read the relevant section there.
- Apply the fix. Almost all of them are single SQL statements or a single session GUC change.
- Re-run EXPLAIN. Verify the plan actually changed the way you expected. If it didn't, the fix was for a different root cause.
The entire workflow, end-to-end, is described in the pillar article. If the series has been useful, that's the single link to bookmark — everything else is deep-dive for specific categories.