pgconfigurator
pgconfigurator
Talk to sales

Sort

Appears in EXPLAIN asSort

Orders rows — in memory if it fits work_mem, otherwise on disk.

What it is

A Sort node orders its input by one or more keys. Small sorts run in memory (quicksort / top-N heapsort); larger ones spill to temporary files and use an external merge sort.

When the planner picks it

For ORDER BY, for merge joins, for sorted aggregation/grouping, and for DISTINCT when hashing isn't chosen.

Is it good or bad?

Necessary work, but an external (on-disk) sort is much slower than an in-memory one. The fix is almost always more work_mem for the query — or an index that supplies the order for free.

In depth

How it works under the hood

A Sort node buffers its input and orders it by the sort keys. PostgreSQL picks a method automatically and reports it:

  • quicksort — the whole input fit in work_mem; fastest.
  • top-N heapsort — there's a LIMIT, so it keeps only the top N rows in a heap. Very cheap even over huge inputs.
  • external merge — the input did not fit in work_mem, so it was written to temporary files and merged from disk. Much slower.

Reading the method line

Sort  (actual time=4105..4921 rows=580000 loops=1)
  Sort Key: payload
  Sort Method: external merge  Disk: 34824kB
  Buffers: shared hit=8332, temp read=21087 written=22237

external merge Disk: 34824kB is the signal: this sort spilled ~34 MB to disk. The temp read/written buffers confirm the I/O. An in-memory sort of the same data would show Sort Method: quicksort Memory: ...kB and no temp files.

Two ways to make a sort cheap

  1. Give it more memory so it stays in RAM:

    SET LOCAL work_mem = '128MB';

    Remember work_mem is per sort node per connection — many concurrent big sorts multiply real memory use, so raise it per-query rather than globally.

  2. Remove the sort entirely with an index that already returns rows in the wanted order. An ORDER BY created_at DESC LIMIT 20 over an index on created_at becomes a tiny Index Scan + Limit — no Sort at all.

Incremental Sort

If an index supplies part of the ordering (rows sorted by a, query wants a, b), the planner can use an Incremental Sort: it only sorts the b values within each run of equal a. That keeps memory tiny and returns early rows fast — especially valuable with LIMIT.

What the analyzer flags here

  • Sort spilled to disk — the sort exceeded work_mem and merged from disk

Paste a plan into the analyzer →

Settings that influence it

work_mem

How we tune these →

FAQ

How do I stop a sort from spilling to disk?
Raise work_mem for the session or the query (SET LOCAL work_mem = '...'), or provide an index that already returns rows in the required order so the sort disappears entirely.

See also