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:
- Rename such opclasses and make them not default.
- Create new default opclasses with bitwise comparison functions.
- Write recommendation to re-create indexes with default opclasses into documentation.
------
With best regards,
Alexander Korotkov.