Сбор статистики использования индексов и таблиц

Представьте, что ваша база данных — это большой склад. Индексы — это каталоги и список, который помогает быстро найти нужное. Таблицы — это сами товары на полках. Если индекс плохо используется, это всё равно, что каталог лежит в дальнем углу и никто его не открывает. Если таблица активно используется, но у нее плохая структура или избыточные данные, это может напрягать наш склад (базу данных) и замедлять его работу.

Основные задачи анализа:

1. Оценка эффективности использования индексов. Например, ваш дорогущий индекс лежит без дела? Выбросьте его!
2. Определение частоты операций чтения и записи. Помогает понять, какие таблицы активно используются.
3. Оптимизация запросов. Статистика помогает выяснить, где можно ускорить обработку данных, добавив или изменив индексы.

Представления pg_stat_user_indexes и pg_stat_user_tables

В PostgreSQL есть два очень полезных представления для сбора статистики: pg_stat_user_indexes и pg_stat_user_tables. Давайте разберём их подробнее.

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

Основные поля:

• relname — имя таблицы, к которой относится индекс.
• indexrelname — имя индекса.
• idx_scan — сколько раз индекс был использован для поиска.
• idx_tup_read — количество строк, считанных с помощью индекса.
• idx_tup_fetch — количество реально возвращённых строк (после применения фильтров).

Пример запроса:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Здесь мы:

• сортируем данные по количеству вызовов индекса (idx_scan), чтобы увидеть, какие индексы наиболее популярны.
• если индекс практически не используется (idx_scan = 0), стоит задуматься: а нужен ли он вообще?

Практическое применение:

Вы развёртываете новую версию приложения и добавили новый индекс. С помощью pg_stat_user_indexes вы можете проверить, действительно ли ваш запрос начал использовать новый индекс, или PostgreSQL по-прежнему выбрал старый путь, игнорируя ваш шедевр оптимизации.

pg_stat_user_tables: просмотр данных по таблицам

Основные поля:

• relname — имя таблицы.
• seq_scan — количество последовательных сканирований таблицы (без использования индексов).
• seq_tup_read — количество строк, возвращённых из таблицы при последовательных сканированиях.
• idx_scan — количество индексных сканирований для таблицы.
• n_tup_ins — количество вставленных строк
• n_tup_upd — количество обновлённых строк.
• n_tup_del — количество удалённых строк.

Пример запроса:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Что мы здесь видим?

• Таблицы с большим количеством последовательных сканирований (seq_scan) могут сигнализировать о необходимости добавить индекс.
• Количество операций вставки, обновления и удаления помогает понять, насколько часто изменяются данные в таблице.

Практическое применение: Вы работаете с таблицей users, в которой хранятся данные всех пользователей вашего приложения. С помощью pg_stat_user_tables вы обнаруживаете, что последовательные сканирования (seq_scan) этой таблицы просто зашкаливают. Это подсказка: пора создавать индексы по наиболее используемым столбцам, чтобы ускорить работу запросов.

Пример: анализ индексов и таблиц в реальной базе данных

Допустим, у нас есть база данных с таблицами orders (заказы) и products (товары). Мы хотим понять, как эффективно используются таблицы и индексы.

Анализ индексов:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Вы видите, что индекс orders_customer_id_idx был вызван 50 тысяч раз, а вот orders_date_idx всего 5 раз. Возможно, orders_date_idx не нужен.

Анализ таблиц:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

Таблица products постоянно подвергается последовательным сканированиям. Это намёк: в каталоге товаров не хватает индексов.

Типичные ошибки и как их избежать

Cтандартная ловушка для новичков — это игнорировать статистику. Например, вы добавили новый индекс с мыслями: «Всё, теперь запросы будут летать», а PostgreSQL его не использует, потому что статистика не обновлялась автоматически. После значительных изменений в таблицах не забывайте обновлять статистику вручную с помощью команды ANALYZE.

Ещё одна частая ошибка — фанатичное добавление индексов. Помните, каждый индекс занимает место на диске и замедляет операции вставки, обновления и удаления. Используйте статистику pg_stat_user_indexes, чтобы убедиться, что индекс действительно востребован, а не лежит мёртвым грузом.

Практическая польза знаний: где пригодится?

В реальной разработке: если база данных тормозит, первым делом вы будете искать проблемы с таблицами и индексами.

На собеседовании: вопросы про оптимизацию индексов — это классика жанра SQL-интервью. Умеете объяснять pg_stat_user_indexes? Вы уже наполовину прошли испытание.

В администрировании баз данных: мониторинг — это ежедневная рутина DBA. Без статистики о таблицах и индексах вы ничего не сможете улучшить.

2

Задача

SQL SELF, 46 уровень, 3 лекция

Недоступна

Просмотр неиспользуемых индексов

Просмотр неиспользуемых индексов

Открыть