Updated on 2025-05-29 GMT+08:00

Unique

Description

Deduplicates lower-layer data. During the execution, it traverses all input data, filters duplicate records, and retains only the unique record.

Typical Scenarios

Disable the enable_hashagg parameter and perform the query with DISTINCT.

Examples

Perform the query with DISTINCT.

-- Prepare data.
gaussdb=# CREATE TABLE t1 (id INT , number INT);
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,50), 1);
INSERT 0 50
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,50), 2);
INSERT 0 50

-- Execution result.
gaussdb=# SET enable_hashagg = off;
SET
gaussdb=# EXPLAIN SELECT DISTINCT t1.id FROM t1;
                            QUERY PLAN                            
------------------------------------------------------------------
 Unique  (cost=150.43..161.18 rows=200 width=4)
   ->  Sort  (cost=150.43..155.80 rows=2149 width=4)
         Sort Key: id
         ->  Seq Scan on t1  (cost=0.00..31.49 rows=2149 width=4)
(4 rows)

-- Drop.
gaussdb=# DROP TABLE t1;

In the preceding example, the output of the Unique operator is as follows.

Item

Description

Unique

Operator name.

Sort

Operator name.

Sort Key

Keyword based on which the sort operator uses for sorting. In the example, the value is id.