Subject: Re: Correct the documentation for work_mem

From: Tom Lane <[email-masked]>
Date: Fri, 21 Apr 2023 13:15:01 -0400

Peter Eisentraut <[email-masked]> writes:
> On 21.04.23 16:28, Imseih (AWS), Sami wrote:
>> I suggest a small doc fix:
>> “Note that for a complex query, several sort or hash operations might be 
>> running simultaneously;”

> Here is a discussion of these terms: 
> https://takuti.me/note/parallel-vs-concurrent/

> I think "concurrently" is the correct word here.

Probably, but it'd do little to remove the confusion Sami is on about,
especially since the next sentence uses "concurrently" to describe the
other case.  I think we need a more thorough rewording, perhaps like

-       Note that for a complex query, several sort or hash operations might be
-       running in parallel; each operation will generally be allowed
+       Note that a complex query may include several sort or hash
+       operations; each such operation will generally be allowed
        to use as much memory as this value specifies before it starts
        to write data into temporary files.  Also, several running
        sessions could be doing such operations concurrently.

I also find this wording a bit further down to be poor:

        Hash-based operations are generally more sensitive to memory
        availability than equivalent sort-based operations.  The
        memory available for hash tables is computed by multiplying
        <varname>work_mem</varname> by
        <varname>hash_mem_multiplier</varname>.  This makes it

I think "available" is not le mot juste, and it's also unclear from
this whether we're speaking of the per-hash-table limit or some
(nonexistent) overall limit.  How about

-       memory available for hash tables is computed by multiplying
+       memory limit for a hash table is computed by multiplying

			regards, tom lane

---

From: Gurjeet Singh <[email-masked]>
Date: Fri, 21 Apr 2023 10:39:45 -0700

> Probably, but it'd do little to remove the confusion Sami is on about,

+1.

When discussing this internally, Sami's proposal was in fact to use
the word 'concurrently'. But given that when it comes to computers and
programming, it's common for someone to not understand the intricate
difference between the two terms, we thought it's best to not use any
of those, and instead use a word not usually associated with
programming and algorithms.

Aside: Another pair of words I see regularly used interchangeably,
when in fact they mean different things: precise vs. accurate.

> +       operations; each such operation will generally be allowed

This wording doesn't seem to bring out the fact that there could be
more than one work_mem consumer running (in-progress) at the same
time. The reader to could mistake it to mean hashes and sorts in a
complex query may happen one after the other.

+ Note that a complex query may include several sort and hash operations, and
+ more than one of these operations may be in progress simultaneously at any
+ given time;  each such operation will generally be allowed

I believe the phrase "several sort _and_ hash" better describes the
possible composition of a complex query, than does "several sort _or_
hash".

> +       memory limit for a hash table is computed by multiplying

+1

Best regards,
Gurjeet https://Gurje.et
Postgres Contributors Team, http://aws.amazon.com

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Sat, 22 Apr 2023 03:36:15 +0000

> time. 

Do you mean, more than one work_mem consumer running at the same
time for a given query? If so, that is precisely the point we need to convey
in the docs.

i.e. if I have 2 sorts in a query that can use up to 4MB each, at some point
in the query execution, I can have 8MB of memory allocated.

Regards,

Sami Imseih
Amazon Web Services (AWS)

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Mon, 24 Apr 2023 16:20:08 +0000

Based on the feedback, here is a v1 of the suggested doc changes.

I modified Gurjeets suggestion slightly to make it clear that a specific
query execution could have operations simultaneously using up to 
work_mem.

I also added the small hash table memory limit clarification.

Regards,

Sami Imseih

---

From: David Rowley <[email-masked]>
Date: Thu, 13 Jul 2023 11:11:49 +1200

> +        Note that a complex query may include several sort and hash operations,
> +        and more than one of these operations may be in progress simultaneously
> +        for a given query execution; each such operation will generally be allowed

I'm wondering about adding "and more than one of these operations may
be in progress simultaneously".  Are you talking about concurrent
sessions running other queries which are using work_mem too?  If so,
isn't that already covered by the final sentence in the quoted text
above? if not, what is running simultaneously?

