Re: using a lot of maintenance_work_mem

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Tom Lane wrote:
> >> You seem to be confusing one limitation in one code path with the
> >> overall meaning of maintenance_work_mem.
>
> > Oh, OK, so sorts are limited, but not hash sizes? Are there any other
> > uses? Should this be documented somehow? What is the actual sort
> > limit?
>
> The particular complaint that's being made here is about tuplesort.c's
> array of SortTuples, which isn't all (or even the largest part) of its
> memory consumption. The tuples themselves eat significantly more in
> nearly all cases. I don't think there's any very easy way to document
> what the largest useful maintenance_work_mem for sorting is based on
> that --- you'd have to pull a number for tuple size out of the air.
> But it's certainly possible to use up lots of gigabytes when sorting
> wide tuples. I think the original complaint in this thread was about
> building an index, which probably had relatively small tuples so the
> SortTuple constraint was more pressing.
>
> In any case, this is the sort of thing that'd be far better to fix than
> document.

Added to TODO:

Allow sorts to use more available memory

* http://archives.postgresql.org/pgsql-hackers/2007-11/msg01026.php
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg01123.php
* http://archives.postgresql.org/pgsql-hackers/2011-02/msg01957.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +