Sort and Incremental Sort in a Plan, and When an Index Removes the Sort
ORDER BY, merge joins and some grouping all require sorted rows, and when nothing supplies that order the plan grows a Sort node that must consume every row before it can emit the first one. An index whose order already matches can remove the Sort completely, and Incremental Sort is the middle case: the rows are already sorted by the first column, so the engine only sorts within each group. In EXPLAIN ANALYZE the Sort node reports its method and memory, which is where you learn whether it stayed in RAM.
Questions this Concept answers
Why can a `Sort` node not emit its first row early?
J
jeremy
Video
When Incremental Sort Makes a PostgreSQL Query Plan Slower
Incremental sort is a PostgreSQL executor node, introduced in version 13, that exploits a partially sorted input — typically an index whose leading keys already satisfy part of the ORDER BY — and sor…