Nudges Overview

Nudges are actionable suggestions that Query Doctor attaches to queries in the Live Queries view. Each nudge identifies a specific performance concern — from SQL anti-patterns to concrete index recommendations — and is assigned a severity level so you can prioritize what to fix first.

In the query detail view, nudges are grouped by severity in the Insights panel. Nudges that reference a specific location in the query are also highlighted inline in the formatted SQL view.

Severity levels

Severity	Meaning
Critical	High-impact issue that significantly degrades performance. Act on these first.
Warning	Issue that negatively impacts performance or correctness. Worth investigating.
Info	Best practice or minor inefficiency. Consider addressing when convenient.

The query list sorts by nudge severity by default, using a weighted score: critical counts for 16 points, warning for 4, and info for 1. This surfaces the queries with the most impactful issues at the top.

Nudge categories

Nudges come from two sources: query analysis (SQL anti-patterns detected by parsing the query AST) and optimization results (cost-based findings from the analyzer).

Optimization nudges

These are generated after the analyzer evaluates index configurations for a query.

Nudge	Severity	Description
Large improvement found	Critical	50%+ cost reduction with an absolute difference >100. Includes suggested CREATE INDEX statements.
Small improvement found	Warning	Cost reduction below 50%. Still worth reviewing, especially for high-call queries.

Query analysis nudges

These are detected by parsing the SQL and flagging common anti-patterns. Each can include a character offset pointing to the exact location in the query.

Nudge	Severity	Description
Function on column in conditions	Warning	A function wrapping a column in a WHERE or JOIN … ON condition (e.g. WHERE lower(name) = 'bob') prevents the planner from using a plain B-tree index on that column.
Leading wildcard LIKE	Warning	A LIKE pattern starting with % (e.g. LIKE '%search') forces a sequential scan. Consider a trigram index or full-text search.
NULL comparison with = or !=	Warning	Comparing to NULL with = or != always yields unknown. Use IS NULL or IS NOT NULL.
DISTINCT without clear need	Warning	DISTINCT adds a sort or hash step. Use it only when duplicates are actually possible and unwanted.
Missing JOIN condition	Warning	A JOIN without an ON clause or multiple tables in FROM without an explicit join produce a cartesian product.
Many ORs instead of IN	Warning	Three or more OR conditions on the same column (e.g. id = 1 OR id = 2 OR id = 3) can be simplified to IN (1, 2, 3).
NOT IN subquery	Warning	NOT IN (SELECT ...) is a performance and correctness trap — if the subquery returns any NULL, zero rows are returned. Use NOT EXISTS instead.
ORDER BY random()	Warning	ORDER BY random() forces a full sequential scan and sort of the entire table. Use TABLESAMPLE or an index-based approach instead.
Scalar subquery in SELECT	Warning	A correlated scalar subquery in SELECT runs once per outer row (N+1 problem). Rewrite as a JOIN.
COUNT(*) over COUNT(column)	Info	COUNT(column) checks for NULLs per row; COUNT(*) counts rows directly and is faster. Use COUNT(column) only when skipping NULLs intentionally.
Non-aggregate HAVING	Info	A HAVING clause with no aggregate functions should be moved to WHERE to filter rows before grouping.
SELECT *	Info	Selecting all columns fetches more data than needed and prevents Index Only Scans. List only the columns you use.
Missing WHERE clause	Info	A top-level SELECT from a table with no WHERE clause reads every row.
Missing LIMIT clause	Info	A top-level SELECT from a table with no LIMIT may return more rows than the caller needs.
Large IN tuple	Info	An IN list with 10+ literal values can be replaced with = ANY($1) using a parameterized array for cleaner plans.
FILTER over CASE in aggregate	Info	SUM(CASE WHEN ... THEN ... END) can be rewritten as SUM(...) FILTER (WHERE ...) for better readability.
UNION without ALL	Info	UNION removes duplicates with an implicit sort. Use UNION ALL if deduplication is not needed.
NULLs first in DESC order	Info	ORDER BY x DESC puts NULLs first by default. Add NULLS LAST or filter with IS NOT NULL to control placement.
COUNT for existence check	Info	COUNT(*) > 0 scans all matching rows. EXISTS short-circuits after the first match.
JSONB set-returning function in WHERE	Info	JSONB set-returning functions (e.g. jsonb_array_elements) cannot be used as an index access path. GIN-compatible operators may allow index usage.