Unique

Description

Removes adjacent duplicates from a sorted input stream.

Core node tag

T_Unique

Structured EXPLAIN Node Type

Unique

Inputs

One sorted child plan

Output

Distinct tuples

Executor initializer

ExecInitUnique

Memory mechanism

unclassified

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Unique.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: none extracted from this node implementation.

Same-version manual discussion

The added condition stringu1 = 'xxx' reduces the output row count estimate, but not the cost because we still have to visit the same set of rows. That's because the stringu1 clause cannot be applied as an index condition, since this index is only on the unique1 column. Instead it is applied as a filter on the rows retrieved using the index. Thus the cost has actually gone up slightly to reflect this extra checking.

In this type of plan the table rows are fetched in index order, which makes them even more expensive to read, but there are so few that the extra cost of sorting the row locations is not worth it. You'll most often see this plan type for queries that fetch just a single row. It's also often used for queries that have an ORDER BY condition that matches the index order, because then no extra sorting step is needed to satisfy the ORDER BY . In this example, adding ORDER BY unique1 would use the same plan because the index already implicitly provides the requested ordering.

In this plan, we have a nested-loop join node with two table scans as inputs, or children. The indentation of the node summary lines reflects the plan tree structure. The join's first, or “ outer ” , child is a bitmap scan similar to those we saw before. Its cost and row count are the same as we'd get from SELECT ... WHERE unique1 < 10 because we are applying the WHERE clause unique1 < 10 at that node. The t1.unique2 = t2.unique2 clause is not relevant yet, so it doesn't affect the row count of the outer scan. The nested-loop join node will run its second, or “ inner ” child once for each row obtained from the outer child. Column values from the current outer row can be plugged into the inner scan; here, the t1.unique2 value from the outer row is available, so we get a plan and costs similar to what we saw above for a simple SELECT ... WHERE t2.unique2 = constant case. (The estimated cost is actually a bit lower than what was seen above, as a result of caching that's expected to occur during the repeated index scans on t2 .) The costs of the loop node are then set on the basis of the cost of the outer scan, plus one repetition of the inner scan for each outer row (10 * 7.90, here), plus a little CPU time for join processing.

The condition t1.hundred < t2.hundred can't be tested in the tenk2_unique2 index, so it's applied at the join node. This reduces the estimated output row count of the join node, but does not change either input scan.

Here we see an Index-Only Scan node using tenk1_four_unique1_idx , a multi-column index on the tenk1 table's four and unique1 columns. The scan performs 3 searches that each read a single index leaf page: “ four = 1 AND unique1 = 42 ” , “ four = 2 AND unique1 = 42 ” , and “ four = 3 AND unique1 = 42 ” . This index is generally a good target for skip scan, since, as discussed in Section 11.3 , its leading column (the four column) contains only 4 distinct values, while its second/final column (the unique1 column) contains many distinct values.

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

Now let's modify the query to add a WHERE condition:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
   Filter: (unique1 < 7000)

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

Now, let's make the condition more restrictive:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

Executor implementation notes

nodeUnique.c Routines to handle unique'ing of queries where appropriate

Unique is a very simple node type that just filters out duplicate tuples from a stream of sorted tuples from its subplan. It's essentially a dumbed-down form of Group: the duplicate-removal functionality is identical. However, Unique doesn't do projection nor qual checking, so it's marginally more efficient for cases where neither is needed. (It's debatable whether the savings justifies carrying two plan node types, though.)

NOTES Assumes tuples returned from subplan arrive in sorted order.

now loop, returning only non-duplicate tuples. We assume that the tuples arrive in sorted order so we can detect duplicates easily. The first tuple of each group is returned.

Else test if the new tuple and the previously returned tuple match. If so then we loop back and fetch another new tuple from the subplan.

EXPLAIN identity in core source

case T_Unique:
			pname = sname = "Unique";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans

Documentation and source

• src/backend/commands/explain.c:1577
• src/backend/executor/execProcnode.c:350
• src/backend/executor/nodeUnique.c
• src/include/nodes/plannodes.h
• PostgreSQL 18.6 · using-explain
• PostgreSQL 18.6 · using-explain

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• AppendAppend
• Merge AppendMergeAppend
• Recursive UnionRecursiveUnion
• SetOpSetOp