It's kind of lame questions, possibly I'm missing something but my
doubts are as follow:
When planner/executor needs to sort rowsit sorts whole records (i
think so). So in the case when there are many wide columns it takes
quite a lot of memory and sort goes out to the disk because it excess
the work_mem.
Isn't it possible to sort only fields that order matters & some row
identifier/position (don't really know what - oid/ctid are tight to
table but something temporary tight to 'resultset')? It would take
much less memory and could be processed in the work_mem more often.
# select sum(length(title)) from contacts;
sum
------
4225
(1 row)
# explain analyze SELECT * FROM contacts ORDER BY title;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=6303.07..6369.65 rows=26634 width=269) (actual
time=71.945..92.989 rows=26634 loops=1)
Sort Key: title
Sort Method: external sort Disk: 7368kB
-> Seq Scan on contacts (cost=0.00..1456.34 rows=26634 width=269)
(actual time=0.008..10.995 rows=26634 loops=1)