Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-3.x)/ SQL Optimization/ Introduction to the SQL Execution Plan/ Operators/ Operators/ Unique
Updated on 2025-03-13 GMT+08:00
View PDF
Share

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

Example: 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;
Parent topic: Operators