Message-ID: <2401e86059624be1917fd0fbcd08700f52f2ba12.camel@j-davis.com>
Subject: Re: Default setting for enable_hashagg_disk
From: Jeff Davis <pgsql@j-davis.com>
To: Robert Haas <robertmhaas@gmail.com>, Bruce Momjian <bruce@momjian.us>
Cc: Andres Freund <andres@anarazel.de>, David Rowley <dgrowleyml@gmail.com>,
  Justin Pryzby <pryzby@telsasoft.com>, Melanie Plageman
 <melanieplageman@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: Thu, 25 Jun 2020 10:15:52 -0700
In-Reply-To: 
 <CA+TgmoYi_03kpoukBzVrsVeNWY_BcLOFjUD8vTtt-fNmNy5NCA@mail.gmail.com>
References: 
	<CA+TgmoagMLfruY4y_40oKLmFFdBRVSGraZsTa5ss9wH546EYhQ@mail.gmail.com>
	 <ee1a7be071349b93c01ffb9ef4cf3a6830d9e780.camel@j-davis.com>
	 <CA+TgmoY4-zSMFf8XbpO6uRsMX4vNPPLXvtajAZpG3f9eoEyjdA@mail.gmail.com>
	 <129c8f1739957589584d9525e1d8782eae3f412f.camel@j-davis.com>
	 <CAApHDvqFZikXhAGW=UKZKq1_FzHy+XzmUzAJiNj6RWyTHH4UfA@mail.gmail.com>
	 <20200624090628.GA19640@momjian.us>
	 <CAApHDvrcfOOjRmO5EZk_a_GKd-RUGG+2FTM=Sr4jZUbHaBJqjA@mail.gmail.com>
	 <20200624171203.GB17842@momjian.us>
	 <20200624191900.t4sbfk236gndwoa3@alap3.anarazel.de>
	 <20200624231810.GB13627@momjian.us> <20200624233837.GA21436@momjian.us>
	 <CA+TgmoYi_03kpoukBzVrsVeNWY_BcLOFjUD8vTtt-fNmNy5NCA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Thu, 2020-06-25 at 11:46 -0400, Robert Haas wrote:
> Because the first problem is so bad, most people
> set the value relatively conservatively and just live with the
> performance consequences. But this also means that they have memory
> left over most of the time, so the idea of letting a node burst above
> its work_mem allocation when something unexpected happens isn't
> crazy:
> as long as only a few nodes do that here and there, rather than, say,
> all the nodes doing it all at the same time, it's actually fine.

Unexpected things (meaning underestimates) are not independent. All the
queries are based on the same stats, so if you have a lot of similar
queries, they will all get the same underestimate at once, and all be
surprised when they need to spill at once, and then all decide they are
entitled to ignore work_mem at once.

>  If we
> had a smarter system that could dole out more work_mem to nodes that
> would really benefit from it and less to nodes where it isn't likely
> to make much difference, that would be similar in spirit but even
> better.

That sounds more useful and probably not too hard to implement in a
crude form. Just have a shared counter in memory representing GB. If a
node is about to spill, it could try to decrement the counter by N, and
if it succeeds, it gets to exceed work_mem by N more GB.

Regards,
	Jeff Davis