OK, I did a little more testing. On one of our tables with 1.25 million
rows of semi-unique data (it's a keyword table, small row size, lots of
keywords, many repeaters) Some words occur once, some occur 1200 times,
most occur 3 to 10 times.
This test box has 512 Megs of RAM, and other than having X running it a
pretty close match to the servers we use. (1.1 Gigahertz CPU, running a
4x2G RAID5 drive set).
Shared buffers set to ~ 32 Megabyte (4000*8k)
I ran the following query in parallel by four psql sessions:
select distinct word from wordtable;
with sort_mem set to 64 Megs, my workstation, which sits at 0 used swap
and about 300 Megs of system buff/cache, used all the available memory,
and about 600 Megs of swap to run those four queries, and one of them
errored out with "ERROR: MemoryContextAlloc: invalid request size
4294967293" The run time was very long, with lots of swapping going on.
This was with only four processes connected. Each one used about 130 Megs
of ram according to top. subtracting 32Megs of shared, that would be
about 100 Megs of individual memory per.
With sort_mem set to 8 megs, the four queries used up all my ram, pushing
100 Megs into swap. The queries were much faster. About 180 seconds.
The test with 64 Meg sort_mem was about 8 minutes or so. (I stopped
checking after about 5 minutes. I used explain analyze for all the tests
on less than 64 Megs.)
Next I tested with 2Megs sort memory. Now I had a fair bit of ram left
over (about 100 Meg), and the queries each took about 135 seconds to run.
I'd suggest lowering sort_mem to something more reasonable, unless you
have a test case that shows a marked performance increase with 64 Megs of
sort_mem. All mine point to 1 to 4 megs being perfect for sort_mem on
most queries.
Good luck.