Збір статистики використання індексів і таблиць

Уяви, що твоя база даних — це великий склад. Індекси — це каталоги і списки, які допомагають швидко знайти потрібне. Таблиці — це самі товари на полицях. Якщо індекс погано використовується, це як каталог, що лежить у далекому кутку і ніхто його не відкриває. Якщо таблиця активно використовується, але має погану структуру або зайві дані, це може напрягати наш склад (базу даних) і гальмувати його роботу.

Основні задачі аналізу:

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 постійно піддається послідовним скануванням. Це натяк: у каталозі товарів бракує індексів.

Типові помилки і як їх уникнути

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

Ще одна часта помилка — фанатичне додавання індексів. Пам’ятай, кожен індекс займає місце на диску і гальмує операції вставки, оновлення і видалення. Використовуй статистику pg_stat_user_indexes, щоб переконатися, що індекс дійсно потрібен, а не лежить мертвим вантажем.

Практична користь знань: де стане в нагоді?

У реальній розробці: якщо база даних гальмує, перше, що ти будеш шукати — проблеми з таблицями та індексами.

На співбесіді: питання про оптимізацію індексів — це класика жанру SQL-інтерв’ю. Вмієш пояснювати pg_stat_user_indexes? Ти вже наполовину пройшов випробування.

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