I think Tom's suggestion looks fine. I'd maybe change "sort or hash"
to "sort and hash" per the suggestion from Gurjeet above.

David

---

From: Tristen Raab <[email-masked]>
Date: Mon, 31 Jul 2023 20:44:21 +0000

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            tested, passed

Hello,

I've reviewed and built the documentation for the updated patch. As it stands right now I think the documentation for this section is quite clear.

> sessions running other queries which are using work_mem too?

This appears to be referring to the "sort and hash" operations mentioned prior.

> If so,

I believe the last sentence is referring to another session that is running its own sort and hash operations. So the first section you mention is describing how sort and hash operations can be in execution at the same time for a query, while the second refers to how sessions may overlap in their execution of sort and hash operations if I am understanding this correctly.

I also agree that changing "sort or hash" to "sort and hash" is a better description.

Tristen

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 7 Sep 2023 20:16:21 -0400

> +       memory limit for a hash table is computed by multiplying

Adjusted patch attached.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6bc1b215db..45d1bb4b7b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1829,9 +1829,10 @@ include_dir 'conf.d'
         (such as a sort or hash table) before writing to temporary disk files.
         If this value is specified without units, it is taken as kilobytes.
         The default value is four megabytes (<literal>4MB</literal>).
-        Note that for a complex query, several sort or hash operations might be
-        running in parallel; each operation will generally be allowed
-        to use as much memory as this value specifies before it starts
+        Note that a complex query might perform several sort or hash
+        operations at the same time, with each operation generally being
+        allowed to use as much memory as this value specifies before
+        it starts
         to write data into temporary files.  Also, several running
         sessions could be doing such operations concurrently.
         Therefore, the total memory used could be many times the value
@@ -1845,7 +1846,7 @@ include_dir 'conf.d'
        <para>
         Hash-based operations are generally more sensitive to memory
         availability than equivalent sort-based operations.  The
-        memory available for hash tables is computed by multiplying
+        memory limit for a hash table is computed by multiplying
         <varname>work_mem</varname> by
         <varname>hash_mem_multiplier</varname>.  This makes it
         possible for hash-based operations to use an amount of memory

---

From: David Rowley <[email-masked]>
Date: Fri, 8 Sep 2023 17:23:28 +1200

> Adjusted patch attached.

This looks mostly fine to me modulo "sort or hash".  I do see many
instances of "and/or" in the docs. Maybe that would work better.

David

---

From: "Imseih (AWS), Sami" <[email-masked]>
Date: Sat, 9 Sep 2023 02:25:30 +0000

> instances of "and/or" in the docs. Maybe that would work better.

"sort or hash operations at the same time" is clear explanation IMO.

This latest version of the patch looks good to me.

Regards,

Sami

---

From: David Rowley <[email-masked]>
Date: Mon, 11 Sep 2023 22:02:55 +1200

> "sort or hash operations at the same time" is clear explanation IMO.

Just for anyone else following along that haven't seen the patch. The
full text in question is:

It's certainly not a show-stopper. I do believe the patch makes some
improvements.  The reason I'd prefer to see either "and" or "and/or"
in place of "or" is because the text is trying to imply that many of
these operations can run at the same time. I'm struggling to
understand why, given that there could be many sorts and many hashes
going on at once that we'd claim it could only be one *or* the other.
If we have 12 sorts and 4 hashes then that's not "several sort or hash
operations", it's "several sort and hash operations".  Of course, it
could just be sorts or just hashes, so "and/or" works fine for that.

David

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 11 Sep 2023 11:03:14 -0400

> could just be sorts or just hashes, so "and/or" works fine for that.

Yes, I see your point and went with "and",   updated patch attached.

index 6bc1b215db..8ed7ae57c2 100644
+        Note that a complex query might perform several sort and hash

---

From: David Rowley <[email-masked]>
Date: Wed, 27 Sep 2023 02:05:44 +1300

> Yes, I see your point and went with "and",   updated patch attached.

Looks good to me.

David

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 26 Sep 2023 19:44:44 -0400

> > Yes, I see your point and went with "and",   updated patch attached.

Patch applied back to Postgres 11.