Комментарии 34
— Вжик! — сказала пила, и бревно пополам.
— Ух ты! — сказали мужики и положили бревно потолще.
— Вжжжик! — сказала пила и… бревно пополам.
— Ого! — сказали мужики и положили рельсу.
— Вжж-крях! — сказала пила и поломалась.
— Ага!!! — сказали мужики, — То-то же…
"ERROR: syntax error at or near "abc" at character 123\nSELECT ...". Понятно, что дальнейший текст запроса можно отрезать, но если вместо «abc» в теле ошибки оказывается что-то очень длинное — от него так просто не избавиться.Чуть позже напишу отдельный пост, как вообще можно быстро матчить/группировать ошибки при наличии исходного набора форматов (вроде
'syntax error at or near "%s"').Зато «начало» ошибки (даже если оно усеченное) можно подставить под индекс с varchar_pattern_ops, что дает индексный LIKE по префиксу.
varchar_pattern_ops,
Какая интересная штука, как то я ее упустил, ни разу не пользовался. Хотя с другой стороны я не ищу обычно лайком за исключением автокомплитера, но там триграммы полезней.
И тут сначала надеешься, что человек набирает все-таки от начала строки — и тогда
btree(varchar_pattern_ops) + LIKE 'abc%', а если не нашлось достаточное количество — тогда уже gin(to_tsvector(...)) + to_tsquery('abc:*').Триграммы хорошо помогают, если поиск нужен просто по подстроке
create index consultants_active_trgm_idx
on directories.consultants
using GIST(name gist_trgm_ops)
where is_deleted=false;select *,
tableoid::regclass
from directories.counterparties
where name ~* ('(^|\s)' || ?) and
is_deleted = false
order by name
limit 10;Index Scan using consultants_active_trgm_idx on consultants (cost=0.41..71.88 rows=61 width=58) (actual time=1.119..58.472 rows=40 loops=1)
58.472мс — это все-таки не очень быстро для поиска 10 целевых записей. Интересно посмотреть на buffers и вариант с LIKE на тех же данных.
На самом деле это запрос вовсе не к одной таблице (6 млн строк суммарно) и машина это моя собственная, не сервак, до сервака сейчас не дотянусь.
Кстати для ФИО можно и like? || '%' использовать по идее. Как минимум в русском, в остальных языках — не уверен. А вот по юрлицам итд итп — нет.
like% имеет почти тот же план только выполняется 37 мс
This page is no longer available.
А вот по юрлицам итд итп — нет.Тоже можно, если сначала их определенным алгоритмом преобразовать.
[link]Ну вот ваш вариант.
create index consultants_active_btree_idx on directories.consultants using btree(name varchar_pattern_ops) where is_deleted = false;
Причем обратите внимание, ищу лайком, по паттерну будет еще хуже.
Parallel Bitmap Heap Scan on consultants (cost=8844.65..64082.74 rows=25 width=58) (actual time=162.509..251.519 rows=3 loops=3)
Recheck Cond: (NOT is_deleted)
Filter: ((name)::text ~~* 'Евлам%'::text)
Rows Removed by Filter: 207262
Heap Blocks: exact=17893
Buffers: shared hit=54451UPD а собственно вариант без раскастовки только like вместо ilike, а значит lower/upper до кучи
UPD2 Да в том что-то есть, хотя применимо только для like? || % и требует танца с upper/lover, но работает быстрее в тысячу раз. Хотя вот именно для того триграммы и придумали, чтобы при поиски Площади святого Петра мы не испытывали проблем.
Limit (cost=3382.76..3382.78 rows=10 width=70) (actual time=0.036..0.037 rows=3 loops=1)
Buffers: shared hit=6
-> Sort (cost=3382.76..3390.43 rows=3069 width=70) (actual time=0.036..0.036 rows=3 loops=1)
Sort Key: name
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=6
-> Bitmap Heap Scan on consultants (cost=61.58..3316.44 rows=3069 width=70) (actual time=0.024..0.029 rows=3 loops=1)
Recheck Cond: (NOT is_deleted)
Filter: (lower((name)::text) ~~ 'расти%'::text)
Heap Blocks: exact=3
Buffers: shared hit=6
-> Bitmap Index Scan on consultants_active_btree_idx (cost=0.00..60.81 rows=3069 width=0) (actual time=0.013..0.013 rows=3 loops=1)
Index Cond: ((lower((name)::text) ~>=~ 'расти'::text) AND (lower((name)::text) ~<~ 'растй'::text))
Buffers: shared hit=3
Planning Time: 0.085 ms
Execution Time: 0.055 ms
(
... WHERE ... LIKE 'abc%'
LIMIT 10
)
UNION ALL
(
... WHERE ... @@ to_tsquery('abc:*') -- а можно и триграммы
LIMIT 10
)
LIMIT 10ЗЫ в любом случае благодарю за примеры, было интересно в кои то веки. Вот только вокруг сплошные терабайтные базы чуть ли не в памяти, uuid через char, функции в условии и прочие каскадные прелести…
Но индексов будет два, да. Мы у себя моделировали — в большинстве запросов пользователю хватает результатов в LIKE-блоке, а выигрыш в тысячу раз стоит того, чтобы иметь второй индекс.
хотя нужно держать в голове, что сортировка все же неправильная.
(select *,
tableoid::regclass
from directories.consultants
where lower(name) like lower(?) || '%' and
is_deleted = false
order by lower(name) using ~<~
limit 10)
union all
(select *,
tableoid::regclass
from directories.consultants
where name ~* ('(\W)' || ?) and
is_deleted = false
order by name
limit 10)
limit 10;Explain
habr.com/post/491162
Вариант с нормализованной базой юрлиц интересен, но опять же, если это делалось на этапе создания приложения, когда в руках таблица с миллонами записей по нескольки странам, тут только использовать что есть. Хотя я бы потестил нормализованную базу, не видел еще доведенного до идеала решения, поскольку всевозможные сокращения ака 'НПФ', 'ДЮСШ' и прочие, тысячи их, не оставляют ни малейшего шанса.
С географией тоже удобней искать по границе слова, это я на себе почувствовал. Вот с фамилиями согласен, тут префиксный поиск в полный рост, хотя черт его знает как там в других языках.
И зачем вообще в БД хранить логи? Или это типа путь 1С, хранить файлы в БД. )
habr.com/post/487380
www.youtube.com/watch?v=OVeTdSWeMYo
А вам, видимо, надо смотреть в сторону v11 и
ALTER TABLE ... SET (toast_tuple_target = ...);
ALTER TABLE ... ALTER COLUMN ... SET STORAGE MAIN;Напишу попозже про наш опыт тюнинга этих параметров.
Либо сразу обрабатывать, нужные поля в таблицу, остальное в jsonb, если вдруг понадобилось то индексируем нужные поля частичным индексом по диапазону времени.
А как насчёт FTS + GIN?
Зато без изврата.
Всё имеет свою цену. Если у вас вот прям такая задача стоит, то логично использовать соответствующие инструменты. Или не использовать Postgres совсем, а например Elasticsearch.
Все зависит от задачи. Если можно найти решение, которое дает неотличимый для пользователя ответ, но "дешевле" — почему бы им не воспользоваться.
Потому что вы свой колхоз опубликовали на Хабре, а кто-то его заплюсовал. Вы надеетесь этим привлечь клиентов? Скорее вы добьётесь обратного эффекта. Пожалуйста, добавьте вступление в начале поста:
Мы в компании "Тензор" любим изобретать велосипеды, но иногда они выходят с квадратными колёсами. Вместо того чтобы использовать решения промышленного уровня, признанные во всём мире, мы лепим колхоз из палок и синей изоленты. В данной задаче нам требовалось хранить логи и выполнять поиск по ним. Советуем вам для начала обратить внимание на Elastic Stack (бывший ELK), который развертывается в считанные минуты и агрегирует логи с разных частей приложения. Также недавно дозрел до продакшна Grafana Loki. Есть и более экзотические решения, хорошо зарекомендовавшие себя на рынке. Решать вам, но помните, что скупой платит дважды!
Вы почему-то считаете, что у любой задачи есть только единственное "правильное" решение, но это не так.
Мы используем для разных задач и Elastic, и ClickHouse, и Redis — но всегда надо учитывать не только их возможности, но и ограничения. А в корпоративных велосипедах нет ничего особо ужасного — так появились и Bootstrap, и AWS, и Google Cloud.
Сравнение некорректное. Все перечисленные вами продукты отнюдь не являются велосипедами. Они были тщательнейшим образом спроектированы, и в их разработку были вложены сотни миллионов долларов. И теперь они задают отраслевые стандарты.
Да, я считаю что нужно использовать по возможности только "правильные" решения, являющиеся продуктом коллективного разума лучших инженеров планеты, признанные корп. стандартом, имеющие поддержку сообщества и интеграцию с другими "правильными" решениями из коробки.
И я предложил не одно такое решение а три, на вскидку.
То есть весь упрек — в недостаточно тщательном проектировании наших решений (для задач, всех условий которых вы не знаете) или в отсутствии ссылок на решения от "коллективного разума" (который не факт, что решал именно такие задачи)?
Я бы предложил не оффтопить здесь, а вынести дальнейшее общение в личку, если обсудить наши задачи и их решения действительно интересно.
Микропост: режем UNICODE-строки для PostgreSQL