Thread: [GENERAL] PG-10 + ICU and abbreviated keys
Hi.
In PG-10, with ICU enabled, is abbreviated keys now enabled?
If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to take advantage of abbreviated keys?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
På mandag 13. november 2017 kl. 19:07:53, skrev Peter Geoghegan <pg@bowt.ie>:
On Mon, Nov 13, 2017 at 12:27 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> In PG-10, with ICU enabled, is abbreviated keys now enabled?
Yes. ICU will use abbreviated keys on every platform, including Windows.
> If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to take advantage of abbreviated keys?
You need to use an ICU collation. It must be a per-column collation,
as you cannot currently use ICU for an entire database. (This
limitation should be removed in the next release or two.)
Ok, so I have to explicitly specify like this:
create table test(id serial primary key, name varchar collate "nb_NO" not null);
Will ICU be used here as long as PG is compiled with ICU-suppoert, as the debian-packages are, or do I have to specify collation-provider?
Do I have to explicitly specify collation when using ORDER by on that column for index and abbreviated keys to be used?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
På mandag 13. november 2017 kl. 21:40:47, skrev Peter Geoghegan <pg@bowt.ie>:
On Mon, Nov 13, 2017 at 11:48 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> Ok, so I have to explicitly specify like this:
>
> create table test(id serial primary key, name varchar collate "nb_NO" not null);
That doesn't look like an ICU locale. You may mean "nb-NO-x-icu". But
otherwise, yes.
Ok, is there a way I can get a list of ICU-collations?
> Will ICU be used here as long as PG is compiled with ICU-suppoert, as the debian-packages are, or do I have to specify collation-provider?
If you did initdb with a version with ICU support, the ICU collations
should be there.
> Do I have to explicitly specify collation when using ORDER by on that column for index and abbreviated keys to be used?
Only if you didn't define the column with a per-column collation initially.
Ok, thanks.
Looking forward to this being a per-database setting so it's (hopefully) more transparent.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
På mandag 13. november 2017 kl. 21:46:08, skrev Peter Geoghegan <pg@bowt.ie>:
On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> Do I have to explicitly specify collation when using ORDER by on that column for index and abbreviated keys to be used?
>
> Only if you didn't define the column with a per-column collation initially.
BTW, if you specifically want to quickly verify whether or not
abbreviated keys were used, you can do that by setting "trace_sort =
on", and possibly setting "client_min_messages = LOG", too.
There should be quite a bit of debug output from that that
specifically mentions abbreviated keys.
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
På mandag 13. november 2017 kl. 22:28:40, skrev Peter Geoghegan <pg@bowt.ie>:
On Mon, Nov 13, 2017 at 12:48 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> Thanks.
As the person that worked on abbreviated keys, I'd like to hear about
how you get with this. How much faster is it for you?
I don't usually get to hear about this, because most users don't
notice that anything in particular gets faster, because there are many
performance enhancements added to a release.
We haven't migrated any of our databases to v10 yet so I really can't tell. I'm evaluating ICU-usage as the last step before we decide moving to v10. Being a per-column setting that means a pg_dump/reload won't cut it (AFAIU), so I'm not sure we'll take that route as it involves much manual tweaking which we're really not interessted in spending time on.
When sorting on text, we're usually doing so using an multi-column index, like for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created ASC)". Will abbreviated keys help here?
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
På tirsdag 14. november 2017 kl. 00:44:11, skrev Peter Geoghegan <pg@bowt.ie>:
On Mon, Nov 13, 2017 at 2:05 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> When sorting on text, we're usually doing so using an multi-column index, like for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created ASC)". Will abbreviated keys help here?
Yes, they'll help with that, even though the leading column might be
low cardinality.
Nice to know, thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963