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.
