A genuinely good feature behind this toggle, which makes it one of the more interesting enable_* switches to understand — and one of the few with a known failure mode worth recognizing. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob. Incremental sort arrived in PostgreSQL 13 (Tomas Vondra and James Coleman), and when it helps, it helps a lot.
The presorted prefix
The idea rests on a simple observation about sort keys. Suppose you have a B-tree index on column a, and you ask for ORDER BY a, b. The index already hands you rows sorted by a — but not by b within each a. The old options were both unsatisfying: do a full sort of everything on (a, b), throwing away the ordering the index already gave you, or hope an index on (a, b) exists. A full sort of a million rows is expensive, and it’s mostly wasted work when the data is already 90% ordered.
Incremental sort exploits the part that’s already sorted. Because the input arrives ordered by a, rows sharing the same value of a form contiguous groups, and within each group all that remains is to sort by b. So the executor reads one group at a time — all the rows for a = 1, sort them by b, emit; all the rows for a = 2, sort them by b, emit — sorting many small batches instead of one enormous one. The already-sorted columns are the presorted prefix, and the sort only does the work the prefix doesn’t already cover.
Two payoffs fall out of this. The sort batches are small, so they tend to fit in work_mem and use an in-memory quicksort instead of spilling to a slow external merge on disk — a large win on its own. And it pairs beautifully with LIMIT: if you only want the first ten rows of ORDER BY a, b, incremental sort can produce them after sorting just the first group or two, without sorting the entire input at all. In EXPLAIN the node appears as Incremental Sort with a telltale Presorted Key: line naming the prefix, alongside Full-sort Groups: and Pre-sorted Groups: counts showing how the batching played out.
When it goes wrong
Incremental sort is a clear win often enough to be on by default, but it has a documented dark corner. The planner estimates the cost of incremental sort using assumptions about how many distinct groups the presorted prefix produces and how evenly rows distribute across them — and when those estimates are wrong, particularly with skewed data where one prefix value owns a huge fraction of the rows, it can choose an incremental sort that performs worse than a plain full sort would have. This is the standard enable_* story (a cost misestimate yielding a bad plan) but it bit enough real workloads that incremental sort became a recurring example in “the planner picked the wrong thing” troubleshooting.
There’s a wrinkle specific to this feature, and it’s a genuine trap. PostgreSQL 16 added a related optimization — using a presorted prefix to feed GROUP BY aggregation — controlled by its own GUC, enable_presorted_aggregate, not by this one. So a query that regressed after upgrading to 16 because of presorted-input aggregation will not be fixed by setting enable_incremental_sort = off; you need enable_presorted_aggregate = off for that path. If you’re chasing a sort-related regression and disabling incremental sort changes nothing, that adjacent parameter is the next thing to check.
Symptoms that warrant flipping it
Reach for SET enable_incremental_sort = off when a query containing an Incremental Sort node regressed — classically after upgrading to 13 or later, where the feature first became available — and you suspect the planner mispriced it on skewed data. The tells are in the node’s EXPLAIN (ANALYZE) output: a large gap between estimated and actual time on the Incremental Sort, or Pre-sorted Groups: showing wildly uneven group sizes that betray the skew the cost model didn’t expect. Disable it for the session, re-run EXPLAIN (ANALYZE), and the planner falls back to a full Sort; if the full sort is faster, you’ve found the misestimate.
As always, the switch is the diagnosis, not the cure. The real fix is usually better statistics so the planner estimates the prefix’s group distribution correctly — ANALYZE, or a higher statistics target on the leading column, or extended statistics if the skew involves correlated columns. And remember the two-parameter subtlety: if disabling incremental sort doesn’t move the plan, the culprit may be the presorted-aggregate path under enable_presorted_aggregate instead. Leaving enable_incremental_sort = off in postgresql.conf throws away a feature that speeds up a great many ordered and LIMITed queries to avoid a misestimate that better statistics would resolve — the usual family mistake. Diagnose with it, fix the statistics, and set it back.