В оживлённом городе Pawsgresville, где данные текли, как реки, и порой так же непредсказуемо исчезали, работал частный сыщик по имени Биллометр Би-Tри, или просто B-Tree. Его офис, утопающий в бумагах и запахе дешёвого кофе, всегда был полон клиентов, жаждущих разгадать загадки своих запросов: найти потерянные данные, упорядочить списки или вычислить хитроумные диапазоны. Но однажды на пороге его офиса появилась проблема, способная перевернуть весь порядок в городе.
Глава 1. Таинственное исчезновение
В тот день в офис вошла нервная сиамская кошка с лэптопом в лапах. Её звали Лайка, и она владела небольшим архивом запросов. Её проблема была сложной: «Мой поиск перестал работать. Я ввожу ‘%tractor%’, а результаты находят всё, что угодно, кроме нужного!»
B-Tree прищурился и задумчиво посмотрел на экран. «Так-так. LIKE с ведущим процентом, говорите? Вы использовали индекс?»
«Конечно! У меня стоит обычный B-Tree индекс, но он будто игнорируется!»
«Вот в чём дело, мадам, — сказал детектив, — ваш запрос с ведущим ‘%’ вынуждает систему сканировать всю таблицу. Индексы B-Tree просто не предназначены для таких трюков. Но не волнуйтесь, у меня есть решение. Это работа для профессора GIN.»
Глава 2. GIN и загадка словаря
В соседнем районе, среди пыльных книжных полок, жил профессор GIN. Он знал всё о полнотекстовом поиске и индексах, которые могли справиться с задачами, перед которыми B-Tree пасовал.
«Профессор GIN, нам нужна ваша помощь!» — заявил B-Tree, ввалившись в библиотеку. Лайка, прижав уши, следовала за ним.
«Что у вас на этот раз?» — спросил GIN, глядя поверх очков.
«LIKE с ведущим процентом. И обычный B-Tree не справляется.»
Профессор склонился над лэптопом, перебирая данные. «Вам нужен GIN-индекс или хотя бы trigram extension. Смотрите, — он открыл словарь на слове ‘трактор’ и показал, как индекс можно использовать для разбиения слов на подстроки. — GIN отлично работает, если вы часто ищете фрагменты слов. Но помните: если данных слишком мало, накладные расходы на создание такого индекса могут быть не оправданы.»
CREATE INDEX idx_content ON documents USING gin(to_tsvector('english', content));
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('tractor');
Лайка выглядела впечатлённой. «Такой индекс точно решит мою проблему?»
«В большинстве случаев, да. Но есть нюанс: Если вам придётся часто добавлять или обновлять данные, мой индекс может замедлить операции. Это моя слабость. А для сложных пространственных задач есть ещё один эксперт.»
Глава 3. Заговор полигона
В другой части города произошла кража. Билл и 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 и огромные архивы
Тем временем в архиве городского регистратора застряли поисковики. База данных была огромной, и никто не мог найти документы за определённый месяц.
— У нас есть BRIN! — предложил архивариус. — Он отлично справляется с большими данными, которые хранятся в упорядоченном виде.
BRIN подошёл к задаче с минималистичным подходом:
CREATE INDEX idx_events ON events USING brin(event_date);
SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31';
— Я экономлю много места, — объяснил BRIN, — но если данные не упорядочены, моя эффективность падает.
Глава 5. Загадка логических связок
Тем временем детектив Биллометр Би-Три столкнулся с новой задачей. Клиент попросил найти данные с двумя условиями:
SELECT * FROM users WHERE age = 25 AND city = 'Saint-Pawsburg';
— Ну что ж, детективы, за дело! — сказал Билл.
B-Tree любит, чтобы всё было по порядку. Для этого в таблице был индекс:
CREATE INDEX idx_users_age_city ON users (age, city);
— Отлично! — сказал B-Tree. — Я знаю, как искать: сначала найду всех, у кого age = 25, а потом проверю city = 'Saint-Pawsburg'.
Но тут прилетел другой запрос:
SELECT * FROM users WHERE city = 'Saint-Pawsburg' AND age = 25;
Билл вздохнул:
— Порядок в индексе важен! У меня сначала age, а потом city. Если ты начнёшь с city, я не смогу сразу сузить круг поиска.
Клиент ушел, но вернулся с новым запросом
— Мне нужны записи, где возраст = 25 ИЛИ город = 'Saint-Pawsburg'.
Билл задумался. Он знал, что его индекс поддерживает запросы с AND, но с OR всё сложнее. Если у тебя есть индексы только на возраст и на город, то Postgres объединяет результаты:
SELECT * FROM users WHERE age = 25 OR city = 'Saint-Pawsburg';
Но это может быть медленно. OR-запросы редко работают хорошо с одним индексом. Придётся работать тяжело: я сначала найду всех с age = 25, а потом добавлю тех, у кого city = 'Saint-Pawsburg'.
— Лучший способ ускорить такие запросы — отдельные на каждую колонку индексы
CREATE INDEX idx_age ON users (age);
CREATE INDEX idx_city ON users (city);
или пересмотр условий, — посоветовал Билл.
На следующий день Билл отправился в Зал Планировщиков.
Глава 6. Планировщик и великий анализ
Это было место, где великий Query Planner обучал новичков искусству составления планов выполнения запросов. Он стоял у доски, показывая разницу между Index Scan и Sequential Scan, а детектив сидел в заднем ряду, внимательно слушая. Планировщик решал, использовать ли индекс вообще.
— Ваш запрос, — сказал он Биллу, — слишком неэффективен. Данные малы, поэтому последовательный обход таблицы будет быстрее.
Поняв это, Билл стал чаще советовать клиентам использовать EXPLAIN ANALYZE, чтобы понять, как Postgres выбирает планы:
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25;
Это помогало увидеть, использует ли запрос индекс и почему.
«Планировщик — тот ещё хитрец, — говорил Query Planner, — он выбирает путь, который считает оптимальным, но не всегда угадывает. Индекс может не использоваться, если таблица маленькая или если фильтр слишком общий. А ещё есть VACUUM. Без регулярной уборки таблицы индексы могут терять свою эффективность.»
Билл кивнул. Он знал, что VACUUM — это тот самый процесс, который убирает мёртвые строки и держит данные в форме. «Значит, уборка так же важна, как и хороший план.»
Глава 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 оставался самым быстрым и умным городом баз данных, потому что его жители всегда соблюдали порядок в индексе и использовали подходящие инструменты. 🕵️♂️
Top comments (0)