PostgreSQL Plan Signatures: Quick Reference

A scannable lookup companion to the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series. Designed for when you have an EXPLAIN plan in front of you and need the pattern → fix mapping fast, without re-reading the deep-dive articles. Three tables below:

1. Plan-node signatures — "when you see this, do that."
2. SQL anti-patterns — "if your code looks like this, replace with that."
3. 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

1. Capture the plan with EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) in psql, or via the MyDBA EXPLAIN visualiser which captures it automatically.
2. Scan for the dominant signature — the single biggest cost in the plan, usually the node with the highest actual time × loops.
3. Look up the signature in table 1. If the fix points to a deeper article, read the relevant section there.
4. Apply the fix. Almost all of them are single SQL statements or a single session GUC change.
5. 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.

---

postgres #performance #database #sql

Full series and canonical copy: https://mydba.dev/blog/postgres-plan-signatures-quick-reference