"Тайны Pawsgresville: Дело об исчезнувших данных". Идексы Postgres.

В оживлённом городе Pawsgresville, где данные текли, как реки, и порой так же непредсказуемо исчезали, работал частный сыщик по имени Биллометр Би-Tри, или просто B-Tree. Его офис, утопающий в бумагах и запахе дешёвого кофе, всегда был полон клиентов, жаждущих разгадать загадки своих запросов: найти потерянные данные, упорядочить списки или вычислить хитроумные диапазоны. Но однажды на пороге его офиса появилась проблема, способная перевернуть весь порядок в городе.

В другой части города произошла кража. Билл и GIN направились к старому знакомому — мистеру GIST. Этот рыжий кот, известный своим свободным характером, обитал в мастерской на окраине города, где возился с геометрическими запросами и сложными структурами данных. Карта геометрических данных оказалась повреждена.

«Я слышал, вам нужны индексы для хитрых диапазонов?» — спросил GIST, подкручивая свои усы.

«Именно так. Кто-то украл часть данных о полигонах, — сказал Билл. — Нам нужно найти все области, которые пересекаются с этим прямоугольником!Нам нужны индексы, которые могут обрабатывать запросы вроде ‘найти все записи внутри определённого квадрата или диапазона значений.’»

GiST кивнул и создал геометрический индекс:

CREATE INDEX idx_map ON locations USING gist(location);

SELECT * FROM locations WHERE location && 'BOX(1 1, 5 5)'::box;

— Ваш случай особый, — добавил GiST. — Мне хорошо даются сложные данные, но для простых диапазонов, таких как даты, я могу быть медленнее.

Глава 4. BRIN и огромные архивы

Глава 7: Секреты подземелий VACUUM

Когда детектив B-Tree думал, что дело близится к завершению, в офис постучался новый клиент. Это был пушистый белый кот по имени Vacuum, известный в Пушгресвиле как мастер уборки данных.

— «Детектив B-Tree, я заметил странности в подземных хранилищах данных. Кажется, там накопилось слишком много мусора, и я не могу эффективно выполнять свою работу!»

B-Tree приподнял шляпу, задумавшись:

— «Расскажите подробнее, Vacuum. Вы ведь тот самый, кто заботится о чистоте таблиц и удалении устаревших строк?»

Vacuum кивнул.

— «Верно. Но в последнее время в таблицах Пушгресвиля полно мёртвых строк, оставшихся после DELETE и UPDATE запросов. Их никто не убирает, и это мешает индексам работать быстро. А ещё... кажется, некоторым индексам нужна моя помощь, чтобы оставаться компактными.»

— «Компактность... это звучит серьёзно,» — пробормотал B-Tree.

Vacuum разложил карту:

Обычный VACUUM: он очищает мёртвые строки, но не возвращает место операционной системе.

VACUUM FULL: он полностью реорганизует таблицы, возвращая свободное пространство системе. Правда, это требует блокировки таблиц.

Автовакуум: работает в фоновом режиме, но иногда не справляется, если нагрузка слишком велика или настройки сервера не оптимальны.

— «Это как убирать мусор с улиц. Если делать это нерегулярно, движение по городу замедляется,» — объяснил Vacuum.

B-Tree вспомнил, как недавно планировщик запросов жаловался на Sequential Scan, который с каждым днём становился всё медленнее.

— «Значит, ты не только убираешь мусор, но и помогаешь индексам оставаться эффективными?»

Vacuum улыбнулся.

— «Точно! Без меня GIN и GiST начинают страдать, ведь их размеры увеличиваются, а поиск становится медленнее. А BRIN вообще может потерять свой смысл, если я не буду поддерживать порядок в данных.»

Полезные советы для расследований

• Индексы работают быстрее, если запросы учитывают порядок столбцов.
  Например, если индекс CREATE INDEX idx_users_age_city ON users (age, city), то запросы вроде WHERE age = ... AND city = ... будут работать эффективно. А вот WHERE city = ... AND age = ... могут быть медленнее.


• Сложные условия с OR редко эффективно используют индексы.


• Если много OR-условий, попробуйте переписать запросы или создать несколько индексов.



• Для LIKE лучше использовать pg_trgm.
  
  Подключите расширение:
  
  CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
  
  Это ускорит запросы вроде LIKE '%подстрока%'.




• Проверяйте запросы с помощью EXPLAIN ANALYZE. Оно покажет, что делает планировщик, и подскажет, как улучшить запрос.




• Не злоупотребляйте индексами. Они ускоряют чтение, но замедляют вставку и обновление данных.

Когда использовать какой индекс?

Каждый тип индекса в PostgreSQL — мастер в своём деле:

B-Tree — универсальный детектив. Поиск равенства и диапазонов, сортировка.

GIN — быстрый эксперт по множественным совпадениям. Полнотекстовый поиск, JSONB, массивы.

GiST — аналитик сложных структур. Геометрия, диапазоны, сложные типы данных.

BRIN — экономный, но эффективный на огромных данных. Очень большие таблицы, упорядоченные данные.

Так в Клубе Индексов каждый запрос находил своего детектива. А Pawsgresvill оставался самым быстрым и умным городом баз данных, потому что его жители всегда соблюдали порядок в индексе и использовали подходящие инструменты. 🕵️‍♂️