Sort

Description

Sorts the tuples returned by the bottom-layer node. The sort operator is used to sort query results based on specified sorting rules and return ordered result sets.

Typical Scenarios

• If a query statement contains the ORDER BY clause, GaussDB selects the sort operator in the execution plan to perform sorting.
• Merge join is used for join operations.

Examples

The query statement contains the ORDER BY clause.

-- Prepare data.
gaussdb=#CREATE TABLE student(id integer, class_id integer, grade number); 
CREATE TABLE 
gaussdb=#INSERT INTO student VALUES(generate_series(1,50), 1, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(51,100), 2, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(101,150), 3, floor(100 * random())); 
INSERT 0 50 
gaussdb=#INSERT INTO student VALUES(generate_series(151,200), 3, floor(100 * random())); 
INSERT 0 50

-- Execution result.
gaussdb=#EXPLAIN SELECT * FROM student ORDER BY grade; 
                              QUERY PLAN
----------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=14.18..14.62 rows=21 width=40)
   Merge Sort Key: grade
   Node/s: All datanodes
   ->  Sort  (cost=13.37..13.40 rows=21 width=40)
         Sort Key: grade
         ->  Seq Scan on student  (cost=0.00..13.13 rows=20 width=40)
(6 rows)

-- Drop.
gaussdb=#DROP TABLE student;

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

Example: Merge join is used for join operations.

-- Prepare data.
gaussdb=# DROP TABLE IF EXISTS t1;
gaussdb=# DROP TABLE IF EXISTS t2;
gaussdb=# CREATE TABLE t1(id int,info text); 
CREATE TABLE 
gaussdb=# CREATE TABLE t2(id int,info text); 
CREATE TABLE 
gaussdb=# INSERT INTO t2 SELECT generate_series(1,100000),'bill'||generate_series(1,100000); 
INSERT 0 100000 
gaussdb=# INSERT INTO t1 SELECT generate_series(1,100000),'bill'||generate_series(1,100000); 
INSERT 0 100000

-- Collect statistics.
gaussdb=# ANALYZE t1;
gaussdb=# ANALYZE t2;

-- Execution result.
gaussdb=# EXPLAIN SELECT /*+ MERGEJOIN(t2 t1) */ * FROM t2 JOIN t1 ON (t1.id=t2.id); 
                                 QUERY PLAN
----------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=9352.82..15036.32 rows=100000 width=26)
   Node/s: All datanodes
   ->  Merge Join  (cost=9348.82..10348.82 rows=100000 width=26)
         Merge Cond: (t2.id = t1.id)
         ->  Sort  (cost=4674.41..4799.41 rows=100000 width=13)
               Sort Key: t2.id
               ->  Seq Scan on t2  (cost=0.00..772.00 rows=100000 width=13)
         ->  Sort  (cost=4674.41..4799.41 rows=100000 width=13)
               Sort Key: t1.id
               ->  Seq Scan on t1  (cost=0.00..772.00 rows=100000 width=13)
(10 rows)

-- Drop.
gaussdb=# DROP TABLE t1,t2;