maxcom

Попробовал использовать довольно интересную фичу нового PostgreSQL - btee_gin. Это расширение, позволяющее засоввывать в поисковый индекс дополнительные поля, например дату, число или boolean (полный список поддерживаемых типов по ссылке).

Без этого расширения поиск по тексту с учетом дополнительных критериев всегда включает в себя полную выборку по текстовому запросу, которая затем так или иначе фильтруется по дополнительным критериям. С btree_gin можно сразу получить из индекса то что нужно, что (потенциально) быстрее.

Вторая полезная фича поиска - gin_fuzzy_search_limit, она появилась еще в 8.2 (или в 8.3). Этот режим позволяет ограничить выборку по индексу для запросов, которые выдают слишком много ответов, поскольку такие запросы исполняются долго и сильно грузят БД (особенно если потом хочется их всех отранжировать и отсортировать). Фактически это режим "выдачи глупых ответов на глупые запросы". Это значение не должно быть слишком маленьким, нужен еще некоторый запас на фильтрацию по нетекстовым критериям. gin_fuzzy_search_limit задает примерное максимальное число ответов, которое мы хотим получить.

Эксперимент по использованию gin_fuzzy_search_limit совместно с btee_gin по тексту и дате оказался не удачным. Для примера рассмотрим индекс по тексту и дате, и искать будем сообщения за последний год. Тестировалось два запроса - первый по слову "linux". При поиске без даты в индексе за год мы получали ~3000 ответов (gin_fuzzy_search_limit = 10000, остальное отфильтроввывавалось по дате). При поиске по индексу с датой - 130 ответов. Хуже ситуация с редковстречающимся словом, например, 'gravatar' (оно еще хорошо тем, что встречается только в этом году). Это слово встречается всего 33 раза, так что при отсутствии даты в индексе из поиска мы получаем все 33 ответа. При наличии даты в индексе - слово не находится вообще, или находится в 1-2 документах (как повезет).

Насколько я понимаю, в данном случае gin_fuzzy_search_limit ограничил отдельно выборку по дате (фактически выбрар произвольные 10000 сообщений этого года) и отдельно выборку по слову, а затем сделал им 'join', выкинув значительную часть полезных результатов.

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

Еще кстати интересный случай про PostgreSQL, на этот раз на примере запроса по той же многогигабайтной таблице

( Read more... )

В продолжение к прошлому посту на эту тему.

Эксперимент показал, что лучшее для нас сочетание - функциональный gin-индекс, при котором tsvector не сохраняется в базе:

create index ftsidx_gin on mime_part using gin (to_tsvector('russian'::regconfig, text))

Это хорошо, потому как сами по себе вектора сравнимы (а то и больше) чем сам текст, на их запись уходит уйма времени (и без того текстовая таблица с GIN-индексом 40Gb). Попытки использовать gist-индекс к разумному итогу не привели - мы так и не смогли дождаться результата поиска :-)

Одна проблема - постгресс иногда не хочет использовать GIN-индекс и пытается искать прямо по базе. Решение состоит из двух половин: 1) засунуть поиск по тексту в подзапрос, отдельно от других условий и 2) alter function to_tsvector(regconfig, text) cost 1000 (чтобы постгресс не думал, что считать tsvector дешево, кстати странно что этого нет по-умолчанию)

Ковырял тут индексирование постгрессом текста на большом потоке поступающих данных. GIN-индекс строится безумно долго, при этом его постоение упирается исключительно в запись на диск. При этом скорость записи выходит не большой, видимо из-за большого количества random seek, однако utilization 100% (кстати это наводит на мысль что выделенный под индекс SSD-диск может сильно помочь). Тюнинг постгресса не очень помогает. GiST индекс стоится радикально быстрее (и при этом скорость записи на диск выше). Расказы на PostgreSQL.org о разнице в 10 раз кажутся оптимистичными, на глаз разница в скорости раз в 50. Осталось только протестировать скорость поиска (напишу про это если кому-нибудь интересно :-)

Видимо правильная схема состоит в наличии двух индексов - GiST с новыми добавлениями, GIN с архивом и отдельный процесс который бы перекладывал из одного в другое. Ну, это, конечно, если загрузка вообще позволяет построить GIN-индекс быстрее чем данные уйдут из базы :-) Хотя надо реально попробовать, может нас устроит время поиска по GiST-индексу

PS: постиг индексы с условиями, они позволяют заметно ускорить запросы при плохом распределении данных в столбцах

Тюнил полнотекстовый поиск по lor (пока еще не затюнил до конца). PostgreSQL умная база, ее разуму можно доверять. Однако иногда вылезает жуткая тупизна оптимизатора, которую приходится обходить бесчеловечными извратами. Надо будет попробовать 8.4

Много интересных докладов в avi: http://www.rit2007.ru/org.html, где бы еще найти время их посмотреть. Жаль звук местами плохо записан.

PostgreSQL переводили какие-то гоблины, как вам вот это:

ERROR: повторный ключ нарушает констрейнт UNIQUE "del_info_pkey"

Сделал поисковик на базе tsearch2 с GIN-индексами для linux.org.ru. Ищет довольно шустро, поиск занимает до 20 секунд на базе в 2 млн сообщений. Редко встречающиеся слова ищет вообще мгновенно, видимо много времени уходит на сортировку по релевантности при поиске с большим количеством попаданий.

Но первичное индексирование в целом заняло часов 5.