Sort

A Sort orders its input on one or more columns, for an ORDER BY, a Merge Join, a GroupAggregate or a WindowAgg. The sample's ORDER BY revenue DESC sorts the 24 country rows that come out of the aggregate, which is nothing. The same node sorting 12,000,000 orders is not.

Why it matters

EXPLAIN ANALYZE prints the Sort Method. quicksort with Memory: NkB: the input fit in work_mem. top-N heapsort: a LIMIT above the sort let it keep only the top N rows. external merge with Disk: NkB: the input did not fit, so PostgreSQL wrote sorted runs to temp files and merged them back. Compare Disk with work_mem. The sample runs at work_mem = 4MB, and any sort larger than that goes to disk. Since PostgreSQL 13 an Incremental Sort appears when the input is already sorted on a leading column and only the rest needs ordering.

What to do

1. For an ORDER BY with a LIMIT, an index on the sort columns removes the Sort. orders_customer_placed_idx is (customer_id, placed_at DESC): a customer's latest orders come out of the index in order.
2. For a one-off report, raise work_mem for the session and run again. The Disk figure, rounded up, is the number.
3. If the rows are wider than they need to be, sort fewer columns: select the keys, sort and limit, then join the rest back.

In the Plan tab

A sort that went to disk carries a spill label, N MB disk, and the finding Sort spilled to disk with work_mem 4MB and a Retry · button with the next size. The inspector's Memory bar marks work_mem against what the sort needed.