Re: Huge amount of memory consumed during transaction

henk de wit <henk53602(at)hotmail(dot)com> writes:
> I indeed found them in the logs. Here they are:

It looks to me like you have work_mem set optimistically large. This
query seems to be doing *many* large sorts and hashes:

> HashBatchContext: 262144236 total in 42 blocks; 3977832 free (40 chunks); 258166404 used
> TupleSort: 9429016 total in 11 blocks; 1242544 free (16 chunks); 8186472 used
> HashBatchContext: 262144236 total in 42 blocks; 3977832 free (40 chunks); 258166404 used
> TupleSort: 9429016 total in 11 blocks; 674376 free (20 chunks); 8754640 used
> TupleSort: 9429016 total in 11 blocks; 245496 free (9 chunks); 9183520 used
> TupleSort: 17817624 total in 12 blocks; 3007648 free (14 chunks); 14809976 used
> TupleSort: 276878852 total in 44 blocks; 243209288 free (1727136 chunks); 33669564 used
> TupleSort: 37740568 total in 14 blocks; 5139552 free (21 chunks); 32601016 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> HashBatchContext: 4202580 total in 10 blocks; 927408 free (13 chunks); 3275172 used
> TupleSort: 75489304 total in 18 blocks; 7909776 free (29 chunks); 67579528 used
> TupleSort: 9429016 total in 11 blocks; 155224 free (16 chunks); 9273792 used
> TupleSort: 46129176 total in 15 blocks; 5787984 free (19 chunks); 40341192 used
> TupleSort: 62906392 total in 17 blocks; 8340448 free (16 chunks); 54565944 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> TupleSort: 134209560 total in 24 blocks; 4506232 free (41 chunks); 129703328 used
> TupleSort: 18866200 total in 12 blocks; 2182552 free (17 chunks); 16683648 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> HashBatchContext: 4202580 total in 10 blocks; 927408 free (13 chunks); 3275172 used
> TupleSort: 37740568 total in 14 blocks; 1239480 free (21 chunks); 36501088 used
> TupleSort: 4710424 total in 10 blocks; 307496 free (15 chunks); 4402928 used
> TupleSort: 27254808 total in 13 blocks; 6921864 free (17 chunks); 20332944 used
> TupleSort: 134209560 total in 25 blocks; 6873024 free (39 chunks); 127336536 used
> TupleSort: 39837720 total in 15 blocks; 3136080 free (34 chunks); 36701640 used

and you just plain don't have enough memory for that large a multiple of
work_mem.

regards, tom lane