Comments 12
Есть ещё Manticore и Xapian. И посмотрите на системные требования ElasticSearch 2.4.6, когда Шай ещё не отправился за деньгами.
Тут конечно как я понял автор специально шилит поиск постргри, т.к. если добавить в эти графики результаты от ElasticSearch , там в сотни, а то в тысячи раз быстрей.
Кстати что за программа которая рисует последний план ?
Ну использование OR явно будет медленным. Надо либо делать отдельную колонку с конкатинацией данных из других колонок и по нему уже делать индекс, либо делать индекс по выражению. Тогда БД не придётся делать шесть параллельных поисков и объединять результаты, а использовать всего один индекс.
Как показали тесты даже для поиска по одной колонке результаты так себе. Как ни странно, поиск по 6-ти полям выдавал результаты очень близкие по времени к поиску по 1-му полю.
Думаю если посидеть с конкатенацией данных и оптимизацией индекса, то получится выиграть по времени около 10%, но общую картину это поменяет не сильно.
Как можно заметить основные затраты времени идут на сортировку и подготовку данных.
Как вы пришли к такому выводу? Я не специалист по планам pgsql, но стоимость узла включает стоимость всех детей, соответственно сортировка там имеет относительную стоимость около ноля. Т.е. основные затраты ушли на подготовку данных, а именно bitmap heap scan и ниже, что в общем-то и логично.
Очень странно что только 3GB active из 25 даже после прогрева. По идее весь индекс должен выгружаться в память и продолжать работать также быстро как и до 10млн записей. Ощущение что просто не хватает памяти. Возможно нужно какие-то флаги/параметры бд подкрутить? В любом случае для простого поиска лучше использовать бд, а не отдельный Elastic, тк меньше узлов падения для отказоустойчивости.
Плюс когда я использовал gin индекс там надо поисковую строку разбивать на слова, иначе скорость не отличается от обычного like.
На 50 млн записей индекс занимал 18-20 Гб в сумме. По 3 Гб на каждую колонку.
На счет 3 Гб на скрине, не уверен что данный скрин был сделан в момент максимальной нагрузки и после выполнения всех запросов.
На счет поисковых слов - писал, что в итоге представленный вариант наверняка не использовал все возможности gin индекса (tsvector и tsquery) из-за особенностей поиска и формата данных, но все равно позволил выполнять like с достаточно высокой скоростью.
На счет Elastic(или другого поискового движка) и отказоустойчивости вы безусловно правы, но эту проблему будем решать чуть позже.
Не приведена полная схема базы, непонятно какой размер текста средний. Для сортировки логично было бы добавить индекс по Name, иначе она будет отъедать все время, или хотя бы сравнить время с сортировкой по id. По опыту с SQL Server такой поиск может работать очень быстро.
Полной схемы действительно не привел. Общее количество колонок было несколько больше (был Guid идентификатор, Guid ссылка на родительский объект, который в данном случае был один для всех записей, 6 полей varchar(60) которые заполнены 40-45 символами, 6 полей varchar(60) которые были заполнены null, 6 Guid? которые заполнены null). Так как все данные не указанные в статье были заполнены однотипными значениями, то данная информация была исключена.
Индекс по Name был только GIN и дополнительных индексов не строилось. Скорее всего другой тип индекса мог бы ускорить сортировку (Спасибо учтем). C Id не сравнивал, так как в конечном варианте работы сортировка бы происходила именно по Name.
По поводу размера строки - теперь можно посчитать (6 полей по 45 символов, 2 uuid, 6 пустых vachar(60) и 6 пустых nullable uuid). 6*46 + 2*16 + 6*1 + 6*16 = 436.
Примерно 440 байт. Так как в выборке используются ограниченный набор колонок для условий и для результата эта информация также не включалась в статью.
Оценка производительности полнотекстового поиска в PostgreSQL на примерах приближенных к реальным