Використання 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), ти зможеш суттєво прискорити виконання цього запиту.