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=#DROP TABLE IF EXISTS t1;
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
----------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=14.18..14.65 rows=26 width=4)
   Node/s: All datanodes
   ->  Unique  (cost=13.37..13.44 rows=26 width=4)
         ->  Sort  (cost=13.37..13.40 rows=21 width=4)
               Sort Key: id
               ->  Seq Scan on t1  (cost=0.00..13.13 rows=20 width=4)
(6 rows)

-- Drop.
gaussdb=#DROP TABLE IF EXISTS t1;

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