Загадка сортировки PostgreSQL: Сказка об алфавитной анархии
Недавно я столкнулся с озадачивающей проблемой в PostgreSQL, которая на первый взгляд выглядела как “это невозможно”.
То, что должно было быть быстрой задачей на пол сторипойнта, превратилось в полноценную задачу на полновесный сторипойнт.
Неожиданный сюрприз сортировки
После добавления сортировки приложение стало себя вести иначе локально и на сервере.
Это было Django-приложение, и поначалу поведение было действительно загадочным.
Но через некоторое время я обнаружил, что проблема заключалась в том, как работает
ORDER BY
.
На моей локальной машине, когда я выполнял:
SELECT * FROM users ORDER BY name;
Результат был таким:
a
A
b
B
Но на сервере я увидел следующее:
A
B
a
b
Тот же запрос, разные результаты. Что происходило?
Виновник: различия в сортировке
На этом этапе было легко догадаться о первопричине: настройки collation.
Моя локальная машина использовала сортировку без учета регистра, которая следовала словарному порядку, в то время как сервер использовал collation “C”, которая сортирует по кодам ASCII.
Попытка исправления: указание сортировки
Моя первая мысль была указать collation для столбца:
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR COLLATE "en_US.utf8";
Этот подход должен был обеспечить согласованную сортировку для столбца во всех запросах. Однако я столкнулся с препятствием…
Осложнение на сервере
Оказалось, что наш сервер PostgreSQL не поддерживал сортировку “en_US.utf8”, которую я пытался использовать.
Решение
Хорошо, другой подход. Я использовал LOWER
в предложении ORDER BY
:
SELECT * FROM users ORDER BY LOWER(name);
Это обеспечивает сортировку без учета регистра. Хотя и недетерминированную. Но для моей прикладной задачи это было не важно.
Дополнительное преимущество заключается в том, что не нужно изменять структуру таблицы.
Чтобы сделать это решение более эффективным, я создал индекс:
CREATE INDEX ON users (LOWER(name));
Этот индекс значительно улучшает производительность запросов при использовании
LOWER
в предложении ORDER BY
.