3 ms·

I've read the linked article and the proposal, and this seems exciting but I'm interested to know more about the real world impact i.e. what kinds of perf gains

I've read the linked article and the proposal, and this seems exciting but I'm interested to know more about the real world impact i.e. what kinds of perf gains might be seen in real world use cases. If you're deeply versed in this maybe you could shine a light on it for the rest of us...

josepheconway 3y ago
I have not tried to measure the performance impact, although I have no doubt that it exists and is significant. Basically it boils down to the difference in performance of doing memcmp() versus strcoll() for every comparison needed to sort your strings. The bigger deal in my view is that it provides at least an option for a Postgres project controlled, built-in, immutable collation. Unlike glibc or ICU which are historically the only options (mostly glibc), and both of which are known to change their collation ordering from time to time completely outside control of the Postgres developers. If you want a deeper dive on why this is an issue, please watch the presentation I gave at pgcon last year found linked here: https://www.pgcon.org/events/pgcon_2023/schedule/session/345-sorting-out-glibc-collation-challenges/ https://www.pgcon.org/events/pgcon_2023/schedule/session/345...
- JohnBooty 3y ago
  Thanks very much! I will watch that as soon as possible. The platform indepenence alone would be worth it, I think, irrespective of any perf gains!
pella 3y ago
from the linked discussion: https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca2... """ v15:Performance: Sotring 10M strings: libc "C" 14s builtin C_UTF8 14s libc "C.utf8" 20s ICU "en-US-x-icu" 31s Running UPPER() on 10M strings: libc "C" 03s builtin C_UTF8 07s libc "C.utf8" 08s ICU "en-US-x-icu" 15s """ """ v20Performance: ASCII-only data: lower initcap upper "C" (libc) 2426 3326 2341 pg_c_utf8 2890 6570 2825 pg_unicode_fast 2929 7140 2893 "C.utf8" (libc) 5410 7810 5397 "en-US-x-icu" 8320 65732 9367 Including non-ASCII data: lower initcap upper "C" (libc) 2630 4677 2548 pg_c_utf8 5471 10682 5431 pg_unicode_fast 5582 12023 5587 "C.utf8" (libc) 8126 11834 8106 "en-US-x-icu" 14473 73655 15112 """
- JohnBooty 3y ago
  Thanks! I guess my question was more about typical query performance moreso than isolated benchmarks focused slowly on sorting. Then again, I've been working with databases long enough to know that there's really no such thing as a "typical query" so now that I think about it, my question was a bit pointless or at least unanswerable.

josepheconway 3y ago

I have not tried to measure the performance impact, although I have no doubt that it exists and is significant. Basically it boils down to the difference in performance of doing memcmp() versus strcoll() for every comparison needed to sort your strings. The bigger deal in my view is that it provides at least an option for a Postgres project controlled, built-in, immutable collation. Unlike glibc or ICU which are historically the only options (mostly glibc), and both of which are known to change their collation ordering from time to time completely outside control of the Postgres developers. If you want a deeper dive on why this is an issue, please watch the presentation I gave at pgcon last year found linked here: https://www.pgcon.org/events/pgcon_2023/schedule/session/345-sorting-out-glibc-collation-challenges/ https://www.pgcon.org/events/pgcon_2023/schedule/session/345...

JohnBooty 3y ago
Thanks very much! I will watch that as soon as possible. The platform indepenence alone would be worth it, I think, irrespective of any perf gains!

pella 3y ago

from the linked discussion: https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca2... """ v15:Performance: Sotring 10M strings: libc "C" 14s builtin C_UTF8 14s libc "C.utf8" 20s ICU "en-US-x-icu" 31s Running UPPER() on 10M strings: libc "C" 03s builtin C_UTF8 07s libc "C.utf8" 08s ICU "en-US-x-icu" 15s """ """ v20Performance: ASCII-only data: lower initcap upper "C" (libc) 2426 3326 2341 pg_c_utf8 2890 6570 2825 pg_unicode_fast 2929 7140 2893 "C.utf8" (libc) 5410 7810 5397 "en-US-x-icu" 8320 65732 9367 Including non-ASCII data: lower initcap upper "C" (libc) 2630 4677 2548 pg_c_utf8 5471 10682 5431 pg_unicode_fast 5582 12023 5587 "C.utf8" (libc) 8126 11834 8106 "en-US-x-icu" 14473 73655 15112 """

JohnBooty 3y ago
Thanks! I guess my question was more about typical query performance moreso than isolated benchmarks focused slowly on sorting. Then again, I've been working with databases long enough to know that there's really no such thing as a "typical query" so now that I think about it, my question was a bit pointless or at least unanswerable.