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
Example: 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 ---------------------------------------------------------------- Sort (cost=10.64..11.14 rows=200 width=12) Sort Key: grade -> Seq Scan on student (cost=0.00..3.00 rows=200 width=12) (3 rows) -- Drop. gaussdb=# DROP TABLE student;
In the preceding example, the output of the sort operator is as follows.
Item |
Description |
---|---|
Sort |
Operator name. |
Sort Key |
Keyword based on which the sort operator uses for sorting. In the example, the value is grade. |
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=# CREATE INDEX t1_k on t1(id); CREATE INDEX 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 ------------------------------------------------------------------------------ Merge Join (cost=9710.87..14918.02 rows=100000 width=26) Merge Cond: (t1.id = t2.id) -> Index Scan using t1_k on t1 (cost=0.00..3457.25 rows=100000 width=13) -> Sort (cost=9710.82..9960.82 rows=100000 width=13) Sort Key: t2.id -> Seq Scan on t2 (cost=0.00..1406.00 rows=100000 width=13) (6 rows) -- Drop. gaussdb=# DROP TABLE t1,t2;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot