PostgreSQL Antipatterns: сказ об итеративной доработке поиска по названию, или «Оптимизация туда и обратно»

[Jabberwocky]

Картинка неполная. Еще было «как описано в документации» с голым пеньком :-)

[Kwisatz]

Зря не упомянули, что результирующая сортировка не совсем правильная. В большей части случаев это не заметно, но бывают казусы.

Кроме того я вам прошлый раз говорил, что tsvector у вас дает хорошие результаты потому что на тестах вы ищете целое слово, дела будут обстоять хуже когда будете искать частичное вхождение.

ЗЫ А вообще по моему опыу работы с чужими базами — многим плевать. Ядер вагон, памяти немеряно, диски ssd, а давайте искать лайком без индексов!

[Kilor]

Зря не упомянули, что результирующая сортировка не совсем правильная. В большей части случаев это не заметно, но бывают казусы.

А можно пример? Сложные кодировки «с умляутами» или что-то более распространенное?

tsvector у вас дает хорошие результаты потому что на тестах вы ищете целое слово, дела будут обстоять хуже когда будете искать частичное вхождение.

Так как раз частичное же и ищется, по префиксу:

to_tsquery('simple', 'роза:*')

[Kwisatz]

А можно пример?

Сейчас резко не подберу, но на вскидку
9 обычных результатов из первой части, выглядят так:
Железячко Арсен
Железячко Боб
…
И 1 результат из второй выборки
Анищенко Железко

Хотя, при сортировке, он должен быть по идее первым. Но если задать сортировку на самом верху, то второй запрос будет выполняться всегда. В большинстве случаев — ничего страшного, но помнить — нужно и не для всех это очевидно.

Так как раз частичное же и ищется, по префиксу:

Да, виноват, по диагонали запросы смотрел

[Kilor]

Хотя, при сортировке, он должен быть по идее первым.

Не совсем понял, почему. Потому что «находимая» часть алфавитно выше?

[Kwisatz]

Потому что вся строка алфавитно выше. Общей результирующей сортировки не выполняется, для ее выполнения нужен еще один запрос, уровнем выше.

select * from 
  (
   (select --prefix part here order by name asc limit 10)
   union 
   (select --ftw/trgm part here order by name asc limit 10)
   limit 10
  ) 
order by name asc

[Kilor]

А, в этом смысле. Для этого специально в пункте 0 указал:

… и покажете более релевантным то, что начинается на введенное

То есть не надо пересортировывать «вообще все» по всей строке — там же каша получится просто в результатах.

[Kwisatz]

и покажете более релевантным то, что начинается на введенное

Хм, действительно, не заметил

там же каша получится просто в результатах.

Никакой каши, сами попробуйте.
Как раз напротив, если вы не сортируете общие результаты, вы создаете ситуацию, когда результаты отсортированы «хрен пойми как» с точки зрения пользователя. Если вы сознательно на это идете — нет вопросов, но упомянуть — стоит. Просто я довольно часто встречал недоумения по поводу порядка сортировки в случае union limit

[Kilor]

Если расширить лимит, то у меня выглядит выборка примерно вот так:

"Роза" -- разные ЮЛ, по ИНН, городу и т.п.
"Роза"
"Роза"
"Роза"
"Роза"
"Роза"
"Роза"
"Абайдулина Роза Камилевна, ИП"
"Абгалимова Роза Абдельгалиммовна, ИП"
"Абдразякова Роза Нурихановна, ИП"
"Абдрахманова Роза Бахытжановна, ИП"
"Абдрахманова Роза Фаритовна, ИП"
"Абдулина Роза Абдихаликовна, ИП"
"Абдулкеримова Роза Саламбековна, ИП"
"Абдулнасырова Роза Камиловна, ИП"
"Абдулова Розалия Салихзяновна, ИП"

А если пересортировать общий результат, то все эти ИП поедут наверх. И становится банально непонятно, почему на предыдущей строке «Абдулова Розалия Салихзяновна, ИП», а на следующей — уже «Роза».

[Kwisatz]

Если добавить ОКП то имхо все будет вполне логично. У вас контрагенты, у вас довольно специфический случай, тут надо действительно подумать, но как правила навигация среди одинаковых элементов по алфавиту много легче.
попробуйте поискать по слову «Розовый»

[Kilor]

Если записи равнозначны — все нормально будет:

"РОЗОВЫЙ АГАТ, питомник"
"Розовый Бегемот, ООО"
"Розовый дельфин"
"Розовый Дельфин, ООО"
"Розовый дождь"
"РОЗОВЫЙ ДОЖДЬ, салон красоты, ЗАО"
"Розовый Дом, ООО"
"Розовый Дом, ТСН"
"Розовый Жемчуг, ООО"
"Розовый коралл"
"Розовый Коралл, ООО"
"Розовый крокодил, студия фотообоев"
"Розовый кролик"
"Розовый Лотос, ООО"
"Розовый Лотос, ООО"
"Розовый Лотос, ООО"
"Розовый Лотос, ООО"
"РОЗОВЫЙ ЛОТОС,производственная фирма, ЗАО"
"Розовый Мир, ООО"
"Розовый Мир, ООО"

А вот если их надо показывать в какой-нить иерархии…

… тогда это уже не подстрочный поиск.

[Kwisatz]

В первом варианте думалось будет еще нечто вроде Пятачек Розовый ООО (реально название кстати).
Скринов сделать забыл но тоже попробовал: география и поиск по имени отчеству хорошо идут если список всегда сортирован по алфавиту, а вот если искать с фамилии то да, ваш вариант с релевантностью удобен. Контрагентов не нашел достаточной выборки.

ЗЫ кстати по триграмм и тсвектору зачем вам lower? Триграммы отлично поддерживают ilike (~*) а тс вектору просто плевать

[Kilor]

Просто на едином примере с lower показывал, чтобы частности не отвлекали. А simple regconfig вроде регистрозависим? Сейчас нет базы в доступе проверить.

[Kwisatz]

А simple regconfig вроде регистрозависим

нет

Просто на едином примере с lower показывал, чтобы частности не отвлекали

Уже родилась толпа джунов сующих lower в tsvector и триграммы)

ЗЫ вспомнил откуда у меня привычка юзать триграммы а не tsvector. Осталась со времен задачи по поиску нечетких дубликатов: n-граммы, левенштейн, metaphone, soundex вот эти все прелести. но это, вероятно, следующий уровень поиска.

ЗЫЗЫ раз уж пошла такая пьянка, когда поиск только на русском, можно еще проверять строку на наличие английских букв и конвертировать раскладку, или увеличить запрос вдвое, это удобно.

[Kilor]

Уже родилась толпа джунов сующих lower в tsvector и триграммы)

«Дай человеку в руки молоток, и он решит, что весь мир состоит из гвоздей»

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

Ага, у нас такое сделано, но только на уровне интерфейса, а не запроса. То есть поискали сначала в исходной раскладке — если пусто, то и в другой.