COUNT for existence check

Claim: Performance

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 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 Finding 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;

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 (>, >=, !=, <>).

A direct COUNT in a SELECT with GROUP BY is not flagged, whether it sits in the select list, HAVING or ORDER BY. GROUP BY status HAVING COUNT(*) > 0 counts the rows of each group, and EXISTS has no way to say that. A window count such as COUNT(*) OVER (PARTITION BY status) > 0 is not flagged either, because it counts the rows of each partition. A scalar subquery with a COUNT is still flagged in a grouped SELECT, because EXISTS can replace it.

Without GROUP BY the whole input is one group, so SELECT 1 FROM orders HAVING COUNT(*) > 0 is flagged. SELECT 1 WHERE EXISTS (SELECT 1 FROM orders) returns the same rows.