Переваги статистики стовпців

Технічні статистики стовпців надають корисну інформацію про дані, що зберігаються в таблиці. Цю інформацію можна використовувати для оптимізації роботи бази даних та покращення ефективності запитів.

Використовуючи Postgres SQL

Запустіть нижче наведений скрипт 🚀

SELECT  
 attname AS column_name,  
 n_distinct,  
 most_common_vals,  
 most_common_freqs  
FROM  
 pg_stats  
WHERE  
 tablename = 'table_name';

Пояснення

Цей запит використовується для отримання статистики стовпців для конкретної таблиці в базі даних PostgreSQL.

Пояснення стовпців:

1. column_name: Назва стовпця в таблиці.
2. n_distinct: Кількість унікальних (відмінних) значень у стовпці.

• Позитивне число вказує на фактичну кількість унікальних значень.
• Негативне число є оцінкою пропорції відмінних значень відносно загальної кількості рядків у таблиці.

1. most_common_vals: Найбільш поширені значення у стовпці.

2. most_common_freqs: Відсоткова частота кожного з найпоширеніших значень.

Ось практичні переваги та способи використання цієї інформації: 👇

1. Покращення дизайну індексів 📉

• Перевага:

Якщо стовпець має велику кількість унікальних значень (висока кардинальність), він ймовірно підходить для індексації.

• Якщо стовпець має малий кількість унікальних значень (низька кардинальність), додавання індексу може не дати вигоди і бути затратним.
• Застосування:
• Наприклад, якщо у вас є стовпець status, який містить лише три значення (active, inactive, pending), що часто використовуються в WHERE умовах, індексація може не покращити продуктивність через низьку кардинальність.
• Однак, якщо стовпець email містить унікальні значення для кожного рядка, індексація може значно покращити ефективність запитів.

2. Оптимізація продуктивності запитів 🚤

• Перевага:
• Якщо стовпець містить дуже поширені значення (наприклад, за замовчуванням або часто повторювані значення), розуміння розподілу даних може допомогти оптимізувати запити, орієнтуючись на ці значення.
• Застосування:
• Використовуючи most_common_vals та most_common_freqs, можна визначити найбільш поширені значення. Наприклад, якщо значення active зустрічається в 90% рядків, запити на інші значення можуть виграти від оптимізованої індексації або переписування запитів.

3. Покращення продуктивності для великих таблиць 🚗

• Перевага:
• Розуміння розподілу даних у стовпці дозволяє мінімізувати повні сканування таблиць.
• Застосування:
• У великих таблицях з багатьма стовпцями статистика може допомогти визначити, які стовпці потребують індексації на основі їх використання у WHERE та JOIN умовах.

4. Краще прийняття рішень щодо часткової індексації 🤔

• Перевага:
• Для стовпців з високою частотою повторення значень (як-от NULL або певне значення за замовчуванням) часткові індекси, що орієнтовані на найбільш вживані значення, можуть покращити ефективність.
• Застосування:
• Якщо стовпець status містить [active, inactive] і active становить 95% рядків, ви можете створити частковий індекс для рядків зі статусом inactive:

add_index :table_name, :status, where: "status = 'inactive'"

5. Покращення загальної ефективності статистики даних 🚃

• Перевага:
• Розуміння загального розподілу даних допомагає написати кращі запити або переробити таблиці для несбалансованих даних.
• Застосування:
• Якщо статистика показує, що стовпець містить велику кількість унікальних значень, можна розглянути розділення таблиці на основі цього стовпця для покращення продуктивності.

6.

Покращення дизайну таблиці ⚡️

• Перевага:
• Якщо статистика показує, що стовпець містить дуже мало повторюваних значень (наприклад, поля з вибором), можна розглянути можливість зміни його типу на ENUM замість простого тексту.
• Застосування:
• Наприклад, якщо стовпець gender містить лише [male, female, other], використання типу ENUM може зменшити розмір зберігання і підвищити ефективність.

Як використовувати статистику стовпців?

Аналізуйте стовпці для підходящості індексації:

• Якщо стовпець часто використовується в WHERE або JOIN умовах, індексація може мати позитивний ефект, якщо він містить багато унікальних значень.

Створюйте часткові індекси на основі найбільш поширених значень:

• Створюйте індекси, орієнтуючись на менш поширені значення, щоб покращити ефективність і зменшити розмір індексу.

Пишіть оптимізовані запити:

• Уникайте неефективних запитів, які залежатимуть від стовпців з не врівноваженим розподілом даних.

Переробляйте або розділяйте таблиці:

• Для таблиць з нерівномірним розподілом даних розгляньте можливість їх переробки або розділення для покращення продуктивності.

Перекладено з: Column Statistics Benefits