Использование pg_stat_statements для анализа использования индексов и фильтров

Индексы — это как хранилище для закладок в книге. Они помогают быстро находить нужные данные. Но что, если мы добавили множество закладок, а никто ими не пользуется? Или, что хуже, неоптимальные закладки заставляют нас открывать книгу с самого начала до самого конца? Вот тут и появляется необходимость анализа использования индексов.

Плохо сконструированные запросы могут игнорировать индексы, что приводит к дорогостоящим последовательным сканированиям (Seq Scan). Это, в свою очередь, замедляет выполнение запросов и увеличивает нагрузку на сервер. Наша цель — понять, какие запросы не используют индексы и почему.

Как понять, используются ли индексы?

Давайте рассмотрим две ключевые проблемы:

1. Используются ли индексы, которые мы создали?
2. Если используются, эффективны ли они?

Для этих целей мы можем проанализировать статистику запросов в pg_stat_statements, обратив внимание на несколько столбцов:

• rows: количество строк, обработанных запросом.
• shared_blks_hit: количество страниц, прочитанных из памяти (а не с диска).
• shared_blks_read: количество страниц, фактически прочитанных с диска.

Чем меньше строк, обрабатываемых запросом, и чем выше доля shared_blks_hit относительно общего числа страниц, тем лучше работает наш индекс.

Пример анализа индексации

Допустим, у нас есть таблица студентов:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Добавим индекс по grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Теперь вставим данные для эксперимента:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Student ' || generate_series(1, 100000),
    'LastName',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Применим запрос, чтобы найти студентов с определённым уровнем:

SELECT *
FROM students
WHERE grade_level = 10;

Проверка в pg_stat_statements

Выполнив запрос несколько раз, мы можем проверить статистику:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Интерпретация результата:

• rows: Если запрос возвращает слишком много строк, есть ли смысл в индексе? Возможно, он не нужен для условий с низкой селективностью.
• shared_blks_hit и shared_blks_read: Если значительное количество страниц прочитано с диска (shared_blks_read), значит, либо индексация не работает, либо данные находятся вне буферного пула.

Оптимизация индексации

Создать индекс — это только половина дела. Важно, чтобы PostgreSQL действительно его использовал. Иногда, несмотря на все усилия, база почему-то выбирает не индекс, а последовательное сканирование всей таблицы. Почему так происходит? Давайте разберёмся.

Сначала посмотрим, почему индекс может игнорироваться даже при, казалось бы, очевидной пользе. А затем — какие приёмы помогут заставить базу «вспомнить», что индекс у нас всё-таки есть и использовать его по назначению.

Что, если индекс не используется?

Иногда PostgreSQL игнорирует индекс и выполняет последовательное сканирование (Seq Scan). Это может быть связано с несколькими причинами:

1. Низкая селективность условия. Если запрос возвращает больше половины строк таблицы, использовать последовательное сканирование может быть быстрее.
2. Тип данных или функции. Если в запросе вы используете функцию над индексируемым столбцом, индекс может быть проигнорирован. Например:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- Индекс не используется

В таких случаях можно переписать запрос:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Использует индекс

3. Неподходящий тип индекса. Например, для полнотекстового поиска лучше использовать GIN или GiST индексы, а не B-TREE.

4. Ошибки в составлении статистики. Если статистика устарела, оптимизатор может принимать неверные решения. Используйте ANALYZE:

   ANALYZE students;
   

Улучшение запроса

Вернёмся к нашему примеру. Если индексация не отрабатывает, можно применить следующие действия:

1. Убедитесь, что запрос использует фильтры, которые могут использовать индекс: не используйте функции, преобразования типов и т.д.
2. Если фильтр возвращает много значений, подумайте, нужен ли индекс. Если это частый запрос, попробуйте изменить структуры таблицы или добавить материализованные представления.
3. Если используется Seq Scan из-за большого объёма данных, попробуйте разбить таблицу на разделы (PARTITION BY).

Проверка эффективности индексации

После оптимизации повторно выполните запрос и проверьте статистику:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Сравните метрики до и после. Вы должны увидеть уменьшение чтения с диска (shared_blks_read) и увеличение количества попаданий (shared_blks_hit).

Реальные кейсы

1. Неправильное использование индекса

У нас есть таблица продуктов с текстовым полем description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Индекс для полнотекстового поиска
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Если мы делаем запрос:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

Индекс не будет использоваться! Причина в том, что ILIKE не совместим с GIN. Чтобы использовать индекс, запрос должен быть переписан:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Отсутствие индекса там, где он нужен

Допустим, запрос:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

выполняет последовательное сканирование (Seq Scan). Это может быть результатом отсутствия индекса по birth_date. Создав индекс:

CREATE INDEX idx_birth_date ON students(birth_date);

и пересчитав статистику (ANALYZE students), вы сможете значительно ускорить выполнение этого запроса.