Sort

Description

Sorts rows from its child according to the plan sort keys.

Core node tag

T_Sort

Structured EXPLAIN Node Type

Sort

Inputs

One child plan

Output

Sorted tuples

Executor initializer

ExecInitSort

Memory mechanism

tuplesort

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Sort.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

The node passes work_mem to tuplesort. Sorting can use memory or temporary files; the actual method and space use depend on the input and plan.

Sorts tuples from the outer subtree of the node using tuplesort, which saves the results in a temporary file or memory. After the initial call, returns a tuple from the file with each call.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: ExecSortEstimate, ExecSortInitializeDSM, ExecSortInitializeWorker, ExecSortRetrieveInstrumentation.

Same-version manual discussion

Estimated start-up cost. This is the time expended before the output phase can begin, e.g., time to do the sorting in a sort node.

The planner may implement an ORDER BY clause in several ways. The above example shows that such an ordering clause may be implemented implicitly. The planner may also add an explicit Sort step:

If a part of the plan guarantees an ordering on a prefix of the required sort keys, then the planner may instead decide to use an Incremental Sort step:

Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)

In some cases EXPLAIN ANALYZE shows additional execution statistics beyond the plan node execution times and row counts. For example, Sort and Hash nodes provide extra information:

The Sort node shows the sort method used (in particular, whether the sort was in-memory or on-disk) and the amount of memory or disk space needed. The Hash node shows the number of hash buckets and batches as well as the peak amount of memory used for the hash table. (If the number of batches exceeds one, there will also be disk space usage involved, but that is not shown.)

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

The planner may implement an ORDER BY clause in several ways. The above example shows that such an ordering clause may be implemented implicitly. The planner may also add an explicit Sort step:

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=1109.39..1134.39 rows=10000 width=244)
   Sort Key: unique1
   ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

If a part of the plan guarantees an ordering on a prefix of the required sort keys, then the planner may instead decide to use an Incremental Sort step:

EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;

                                              QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=19.35..39.49 rows=100 width=244)
   ->  Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
         Sort Key: hundred, ten
         Presorted Key: hundred
         ->  Index Scan using tenk1_hundred on tenk1  (cost=0.29..1574.20 rows=10000 width=244)

Executor implementation notes

Sorts tuples from the outer subtree of the node using tuplesort, which saves the results in a temporary file or memory. After the initial call, returns a tuple from the file with each call.

There are two distinct ways that this sort can be performed:

1) When the result is a single column we perform a Datum sort.

2) When the result contains multiple columns we perform a tuple sort.

We could do this by always performing a tuple sort, however sorting Datums only can be significantly faster than sorting tuples, especially when the Datums are of a pass-by-value type.

EXPLAIN identity in core source

case T_Sort:
			pname = sname = "Sort";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_sort
• work_mem

Documentation and source

• src/backend/commands/explain.c:1522
• src/backend/executor/execProcnode.c:320
• src/backend/executor/nodeSort.c
• src/include/nodes/plannodes.h
• src/backend/utils/sort/tuplesort.c
• PostgreSQL 18.6 · using-explain
• PostgreSQL 18.6 · using-explain

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• Incremental SortIncrementalSort