Scalar subquery in SELECT

Claim: Performance

A correlated scalar subquery in the SELECT list refers to a column of the outer query, so it runs once for every row the outer query returns. If the outer query returns 100,000 rows, the subquery runs 100,000 times. PostgreSQL runs it as a SubPlan and does not turn it into a join.

An uncorrelated scalar subquery refers to nothing outside itself. PostgreSQL runs it once, as an InitPlan, however many rows the outer query returns.

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

For each row produced by the outer query, PostgreSQL runs a correlated scalar subquery’s inner SELECT again, 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 a hash join or a merge join for a correlated scalar subquery.
• Hidden cost. The query looks simple but can be orders of magnitude slower than the equivalent JOIN.

How to fix it

If the subquery refers to the outer query, rewrite it 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.

A subquery that aggregates becomes a join to a subquery grouped by the column it compares with the outer query. A count subquery returns 0 for an outer row with no match, and the join returns NULL there, so wrap the count in coalesce:

-- Before: runs the count once per user
SELECT u.id,
  (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- After: one grouped pass over orders
SELECT u.id, coalesce(c.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, count(*) AS order_count FROM orders GROUP BY user_id
) c ON c.user_id = u.id;

Common variations

Pattern	Same problem?
SELECT (SELECT count(*) FROM orders WHERE orders.user_id = u.id) FROM users u	Yes: the aggregate subquery still runs per row
SELECT e.name, (SELECT count(*) FROM departments) FROM employees e	No: it refers to nothing outside itself, so it runs once
SELECT * FROM t WHERE id IN (SELECT id FROM other)	No: an 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 reports this Finding when it finds a SELECT-list item whose value is a scalar subquery. It does not check whether the subquery is correlated, so a Finding read from the SQL text covers both kinds. When the query has a plan, Query Doctor drops the Finding if the plan has no SubPlan, because then no subquery runs once per row. The Finding’s marker points to the subquery location in the formatted SQL view. A scalar subquery in an arm of UNION, INTERSECT or EXCEPT is not reported, because Query Doctor does not check these arms.