Подскажите, нужно сделать поиск по полям регистронезависимый для всей базы

для всех полей. Таблицы в UTF-8. Как в настройках сервера постгри указать, что поиск должен не учитывать регистр? Какой коллейт и где нужно прописать?

расширение citext?

пробовал, производительность в приложении упала

Explain analyse?

после эксперимента уже вернул все в зад, оставив varchar(34)

Ну, тоже метод.

а глобальная настройка коллейта не спасет отца русской демократии?

Ну, как минимум грузить медленнее не должно.

Эээ... а как Вы это себе иначе представляете, извините (каким бы то ни было методом)?

Насколько я понимаю, время сравнения строк в ci-collation типично всё равно несопостваимо с остальным поиском индэкса и tuple deforming.

Сопоставимо или нет — это дело второе, суть в том, что [на обычном железе] иначе не может быть.

Если это нельзя померить — то как можно говорить, что это есть?

И я, кстати, не знаю, какие алгоритмы применены в collation — так что вот не 100% уверен, что там реальная сложность как-то отличается.

В некоторых ситуациях разницу между доступом по индексу и без... и т.д. и т.п. нельзя померить — как можно говорить, что это есть? ;) Кроме шуток — разница между обычными и case-insensitive сравнениями будет существенно влиять на результат, если в запросе их требуется выполнить много (или много самих таких запросов). > что там реальная сложность как-то отличается. Вы сравниваете сложность "делать что-то" vs "не делать ничего", между прочим.

Так в этих ситуацыях её и нет. Но... Я просто не вижу большой алгоритмической разницы. Там (в libc или в icu) есть какие-то несколько чисел, привешэнных каждому символу. Они ищутся, видимо, в каком-то дереве, которое не сильно отличается для ci/не-ci. С чего там будет большая разница — я вот просто не очень понимаю.

По мне так разница по меньшей мере в том, что в общем случае при регистронезависимом поиске может быть возвращено больше результатов (чем в противоположном случае) со всеми вытекающими, оттого и появляется справедливость утверждения что искомое может быть медленнее. А вот будет ли это наверняка так — следует подробнее посмотреть, как там это сравнение работает, да.

Стикер

И ужэ по этой таблицэ видно, что "your mileage may vary". В итоге — в одном месте icu/case insensitive дажэ обогнало всех (в том жэ месте glibc отстало катастрофически и проиграло только citext), в другом месте icu/case insensitive отстало катастрофически, и проиграло только citext, и вообще все три подхода показали совершэнно разные результаты. (Кроме одного — что citext отсатёт).

А Вы-то написали что, а? Так я напомню (из вредности): > Но... Я просто не вижу большой алгоритмической разницы. А разница не только есть, она (в некоторых ситуациях) составляет один-два порядка! > С чего там будет большая разница — я вот просто не очень понимаю А между тем, такие причины существуют (эти результаты совсем не случайны).

>А разница не только есть, она (в некоторых ситуациях) составляет один-два порядка! Судя по тому, что в разных случаях эта разница в разную сторону — это не алгоритмические проблемы, а какие-то ещё?

К тому жэ, два порядка тут разве что двоичных.

Так реализованы сравнения (collation algorithms), и существенно лучшей реализации (для каждого случая, кроме citext) никто нам не сможет показать, мне кажется. Т.е. тут проблема не в том, что у кого-то "руки кривые". > К тому жэ, два порядка тут разве что двоичных Кхе-кхе... 5820 / 621 ≈ 10, а 25102 / 702 ≈ 36. Двоичных порядков тут куда побольше. ;)

Ну, 25102 — это citext, с которым как раз всё понятно. А 5820, учитывая, что делает он примерно тожэ самое, что 702 — вполне ясно, что это какие-то внутренние проблемы постгреса.

Знаете что... да сколько можно уже?! Я и раньше слышал эти Ваши сказки про "внутренние проблемы постгреса" (насчёт партиционирования и т.п.) в подобных ситуациях. У меня уже складывается впечатление, что каждый раз, как Вы чего-то не знаете, так начинается вот это вот. Короче говоря, если Вы это утверждаете — покажите нам, как надо, не стесняйтесь, "гуру" Вы наш. ;( А пока тут простая дилемма — либо разработчики PostgreSQL, ICU и libc не умеют писать код, либо Вы ошибаетесь. И лично я знаю, на что я ставлю.

> покажите нам, как надо, не стесняйтесь, "гуру" Вы наш. ;( Не требуется быть поваром, чтобы оцэнить вкус борща!

>PostgreSQL, ICU и libc не умеют писать код, л И я этого не говорил. Про postgres — так прямо и не согласен! Это твоё личное мнение, что такие небольшые детали свидетельствуют о неумении писать код!

> Не требуется быть поваром, чтобы оцэнить вкус борща! А кем надо быть, чтоб пытаться свысока судить о программистах куда получше себя, а? ;) > что такие небольшые детали свидетельствуют о неумении писать код! Это не "небольшие детали", а краеугольный камень производительности примерно всех операций с текстами в PostgreSQL. И оптимизировалось в этой области всё не раз (и ещё будет, скорее всего) — код там [далеко] не "наивный".

И как это в итоге получилось, что в этом "ненаивном" коде glibc отстаёт от ICU в разы? На примерно одной и той жэ операцыи? Как hash join отстаёт — ещё понятно (хотя там тожэ есть что поправить) — а вон тот, результат в первом столбцэ как в такой супер-оптимизированной системе получился-то?

Потому что они делают (должны делать, в смысле) существенно разные вещи. Но что касается именно авторов libc... тут у меня тоже есть сомнения, это да. ;)

Кто "они"? strcoll() в GLIBC и ucoll_strcoll() в ICU?

>Но что касается именно авторов libc.. Кстати, в индэксном случае — там наоборот icu проиграло libc (и тожэ на ровном месте, видимо).

Да. Т.е. то, как определяются collations, и как они должны работать, в libc и ICU отличается очень существенно — несмотря на то, результаты в тривиальных случаях 1:1. ;)

Нет же? "Обычный" случай libc — это default. Collation | ORDER BY val | Search (hashing) | Search (index-only scan) ------------------+--------------+------------------+-------------------------- default | 5820 | 630 | 7684 ICU | 702 | 688 | 4527

А, я большэ в sqlize.online смотрю — там libc выигрывает в три раза.

То есть опять, зависимость от collation есть — но в цэлом во-первых результаты сопоставимы, во-вторых нельзя сказать, что кто-то вот так сразу тормозит большэ...

Да это запросто может быть просто "шум" — данных там слишком мало, чтобы делать какие-то выводы. На то (в том числе) и был этот sqlfiddle — чтобы каждый мог у себя попробовать, на более адекватных размерах (и известных значениях shared_buffers и т.п.). > но в цэлом во-первых результаты сопоставимы Разница почти 70%, всё же. И ICU в среднем явно лучше.

То есть в среднем case sensitive collation окажэтся лучшэ, просто потому, что дефолт у нас glibc, а этот — icu...

Просто для информации — вот сейчас (через знакомых) удалось добраться до автора citext (David Wheeler), и на [вежливый] вопрос в стиле "Что это за @$%^ня, неужели так должно быть?!" он ответил: "That sounds like how I recall it working, yes." Выводы делайте какие хотите. ;(

Да в общем, от него и отказались потому, что подход так себе. В частности, и по скорости.

Так в PostgreSQL от него пока не отказались, потому что 100% замены-то тупо нет (https://www.postgresql.org/docs/current/collation.html ): "B-tree cannot use deduplication with indexes that use a nondeterministic collation. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted."