В начале месяца я прочитал доклад про индексы в базах данных для Saint P Ruby Community и буквально через несколько дней жизнь не замедлила подкинуть мне показательный пример работы индексов, планировщика баз данных и важности обновления СУБД.

Итак, дано: большая таблица projects с кучей (ненужных) индексов, в том числе обычный BTree-индекс по числовой колонке forks_count. У неё есть связь по has_one с таблицей project_dependencies с функциональным GIN-индексом по полю packages в колонке data, в котором поле находится JSON-объект с названиями NPM-пакетов в ключах и их версиями в значениях (куда ж сейчас без джаваскрипта?):

CREATE TABLE projects (
  id bigserial PRIMARY KEY,
  title varchar,
  forks_count int
);
CREATE INDEX index_projects_on_forks_count ON projects (forks_count);

CREATE TABLE project_dependencies (
  id bigserial PRIMARY KEY,
  project_id bigint NOT NULL,
  data jsonb DEFAULT '{}'::jsonb,
  FOREIGN KEY (project_id) REFERENCES projects (id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE UNIQUE INDEX index_project_dependencies_on_project_id ON project_dependencies (project_id);
CREATE INDEX project_dependencies_data_packages ON project_dependencies USING gin (((data -> 'packages'::text)));
  
INSERT INTO projects (id, title) VALUES (1, 'Test');
INSERT INTO project_dependencies (project_id, data) VALUES (1, $json$
{"packages": {"react": ["16.8.0"], "react-dom": ["16.8.0"], "nanoid": ["1.0.2"], "object-assign": ["4.1.1"]}}
$json$);

Задача: вытащить примеры проектов, имеющих в зависимостях определённый пакет, показать пользователю наиболее популярные. Показателем популярности и ценности как раз будет количество форков — как правило это какие-то уже раскрученные публичные стартер-проекты.

Как думаете, будет просто? Поехали!

Изначально я опубликовал эту статью как твиттер-тред, но по просьбам трудящихся, не привыкших к формату, публикую чуть расширенную версию как статью.

Пишем запрос, который будет использовать наш функциональный индекс:

SELECT "projects".*
FROM "projects"
WHERE
  "projects"."id" IN (
  	SELECT "project_dependencies"."project_id"
  	FROM "project_dependencies"
  	WHERE (data -> 'packages' ? $1)
  )
ORDER BY "projects"."forks_count" DESC
LIMIT 20 OFFSET 0;

Ожидание: используется функциональный GIN-индекс и всё летает.

Реальность: да, но есть один нюанс…

На сцену выходит селективность (кратенько об этом важном термине можно прочитать вот в этой старой статье на Хабре: https://habr.com/en/post/36705/)

Пробуем запрос на каком-нибудь не совсем уж малоизвестной, но всё же не супер-популярной зависимости. Возьмём nanoid от моего коллеги @Iskin и посмотрим план запроса с ним.

Да, всё работает как ожидалось — сходили в функциональный индекс, отыскали все проекты, использующие nanoid, потом уже достали проекты. Смотрите план запроса: https://explain.tensor.ru/archive/explain/d62a7078323f3f387efa4c5cfbb55e35:0:2021-12-20

Пока всё хорошо: 15-25 миллисекунд на миллионе проектов и слабенькой стейджинг-базе.

Возьмём теперь ультра-популярную зависимость. Что-нибудь, что используется натурально в каждом третьем или четвёртом проекте в базе. react там, например, или angular.

И вот тут становится больно интересно, потому что PostgreSQL на стейджинге выполняет этот запрос две минуты!
https://explain.tensor.ru/archive/explain/c960e48f945b0b9049181baeaac25fd9:0:2021-12-20

А вот локальный PostgreSQL внезапно выбрал совсем другой план и не прогадал — опять всё работает шустро.

Что он делает? Идёт по индексу по самым форкаемым проектам до тех пор, пока не найдёт 20 проектов с react в зависимостях:
https://explain.tensor.ru/archive/explain/564d46aad2add0cd347bbca42b2ce0a6:0:2021-12-20#explain

Почему Postgres решил, что так будет быстрее? (да, на сцену выходит статистика)

Для каждой колонки в таблице, а так же для каждого функционального индекса СУБД собирает статистику по содержащимся в нём значениям — как много разных, как они отсортированы, и, что нас интересует здесь, какие самые популярные значения есть и как они распределены.

То есть СУБД увидела, что react — очень популярный ключ в этом индексированном JSONB-объекте. А значит, придётся вытащить и перелопатить бо́льшую часть таблицы, чего делать не хочется. Но при этом это настолько популярное значение, что можно наудачу вытащить несколько десятков проектов и большинство из них будут реактовыми!

Но почему же тогда на стейджинге всё работает так медленно?

А на стейджинге просто старая версия PostgreSQL — 9.6 (кстати, уже не поддерживается и с неё надо срочно обновляться!)

Может, можно придумать какой-то захак? Пробую старый добрый костыль с указанием явного условия в подзапросе — PostgreSQL 9.6 переключается на использование индекса по forks_count (кстати, PostgreSQL 13 этот хак уже игнорирует).

SELECT "projects".*
FROM "projects"
WHERE
  "projects"."id" IN (
  	SELECT "project_dependencies"."project_id"
  	FROM "project_dependencies"
  	WHERE (data -> 'packages' ? $1)
      AND "project_dependencies"."project_id" = "projects"."id" -- хак!
  )
ORDER BY "projects"."forks_count" DESC
LIMIT 20 OFFSET 0;

Запросы по популярным пакетам теперь работают шустро, ура!

Но теперь начали тормозить запросы по непопулярным пакетам — PostgreSQL так же бежит назад по forks_count, но теперь надо оббежать половину таблицы projects (а то и всю) и для каждой строки в ней сходить в соседнюю таблицу. Это очень медленно и дорого:
https://explain.tensor.ru/archive/explain/42d2b387e59cd1b053f48adfea0ec8ae:0:2021-12-20

В чём же разница между версиями? Оказывается, в PostgreSQL 13 улучшили оценку селективности для JSONB-полей и GIN-индексов по ним, используя имеющуюся статистику: https://www.postgresql.org/message-id/flat/12237.1582833074%40sss.pgh.pa.us (спасибо Алексею Ермакову из facebook-группы PostgreSQL в России, который нашёл эту ссылку)

И что же делать? Обновляться!

Обновляйте ваши базы! СУБД действительно становятся лучше от версии к версии.

Вот лишь небольшой список улучшений в последних версиях PostgreSQL, связанных с индексами и производительностью запросов:

• PostgreSQL 14: уменьшение распухания BTree, многодиапазонные BRIN-индексы

• PostgreSQL 13: дедупликация значений в BTree — это круто для foreign key для has many отношений 1-N, где N велико, т.к. индексы становятся меньше, а размер имеет значение — чем меньше индекс, тем он привлекательней для планировщика (ну и просто быстрее)

• PostgreSQL 12: покрывающие GiST-индексы, REINDEX CONCURRENTLY

• PostgreSQL 11: покрывающие индексы (кроме GiST), партиционированные индексы

• PostgreSQL 10: параллельное сканирование BTree, полноценные Hash-индексы

• PostgreSQL 9.6: ☠️ (алло, вы уже должны были обновиться с неё!)

Используйте локально те же версии софта, что и в проде, чтобы избежать сюрпризов. У нас есть целый мануал про это: Ruby on Whales: Dockerizing Ruby and Rails development (и даже не один: Reusable development containers with Docker Compose and Dip)

Кто-нибудь может сказать «а, вот если бы в PostgreSQL были бы хинты, то уж тогда бы ух!»

Но хинты бы не помогли, потому что для выполнения этого запроса нет одного оптимального плана выполнения! Высокоселективные запросы выгоднее исполнять по другому, чем низкоселективные.

Если вам порой кажется, что всё это планирование SQL-запросов кажется слишком магичным, то вам не кажется. Даже в целом понимая, как оно работает, это порой всё ещё остаётся чёрной магией и экзорцизмом. ????

Посмотрите мой доклад про индексирование БД, не поленитесь — там есть много ссылок на курсы и книги, чтобы вникнуть в тему глубже!

Слайды
Видео

Спасибо за внимание!