Какой индекс по умолчанию используется в PostgreSQL и его плюсы и минусы

Что проверяют

Знаете ли вы, что именно строит CREATE INDEX без уточнений, и можете ли назвать границы применимости B-tree без общих слов про «ускоряет поиск». Следом спрашивают, какие индексы PostgreSQL создаёт сам, и тут многие ошибаются про внешние ключи.

Как отвечать

По умолчанию это B-tree: CREATE INDEX ON orders (customer_id) равносилен USING btree. Его же PostgreSQL создаёт автоматически для PRIMARY KEY и UNIQUE. Для внешних ключей индекс не создаётся, его добавляют сами.

Плюсы:

• операторы: =, <, >, BETWEEN, IN, IS NULL, а также LIKE 'abc%' при локали C или с классом операторов text_pattern_ops;
• упорядоченность: индекс отдаёт строки в нужном порядке, и ORDER BY ... LIMIT обходится без сортировки всей выборки;
• уникальность: единственный тип индекса в PostgreSQL, который бывает уникальным;
• Index Only Scan: если все нужные колонки есть в индексе, в том числе через INCLUDE, таблицу можно не читать;
• дедупликация: с PostgreSQL 13 повторяющиеся значения хранятся компактно.

Минусы:

• цена записи: каждый INSERT, UPDATE и DELETE обновляет все индексы таблицы;
• разбухание: после массовых изменений индекс растёт, помогают VACUUM и REINDEX CONCURRENTLY (с PostgreSQL 12);
• не для всех задач: LIKE '%abc%', полнотекстовый поиск, jsonb и массивы (GIN), геометрия и диапазоны (GiST);
• лимит записи: около 2,7 КБ на значение, длинные тексты напрямую не индексируются.

В Java-проекте это всплывает так: Hibernate не создаёт в PostgreSQL индекс под @ManyToOne, а @Index в @Table работает только при генерации схемы. На проде индексы живут в миграциях Flyway или Liquibase, а CREATE INDEX CONCURRENTLY нельзя выполнить в транзакции, такую миграцию помечают нетранзакционной.

Где спотыкаются

Говорят, что B-tree плох на колонках с высокой кардинальностью. Всё наоборот: бесполезен он на низкой, например на булевом флаге.

Считают, что индекс сортирует таблицу. Упорядочен только сам индекс, строки в таблице лежат как попало.

Уверены, что внешний ключ индексируется автоматически, и получают медленные JOIN и долгие каскадные удаления.