Findings

A Finding is a claim that something is wrong with one query. Query Doctor reads Findings from a query’s SQL text and from its explain plan, and attaches them to the query in the Queries view. The pages in this section cover the Findings read from the SQL text, and the plan Findings listed below. When the plan confirms a text Finding, the two show as one Finding. When the plan proves one false, Query Doctor drops it.

An index or rewrite that lowers a query’s cost is not a Finding. It shows as an Improvement, covered in How optimization works.

Correctness and performance

Each Finding makes one of two claims.

Claim	Meaning
Correctness	The results can be wrong or unstable.
Performance	The query is correct but slow.

A text Finding claims correctness only when the SQL text alone shows the results can be wrong. NOT IN subquery and NULLs first in DESC order are performance Findings, because each changes the results only when a column can hold NULL, and the SQL text does not say whether it can.

Order

On one query, correctness Findings come first, then performance Findings that an Improvement resolves, then the rest by Impact, the share of the query’s cost a Finding accounts for. A performance Finding on a query whose plan costs under 100 is not shown, while a correctness Finding always is. The exception is JSON field read on every row, whose cost the plan does not count.

Across queries, the Queries view sorts by relevance, described in Why a query ranks where it does.

Findings read from the SQL text

Query Doctor parses the SQL and reports these anti-patterns. A Finding that names a place in the query is highlighted there in the formatted SQL view.

Finding	Claim	Description
NULL comparison with = or !=	Correctness	Comparing to NULL with = or != always yields unknown. Use IS NULL or IS NOT NULL.
Join without an ON or USING clause	Correctness	A NATURAL JOIN, or a comma join whose WHERE has no t1.col = t2.col naming two of its tables, can produce a cartesian product.
LIMIT without ORDER BY	Correctness	LIMIT without ORDER BY returns an arbitrary subset of rows, which can change between executions. Order by a unique column.
Function on column in conditions	Performance	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	Performance	A LIKE pattern starting with % (e.g. LIKE '%search') forces a sequential scan. Consider a trigram index or full-text search.
DISTINCT without clear need	Performance	DISTINCT adds a sort or hash step. Use it only when duplicates are actually possible and unwanted.
Many ORs instead of IN	Performance	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	Performance	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()	Performance	ORDER BY random() reads every row of the table and computes random() for each. Sort a TABLESAMPLE or use an index-based lookup instead.
Scalar subquery in SELECT	Performance	A correlated scalar subquery in SELECT runs once per outer row (N+1 problem). Rewrite as a JOIN.
COUNT(*) over COUNT(column)	Performance	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	Performance	A HAVING condition with no aggregate belongs in WHERE. PostgreSQL usually moves it there itself.
SELECT *	Performance	Selecting all columns can fetch columns the caller never uses and usually rules out an Index Only Scan. List only the columns you use.
Missing WHERE clause	Performance	A top-level SELECT that reads a table’s rows with no WHERE clause, directly or through an unfiltered CTE, reads every row.
Missing LIMIT clause	Performance	A top-level SELECT from a table with no LIMIT, and no unique key pinned in WHERE, may return more rows than the caller needs.
Large IN tuple	Performance	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	Performance	SUM(CASE WHEN ... THEN ... END) can be rewritten as SUM(...) FILTER (WHERE ...) for better readability.
UNION without ALL	Performance	UNION removes duplicates with an implicit sort. Use UNION ALL if deduplication is not needed.
Prefix LIKE no index can serve	Performance	LIKE 'abc%' on a text column whose collation is not C, with no index that serves it. A plain B-tree cannot; one built with text_pattern_ops can.
NULLs first in DESC order	Performance	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	Performance	COUNT(*) > 0 scans all matching rows. EXISTS short-circuits after the first match.
JSONB set-returning function in WHERE	Performance	JSONB set-returning functions (e.g. jsonb_array_elements) cannot be used as an index access path. GIN-compatible operators may allow index usage.

Findings read from the plan

Finding	Claim	Description
LIMIT waits for the whole join	Performance	The leading ORDER BY key reads two joined tables, so no index can produce the order and the LIMIT applies only after the whole join is sorted.
JSON field read on every row	Performance	A field is read out of a large JSON document on every row the query reads, which decompresses and parses the whole document each time.

When the SQL text was not read

Postgres’ parser rejects some SQL, and Query Doctor can fail to read SQL the parser accepts. Such a query has no Finding from its text, so nothing checked it for wrong or unstable results. Query Doctor marks it instead of showing it as clean. Of a text that holds several statements, only the first is read. The query page says the SQL text was not read, the Queries and CI run lists show not read in its row, and the dashboard’s Action Plan counts those from CI runs on the comparison branch.