"Jim C. Nasby" <jim@nasby.net> writes:
>> DB2 and Oracle, from memory, allow users to pass hints to the planner to
>> use/not use file system caching.
> Might it make sense to do this for on-disk sorts, since sort_mem is
> essentially being used as a disk cache (at least for reads)?
If sort_mem were actually being used that way, it might be ... but it
isn't, and so I doubt O_DIRECT would be an improvement. It seems more
likely to force disk I/O that otherwise might not happen at all, if the
kernel happens to have sufficient buffer space on hand.
I'll concede though that a large sort would probably have the effect of
blowing out the kernel's disk cache. So while O_DIRECT might be a net
pessimization as far as the sort itself is concerned, it would probably
be more friendly to the rest of the system, by leaving disk buffers free
for more productive uses. It'd all depend on your workload ...
regards, tom lane