COUNT(*) over COUNT(column)

Severity: Info

COUNT(column) evaluates each row’s value for NULL before incrementing the counter. COUNT(*) means “count rows” — it is a zero-argument aggregate that does not examine any column value or check for NULLs, making it the most direct way to count rows.

COUNT(1) is semantically equivalent to COUNT(*) since the literal 1 is never NULL, but it still asks PostgreSQL to evaluate an expression per row. Modern PostgreSQL versions optimize COUNT(1) to match COUNT(*) performance, but COUNT(*) expresses the intent more clearly.

Example

-- Checks each row's `id` value for NULL before counting
SELECT COUNT(id) FROM users;

-- Evaluates the constant 1 per row (optimized away in recent PostgreSQL)
SELECT COUNT(1) FROM users;

-- Counts rows directly — no NULL check, clearest intent
SELECT COUNT(*) FROM users;

Why it matters

NULL-check overhead

COUNT(column) must read the column value for every row and skip NULLs. On a non-nullable column like a primary key this check is wasted work. Benchmarks from CYBERTEC and Citus Data show that COUNT(*) can be measurably faster than COUNT(column) on large tables.

Clarity of intent

COUNT(*) unambiguously says “count the rows.” COUNT(id) can be misread as “count non-NULL ids” — which may or may not be the author’s intent.

When COUNT(column) is appropriate

COUNT(column) is the correct choice when you intentionally want to count only non-NULL values of a specific column. For example:

-- Count how many users have an email address (skip NULLs)
SELECT COUNT(email) FROM users;

In this case, COUNT(email) and COUNT(*) return different results if some rows have NULL in the email column. The nudge is informational — review the query and switch to COUNT(*) only if you are counting rows, not non-NULL values.

How to fix it

Replace COUNT(column) or COUNT(1) with COUNT(*):

-- Before
SELECT COUNT(id) FROM users WHERE active = true;
SELECT COUNT(1) FROM orders WHERE status = 'shipped';

-- After
SELECT COUNT(*) FROM users WHERE active = true;
SELECT COUNT(*) FROM orders WHERE status = 'shipped';

Detection

Query Doctor flags this nudge when it finds a COUNT() function call that has explicit arguments and is not using DISTINCT. The nudge marker points to the COUNT keyword in the formatted SQL view.