DISTINCT without clear need

Claim: Performance

Adding DISTINCT to a query forces PostgreSQL to sort or hash the entire result set to eliminate duplicate rows. If the result set can’t contain duplicates in the first place — because of a primary key, unique constraint, or the query structure — then DISTINCT adds work for no benefit.

Example

-- If users.id is a primary key, every row is already unique
SELECT DISTINCT id, name, email FROM users WHERE active = true;

Why it happens

DISTINCT tells PostgreSQL to deduplicate the result. To do this, the planner adds either a Sort node (to group identical rows together and discard duplicates) or a HashAggregate node (to build a hash table of seen rows). Both consume CPU and memory proportional to the result size. For large result sets, a sort may spill to disk.

If the selected columns already include a unique key, no duplicates are possible and this extra step is pure waste.

When DISTINCT is appropriate

DISTINCT is appropriate when duplicates genuinely occur and you need to eliminate them:

-- Multiple orders per customer, but we want each customer once
SELECT DISTINCT customer_id FROM orders WHERE created_at > '2024-01-01';

It’s also valid after a JOIN that can fan out rows:

-- A user may have many orders, but we want unique user rows
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100;

How to fix it

If the query already guarantees unique results, remove DISTINCT:

-- Primary key guarantees uniqueness — DISTINCT is unnecessary
SELECT id, name, email FROM users WHERE active = true;

If you’re using DISTINCT to fix a JOIN that produces unwanted duplicates, consider whether the join itself can be restructured — for example, using EXISTS instead:

-- Instead of SELECT DISTINCT u.* FROM users u JOIN orders o ON ...
SELECT u.id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100);

Detection

Query Doctor reports this Finding on a plain DISTINCT in a SELECT, including one in a subquery or a CTE. Review each flagged query to determine whether duplicates are actually possible.

These are not reported:

• DISTINCT ON (...). It keeps the first row of each group of rows that share the ON expressions, which is a different job from removing duplicate rows.
• A DISTINCT in the select list of an EXISTS or NOT EXISTS subquery. EXISTS asks only whether a row exists, so in most of these subqueries Postgres drops the DISTINCT before planning. A few keep it, such as one that aggregates or has an OFFSET, and those are not reported either.
• A DISTINCT in an arm of UNION, INTERSECT or EXCEPT. Query Doctor does not check these arms.