Message-ID: <7bb9f018d20a7b30b9a7f6231efab1b5e50c7720.camel@j-davis.com>
Subject: Speed up collation cache
From: Jeff Davis <pgsql@j-davis.com>
To: pgsql-hackers@postgresql.org
Date: Fri, 14 Jun 2024 16:46:39 -0700
Content-Type: multipart/mixed; boundary="=-MByyWPS/y50cophDGOIS"
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/7bb9f018d20a7b30b9a7f6231efab1b5e50c7720.camel%40j-davis.com>
Precedence: bulk

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

The blog post here (thank you depesz!):

https://www.depesz.com/2024/06/11/how-much-speed-youre-leaving-at-the-table=
-if-you-use-default-locale/

showed an interesting result where the builtin provider is not quite as
fast as "C" for queries like:

   SELECT * FROM a WHERE t =3D '...';

The reason is that it's calling varstr_cmp() many times, which does a
lookup in the collation cache for each call. For sorts, it only does a
lookup in the collation cache once, so the effect is not significant.

The reason looking up "C" is faster is because there's a special check
for C_COLLATION_OID, so it doesn't even need to do the hash lookup. If
you create an equivalent collation like:

   CREATE COLLATION libc_c(PROVIDER =3D libc, LOCALE =3D 'C');

it will perform the same as a collation with the builtin provider.

Attached is a patch to use simplehash.h instead, which speeds things up
enough to make them fairly close (from around 15% slower to around 8%).

The patch is based on the series here:

https://postgr.es/m/f1935bc481438c9d86c2e0ac537b1c110d41a00a.camel@j-davis.=
com

which does some refactoring in a related area, but I can make them
independent.

We can also consider what to do about those special cases:

  * add a special case for PG_C_UTF8?
  * instead of a hardwired set of special collation IDs, have a single-
element "last collation ID" to check before doing the hash lookup?
  * remove the special cases entirely if we can close the performance
gap enough that it's not important?

(Note: the special case in lc_ctpye_is_c() is currently required for
correctness because hba.c uses C_COLLATION_OID for regexes before the
syscache is initialized. That can be fixed pretty easily a couple
different ways, though.)

--=20
Jeff Davis
PostgreSQL Contributor Team - AWS


--=-MByyWPS/y50cophDGOIS
Content-Disposition: attachment;
	filename="v2-0007-Change-collation-cache-to-use-simplehash.h.patch"
Content-Type: text/x-patch;
	name="v2-0007-Change-collation-cache-to-use-simplehash.h.patch";
	charset="UTF-8"
Content-Transfer-Encoding: base64

