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:

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 `INSERT`s	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.