Collation-aware comparisons in GIN opclasses - Mailing list pgsql-hackers

From Alexander Korotkov
Subject Collation-aware comparisons in GIN opclasses
Date
Msg-id CAPpHfdtxXMjyZxwND09ZLBBACVbWb5J9bLUf67CndR4VKFDgwg@mail.gmail.com
Whole thread Raw
Responses Re: Collation-aware comparisons in GIN opclasses
Re: Collation-aware comparisons in GIN opclasses
List pgsql-hackers
Hackers,

some GIN opclasses uses collation-aware comparisons while they don't need to do especially collation-aware comparison. Examples are text[] and hstore opclasses. Depending on collation this may make them a much slower.

See example.

# show lc_collate ;
 lc_collate
─────────────
 ru_RU.UTF-8
(1 row)

# create table test as (select array_agg(i::text) from generate_series(1,1000000) i group by (i-1)/10);
SELECT 100000

# create index test_idx on test using gin(array_agg);
CREATE INDEX
Time: 26930,423 ms

# create index test_idx2 on test using gin(array_agg collate "C");
CREATE INDEX
Time: 5143,682 ms

Index creation with collation "ru_RU.UTF-8" is 5 times slower while collation has absolutely no effect on index functionality. 

However, we can just replace comparison function for those opclasses because it would break binary compatibility for pg_upgrade. I see following solution:
  1. Rename such opclasses and make them not default.
  2. Create new default opclasses with bitwise comparison functions.
  3. Write recommendation to re-create indexes with default opclasses into documentation.
------
With best regards,
Alexander Korotkov.  

pgsql-hackers by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: delta relations in AFTER triggers
Next
From: Jehan-Guillaume de Rorthais
Date:
Subject: Re: .ready files appearing on slaves