COUNT for existence check

Severity: Info

Using COUNT(*) > 0 or COUNT(*) >= 1 to check if rows exist forces PostgreSQL to scan and count all matching rows, then compare the total. EXISTS short-circuits after finding the first match, which can be orders of magnitude faster on large tables.

Example

-- Slow: counts all matching rows, then checks if the count is positive
SELECT id FROM users
WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

-- Fast: stops scanning after the first match
SELECT id FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

Why it matters

COUNT(*) must scan every qualifying row to produce a total. If a customer has 10,000 orders, PostgreSQL reads all 10,000 rows just to confirm “at least one exists.” EXISTS returns true as soon as it finds the first matching row, skipping the remaining 9,999.

The difference is most pronounced when:

• The subquery table is large.
• Many rows match the condition.
• The query runs frequently (e.g. in a loop or dashboard).

Common patterns

This nudge fires on several equivalent forms:

-- All of these check existence using COUNT
WHERE (SELECT COUNT(*) FROM t) > 0
WHERE (SELECT COUNT(1) FROM t) >= 1
WHERE (SELECT COUNT(*) FROM t) <> 0

-- In CASE expressions
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END FROM t;

-- In HAVING clauses
SELECT status FROM orders GROUP BY status HAVING COUNT(*) > 0;

How to fix it

Replace the count comparison with EXISTS:

-- Before
SELECT id FROM users
WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

-- After
SELECT id FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

For CASE WHEN COUNT(*) > 0 patterns, restructure the query:

-- Before
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
FROM orders WHERE customer_id = 123;

-- After
SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 123);

Detection

Query Doctor flags A_Expr comparisons where one side is a COUNT(*) or COUNT(1) expression (either directly or inside a scalar subquery) and the other side is 0 or 1, with an operator that makes this an existence check (>, >=, !=, <>).