Scalar subquery in SELECT

Severity: Warning

A scalar subquery in the SELECT list is executed once for every row returned by the outer query. If the outer query returns 100,000 rows, the subquery runs 100,000 times. PostgreSQL can only execute these as nested loops — there is no way to batch or hash-join them.

Example

-- Bad: subquery runs once per outer row
SELECT e.name,
  (SELECT d.name FROM departments d WHERE d.id = e.dept_id)
FROM employees e;

Even though this looks like a single query, it behaves like an N+1 pattern: one outer scan plus one subquery execution per row.

Why it matters

PostgreSQL evaluates correlated scalar subqueries using a nested loop. For each row produced by the outer query, the planner runs the inner SELECT as a separate plan. This means:

• Linear scaling with row count. If the outer query returns N rows, the subquery executes N times.
• No join optimization. The planner cannot use hash joins or merge joins for scalar subqueries — it’s always a nested loop.
• Hidden cost. The query looks simple but can be orders of magnitude slower than the equivalent JOIN.

How to fix it

Rewrite the scalar subquery as a JOIN:

-- Good: single pass with a JOIN
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

Use LEFT JOIN if the subquery might return NULL (i.e., when there’s no matching row). Use JOIN if every outer row is guaranteed to have a match.

Common variations

Pattern	Same problem?
SELECT (SELECT count(*) FROM orders WHERE orders.user_id = u.id) FROM users u	Yes — aggregate subquery still runs per row
SELECT * FROM t WHERE id IN (SELECT id FROM other)	No — IN subquery is not a scalar subquery in SELECT
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM other WHERE ...)	No — EXISTS is evaluated differently

Detection

Query Doctor flags this nudge when it finds a ResTarget in the SELECT list whose value is a scalar subquery (EXPR_SUBLINK). The nudge marker points to the subquery location in the formatted SQL view.