Me again (The guy with the huge tables)
Postgres is started with:
exec /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -o '-F -B
64 -S 262144 -o /var/log/postgres/errors' &
The important part here is the -S 262144. I want it to use up to 256 megs
of RAM for sorting. Datasize limits for user postgres are currently set at
512M.
The table in question currently has 5103416 rows in it. Very simple
struct:
word_id int4
url_id int4
word_count int2
Now when I run:
psql -c 'select * from word_detail order by word_id' searchengine>test.out
FATAL 1: palloc failure: memory exhausted
Here is a top entry:
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
670 postgres105 0 411M 195M RUN 5:27 73.66% 73.66% postgres
Soon thereafter it croaks after running out of swap. I expected that it
should have stopped gobbling up memory once it hit the 256 meg mark (sort
buffer max size)
Of course the bug here might be in the user :)
It's an Intel machine running FreeBSD 2.2.6-STABLE. Has lots of RAM,
320megs or something like that I believe.
One question about the guts. What algo does postgres run for the internal
sort. Is it quicksort then externally mergesort, or is it mergesort all
the way...?
Thanks
-Mike