PostgreSQL 10 High Performance - Third Edition

Sort

Sort nodes can appear when you insert ORDER BY statements into your queries:

EXPLAIN ANALYZE SELECT customerid FROM customers ORDER BY zip;
QUERY PLAN
----------
Sort (cost=2104.77..2154.77 rows=20000 width=8) (actual time=162.796..199.971 rows=20000 loops=1)
    Sort Key: zip
    Sort Method:  external sort  Disk: 352kB
    ->  Seq Scan on customers  (cost=0.00..676.00 rows=20000 width=8) (actual time=0.013..46.748 rows=20000 loops=1)
Total runtime: 234.527 ms  

Sort operations can either execute in memory using the quicksort algorithm, if they're expected to fit, or will be swapped to disk to use what's called an external merge sort—the case in this example. The threshold at which that happens depends on the work_mem setting on the server. This example ...