Индекс GIN PostgreSQL медленнее, чем GIST для pg_trgm?

Question

Несмотря на то, что говорится во всей документации, я считаю, что индексы GIN значительно медленнее, чем индексы GIST для запросов, связанных с pg_trgm. Это таблица из 25 миллионов строк с относительно коротким текстовым полем (средняя длина 21 символ). Большинство строк текста являются адресами вида "123 Main st, City".

Индекс GIST занимает около 4 секунд с поиском, как

select suggestion from search_suggestions where suggestion % 'seattle';

Но GIN занимает 90 секунд и следующий результат при запуске с EXPLAIN ANALYZE:

Bitmap Heap Scan on search_suggestions  (cost=330.09..73514.15 rows=25043 width=22) (actual time=671.606..86318.553 rows=40482 loops=1)
  Recheck Cond: ((suggestion)::text % 'seattle'::text)
  Rows Removed by Index Recheck: 23214341
  Heap Blocks: exact=7625 lossy=223807
  ->  Bitmap Index Scan on tri_suggestions_idx  (cost=0.00..323.83 rows=25043 width=0) (actual time=669.841..669.841 rows=1358175 loops=1)
        Index Cond: ((suggestion)::text % 'seattle'::text)
Planning time: 1.420 ms
Execution time: 86327.246 ms

Обратите внимание, что по индексу выбирается более миллиона строк, хотя на самом деле совпадают только 40 тысяч строк. Есть идеи, почему это так плохо? Это на PostgreSQL 9.4.

Answer 1

Некоторые проблемы выделяются:

Сначала рассмотрите возможность обновления до текущей версии Postgres. На момент написания статьи это pg 9.6 или pg 10 (в настоящее время бета). Начиная с Pg 9.4 было несколько улучшений для индексов GIN, дополнительного модуля pg_trgm и больших данных в целом.

Далее вам нужно гораздо больше оперативной памяти, в частности более высокая work_mem установка. Я могу сказать из этой строки в EXPLAIN выход:

Heap Blocks: exact=7625 lossy=223807

"потеря" в деталях для сканирования битовой карты (с вашими конкретными числами) указывает на существенную нехватку work_mem, Postgres собирает только адреса блоков при сканировании индекса растрового изображения вместо указателей строк, потому что ожидается, что это будет быстрее с вашим низким work_mem настройка (не может содержать точные адреса в оперативной памяти). Таким образом, в следующем сканировании кучи растровых изображений необходимо отфильтровать еще много неквалифицированных строк. Этот связанный ответ имеет детали:

• Строка "Recheck Cond:" в планах запросов с проверкой растрового индекса

Но не устанавливайте work_mem слишком высоко без учета всей ситуации:

• Оптимизируйте простой запрос, используя дату и текст ORDER BY

Могут возникнуть другие проблемы, такие как увеличение индекса или таблицы или более узкие места конфигурации. Но если вы исправите только эти два элемента, запрос уже должен быть намного быстрее.

Кроме того, вам действительно нужно получить все 40k строк в примере? Вы, вероятно, хотите добавить небольшой LIMIT к запросу и сделайте его поиском "ближайшего соседа" - в этом случае индекс GiST является лучшим выбором в конце концов, потому что он должен быть быстрее с индексом GiST. Пример:

• Лучший индекс для функции подобия