RnJvbSA3NzcxODZhNDE5NTVkYThkMDU5MjlmNWMzNGU1MzFkZmE5ODViNTEzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBKZWZmIERhdmlzIDxqZWZmQGotZGF2aXMuY29tPgpEYXRlOiBG
cmksIDE0IEp1biAyMDI0IDE1OjM4OjQyIC0wNzAwClN1YmplY3Q6IFtQQVRDSCB2MiA3LzddIENo
YW5nZSBjb2xsYXRpb24gY2FjaGUgdG8gdXNlIHNpbXBsZWhhc2guaC4KCi0tLQogc3JjL2JhY2tl
bmQvdXRpbHMvYWR0L3BnX2xvY2FsZS5jIHwgMzkgKysrKysrKysrKysrKysrKysrKysrLS0tLS0t
LS0tLQogMSBmaWxlIGNoYW5nZWQsIDI3IGluc2VydGlvbnMoKyksIDEyIGRlbGV0aW9ucygtKQoK
ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9wZ19sb2NhbGUuYyBiL3NyYy9iYWNr
ZW5kL3V0aWxzL2FkdC9wZ19sb2NhbGUuYwppbmRleCA0MzVhMzdhMGUzLi5iNzFjYTJkNzgwIDEw
MDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQvcGdfbG9jYWxlLmMKKysrIGIvc3JjL2Jh
Y2tlbmQvdXRpbHMvYWR0L3BnX2xvY2FsZS5jCkBAIC01NywxMiArNTcsMTIgQEAKICNpbmNsdWRl
ICJhY2Nlc3MvaHR1cF9kZXRhaWxzLmgiCiAjaW5jbHVkZSAiY2F0YWxvZy9wZ19jb2xsYXRpb24u
aCIKICNpbmNsdWRlICJjYXRhbG9nL3BnX2RhdGFiYXNlLmgiCisjaW5jbHVkZSAiY29tbW9uL2hh
c2hmbi5oIgogI2luY2x1ZGUgIm1iL3BnX3djaGFyLmgiCiAjaW5jbHVkZSAibWlzY2FkbWluLmgi
CiAjaW5jbHVkZSAidXRpbHMvYnVpbHRpbnMuaCIKICNpbmNsdWRlICJ1dGlscy9mb3JtYXR0aW5n
LmgiCiAjaW5jbHVkZSAidXRpbHMvZ3VjX2hvb2tzLmgiCi0jaW5jbHVkZSAidXRpbHMvaHNlYXJj
aC5oIgogI2luY2x1ZGUgInV0aWxzL2xzeXNjYWNoZS5oIgogI2luY2x1ZGUgInV0aWxzL21lbXV0
aWxzLmgiCiAjaW5jbHVkZSAidXRpbHMvcGdfbG9jYWxlLmgiCkBAIC0xMjksMTAgKzEyOSwyNyBA
QCB0eXBlZGVmIHN0cnVjdAogewogCU9pZAkJCWNvbGxpZDsJCQkvKiBoYXNoIGtleTogcGdfY29s
bGF0aW9uIE9JRCAqLwogCXBnX2xvY2FsZV90IGxvY2FsZTsJCQkvKiBsb2NhbGVfdCBzdHJ1Y3Qs
IG9yIDAgaWYgbm90IHZhbGlkICovCi19IGNvbGxhdGlvbl9jYWNoZV9lbnRyeTsKIAotc3RhdGlj
IEhUQUIgKmNvbGxhdGlvbl9jYWNoZSA9IE5VTEw7CisJLyogbmVlZGVkIGZvciBzaW1wbGVoYXNo
ICovCisJdWludDMyCQloYXNoOworCWNoYXIJCXN0YXR1czsKK30gY29sbGF0aW9uX2NhY2hlX2Vu
dHJ5OwogCisjZGVmaW5lIFNIX1BSRUZJWAkJY29sbGF0aW9uX2NhY2hlCisjZGVmaW5lIFNIX0VM
RU1FTlRfVFlQRQljb2xsYXRpb25fY2FjaGVfZW50cnkKKyNkZWZpbmUgU0hfS0VZX1RZUEUJCU9p
ZAorI2RlZmluZSBTSF9LRVkJCQljb2xsaWQKKyNkZWZpbmUgU0hfSEFTSF9LRVkodGIsIGtleSkg
ICAJaGFzaF91aW50MzIoKHVpbnQzMikga2V5KQorI2RlZmluZSBTSF9FUVVBTCh0YiwgYSwgYikJ
CShhID09IGIpCisjZGVmaW5lIFNIX0dFVF9IQVNIKHRiLCBhKQkJYS0+aGFzaAorI2RlZmluZSBT
SF9TQ09QRQkJc3RhdGljIGlubGluZQorI2RlZmluZSBTSF9TVE9SRV9IQVNICisjZGVmaW5lIFNI
X0RFQ0xBUkUKKyNkZWZpbmUgU0hfREVGSU5FCisjaW5jbHVkZSAibGliL3NpbXBsZWhhc2guaCIK
Kworc3RhdGljIE1lbW9yeUNvbnRleHQgQ29sbGF0aW9uQ2FjaGVDb250ZXh0ID0gTlVMTDsKK3N0
YXRpYyBjb2xsYXRpb25fY2FjaGVfaGFzaCAqQ29sbGF0aW9uQ2FjaGUgPSBOVUxMOwogCiAjaWYg
ZGVmaW5lZChXSU4zMikgJiYgZGVmaW5lZChMQ19NRVNTQUdFUykKIHN0YXRpYyBjaGFyICpJc29M
b2NhbGVOYW1lKGNvbnN0IGNoYXIgKik7CkBAIC0xMjM1LDE4ICsxMjUyLDE2IEBAIGxvb2t1cF9j
b2xsYXRpb25fY2FjaGUoT2lkIGNvbGxhdGlvbikKIAlBc3NlcnQoT2lkSXNWYWxpZChjb2xsYXRp
b24pKTsKIAlBc3NlcnQoY29sbGF0aW9uICE9IERFRkFVTFRfQ09MTEFUSU9OX09JRCk7CiAKLQlp
ZiAoY29sbGF0aW9uX2NhY2hlID09IE5VTEwpCisJaWYgKENvbGxhdGlvbkNhY2hlID09IE5VTEwp
CiAJewotCQkvKiBGaXJzdCB0aW1lIHRocm91Z2gsIGluaXRpYWxpemUgdGhlIGhhc2ggdGFibGUg
Ki8KLQkJSEFTSENUTAkJY3RsOwotCi0JCWN0bC5rZXlzaXplID0gc2l6ZW9mKE9pZCk7Ci0JCWN0
bC5lbnRyeXNpemUgPSBzaXplb2YoY29sbGF0aW9uX2NhY2hlX2VudHJ5KTsKLQkJY29sbGF0aW9u
X2NhY2hlID0gaGFzaF9jcmVhdGUoIkNvbGxhdGlvbiBjYWNoZSIsIDEwMCwgJmN0bCwKLQkJCQkJ
CQkJCSAgSEFTSF9FTEVNIHwgSEFTSF9CTE9CUyk7CisJCUNvbGxhdGlvbkNhY2hlQ29udGV4dCA9
IEFsbG9jU2V0Q29udGV4dENyZWF0ZShUb3BNZW1vcnlDb250ZXh0LAorCQkJCQkJCQkJCQkJCSAg
ImNvbGxhdGlvbiBjYWNoZSIsCisJCQkJCQkJCQkJCQkJICBBTExPQ1NFVF9ERUZBVUxUX1NJWkVT
KTsKKwkJQ29sbGF0aW9uQ2FjaGUgPSBjb2xsYXRpb25fY2FjaGVfY3JlYXRlKAorCQkJQ29sbGF0
aW9uQ2FjaGVDb250ZXh0LCAxMjgsIE5VTEwpOwogCX0KIAotCWNhY2hlX2VudHJ5ID0gaGFzaF9z
ZWFyY2goY29sbGF0aW9uX2NhY2hlLCAmY29sbGF0aW9uLCBIQVNIX0VOVEVSLCAmZm91bmQpOwor
CWNhY2hlX2VudHJ5ID0gY29sbGF0aW9uX2NhY2hlX2luc2VydChDb2xsYXRpb25DYWNoZSwgY29s
bGF0aW9uLCAmZm91bmQpOwogCWlmICghZm91bmQpCiAJewogCQkvKgotLSAKMi4zNC4xCgo=


--=-MByyWPS/y50cophDGOIS--