What work does a database engine actually have to do to eliminate duplicate rows from a result, and when can the planner skip that work entirely?

## Why it is not free A bag becomes a set only by comparing rows. There is no way to know a row is a duplicate without having seen every other row, so duplicate elimination is inherently a whole-input operation. Engines implement it in two ways. **Sort-based (sort + unique).** Sort the input on all the output columns, then walk the sorted stream and drop any row equal to its predecessor. The comparison work is O(n log n) in the number of rows, with each comparison touching all output columns. It is a blocking operator: no output row can be produced until the sort has consumed the whole input, which delays the first row and stalls the pipeline above it. When the sort exceeds its work-memory budget it spills runs to temporary disk and merges them - a large step change in cost. **Hash-based.** Build an in-memory hash table keyed on the whole output row; probe for each incoming row and emit it only on first sight. This can stream: distinct rows come out as they are discovered. Its memory need is proportional to the number of *distinct* rows, not the input size, so it is excellent when the input has heavy duplication and poor when it is nearly unique. When the table does not fit, engines partition to disk and process in passes. A third route appears when the input is already ordered - for example arriving from an index scan in the right order. Then duplicates are adjacent and the engine can dedup in a single streaming pass with no sort at all, which is why an appropriate index can make a duplicate-eliminating query dramatically cheaper. ## Width and columns matter Duplicate elimination compares the whole output row, not one column. Adding a wide text column, or a per-row unique column such as a surrogate id, changes the operator's behaviour completely: comparisons get more expensive, and a unique id makes every row distinct, so the operator does a lot of work and removes nothing. A frequent production surprise is that adding one column 'for debugging' turns a small deduplicated result back into the full input. ## NULLs during dedup Duplicate elimination uses distinctness, not equality: two NULLs in the same position are considered not distinct and collapse into one row, even though comparing them with the equality operator does not yield true. Grouping behaves the same way. This is a deliberate exception so that duplicate removal and grouping are total operations. ## When the planner can skip it The operator can be removed whenever uniqueness is *provable* from metadata: - The output column list contains a declared primary key or unique constraint of a single scanned table - each row is then already distinct. - The input came from a grouping that produced one row per group on those same columns. - The join preserved uniqueness - for instance, joining on the parent's key from the child side cannot duplicate the parent key column set. This is one of the concrete payoffs of declaring constraints rather than merely believing the data is clean: an undeclared unique column forces the engine to do the sort or hash anyway, because a planner may only rely on what is enforced. ## Practical guidance Treat a duplicate-elimination step in a plan as a question, not a fact of life. Ask where the duplicates came from: usually join fan-out, occasionally an over-broad projection. If the duplicates are a symptom, fix the join or pre-aggregate rather than paying dedup on every execution. If they are legitimate, check whether an index provides the needed ordering for free, keep the output column list narrow, and size the sort or hash memory so the operator does not spill. And remember the cost is paid even when the input is already unique - so if it is unique, declare the constraint that says so.