Message-ID: <a050b6067334cf513bf34f9c7bc925d651160f3a.camel@j-davis.com>
Subject: HashAgg degenerate case
From: Jeff Davis <pgsql@j-davis.com>
To: pgsql-bugs@postgresql.org
Date: Tue, 05 Nov 2024 16:59:56 -0800
Content-Type: multipart/mixed; boundary="=-YkGXPjRUS9ylOtLVVDZk"
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a050b6067334cf513bf34f9c7bc925d651160f3a.camel%40j-davis.com>
Precedence: bulk

--=-YkGXPjRUS9ylOtLVVDZk
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

HashAgg tracks the hash table bucket array (only TupleHashEntryData)
memory differently than the overall hash table (including the
firstTuple) memory.

It can run into a degenerate case when the bucket array grows larger
than the allotted memory for the overall hash table. That, by itself,
shouldn't be a major problem because it would just spill any new groups
and then start over in the next batch.

However, ResetTupleHashTable() calls tuplehash_reset(), and that
preserves the bucket array (just clearing it). That means that the next
time around it's already over the limit, and degenerates to holding a
single tuple.

Unfortunately I don't have a small reproducible case yet. I believe it
would need to be a case where the bucket array is more than half of the
memory limit, and then the hash table needs to grow again (doubling).
The actual case involves parallelism, so maybe that throws off the
estimates in an interesting way.

Fixing it seems fairly easy though: we just need to completely destroy
the hash table each time and recreate it. Something close to the
attached patch (rough).

We might also want to make the degenerate case less painful -- should
we raise the minimum number of groups per batch? One possible problem
would be around groups where the state is large, like ARRAY_AGG(), but
we could have different limits.

Regards,
	Jeff Davis


--=-YkGXPjRUS9ylOtLVVDZk
Content-Disposition: attachment;
	filename*0=v1-0001-HashAgg-completely-rebuild-hash-tables-each-itera.pat;
	filename*1=ch
Content-Type: text/x-patch;
	name="v1-0001-HashAgg-completely-rebuild-hash-tables-each-itera.patch";
	charset="UTF-8"
Content-Transfer-Encoding: base64

RnJvbSA3NzU3NTBmZDU5ZWY2ZjYyMTRkNDFiMTg3YzNmMWU3YjIzOTYxNTIwIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBKZWZmIERhdmlzIDxqZWZmQGotZGF2aXMuY29tPgpEYXRlOiBU
dWUsIDUgTm92IDIwMjQgMTY6NTI6MTcgLTA4MDAKU3ViamVjdDogW1BBVENIIHYxXSBIYXNoQWdn
OiBjb21wbGV0ZWx5IHJlYnVpbGQgaGFzaCB0YWJsZXMgZWFjaCBpdGVyYXRpb24uCgotLS0KIHNy
Yy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVBZ2cuYyB8IDEwICsrLS0tLS0tLS0KIDEgZmlsZSBjaGFu
Z2VkLCAyIGluc2VydGlvbnMoKyksIDggZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2Jh
Y2tlbmQvZXhlY3V0b3Ivbm9kZUFnZy5jIGIvc3JjL2JhY2tlbmQvZXhlY3V0b3Ivbm9kZUFnZy5j
CmluZGV4IDUzZWFkNzdlY2UuLjliMjlkNjdiN2QgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2V4
ZWN1dG9yL25vZGVBZ2cuYworKysgYi9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlQWdnLmMKQEAg
LTE0NjksMTggKzE0NjksMTMgQEAgYnVpbGRfaGFzaF90YWJsZXMoQWdnU3RhdGUgKmFnZ3N0YXRl
KQogewogCWludAkJCXNldG5vOwogCisJTWVtb3J5Q29udGV4dFJlc2V0KGFnZ3N0YXRlLT5oYXNo
X21ldGFjeHQpOwogCWZvciAoc2V0bm8gPSAwOyBzZXRubyA8IGFnZ3N0YXRlLT5udW1faGFzaGVz
OyArK3NldG5vKQogCXsKIAkJQWdnU3RhdGVQZXJIYXNoIHBlcmhhc2ggPSAmYWdnc3RhdGUtPnBl
cmhhc2hbc2V0bm9dOwogCQlsb25nCQluYnVja2V0czsKIAkJU2l6ZQkJbWVtb3J5OwogCi0JCWlm
IChwZXJoYXNoLT5oYXNodGFibGUgIT0gTlVMTCkKLQkJewotCQkJUmVzZXRUdXBsZUhhc2hUYWJs
ZShwZXJoYXNoLT5oYXNodGFibGUpOwotCQkJY29udGludWU7Ci0JCX0KLQogCQlBc3NlcnQocGVy
aGFzaC0+YWdnbm9kZS0+bnVtR3JvdXBzID4gMCk7CiAKIAkJbWVtb3J5ID0gYWdnc3RhdGUtPmhh
c2hfbWVtX2xpbWl0IC8gYWdnc3RhdGUtPm51bV9oYXNoZXM7CkBAIC0yNjIyLDggKzI2MTcsNyBA
QCBhZ2dfcmVmaWxsX2hhc2hfdGFibGUoQWdnU3RhdGUgKmFnZ3N0YXRlKQogCiAJLyogZnJlZSBt
ZW1vcnkgYW5kIHJlc2V0IGhhc2ggdGFibGVzICovCiAJUmVTY2FuRXhwckNvbnRleHQoYWdnc3Rh
dGUtPmhhc2hjb250ZXh0KTsKLQlmb3IgKGludCBzZXRubyA9IDA7IHNldG5vIDwgYWdnc3RhdGUt
Pm51bV9oYXNoZXM7IHNldG5vKyspCi0JCVJlc2V0VHVwbGVIYXNoVGFibGUoYWdnc3RhdGUtPnBl
cmhhc2hbc2V0bm9dLmhhc2h0YWJsZSk7CisJYnVpbGRfaGFzaF90YWJsZXMoYWdnc3RhdGUpOwog
CiAJYWdnc3RhdGUtPmhhc2hfbmdyb3Vwc19jdXJyZW50ID0gMDsKIAotLSAKMi4zNC4xCgo=


--=-YkGXPjRUS9ylOtLVVDZk--