<snip>
... under the periods of heavy swapping, one or more of the postgres
processes would be way up there (between 500MB and 1000MB (which would
easily explain the swapping)) ... the question is: why aren't all of the
processes sharing the same pool of shared memory since I thought that's what
I'm doing when adjusting the shared_buffers property?
<snip>
I seem to remember hearing that the memory limit on certain operations, such as sorts, is not "enforced" (may the hackers correct me if I am wrong); rather, the planner estimates how much a sort might take by looking at the statistics for a table.
If the statistics are wrong, however, the sort doesn't actually stay within sort memory, and so the process consumes a very large amount of memory, much more than the sort_mem configuration parameter should allow it to.
If the other suggestions given (to reduce the shared buffers) don't fix it, I suggest running ANALYZE all your tables and see if the erratic memory usage goes away. If that doesn't help, then try to figure out what query is causing the high memory usage, and run EXPLAIN ANALYZE on just that query to see if it is returning drastically more rows than the planner thinks it will.
Paul Tillotson
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
