Питання на співбесіді: Індекси

Планувальник оцінює кількість рядків для умов окремо по кожній колонці й припускає, що колонки незалежні: вибірковість A AND B = вибірковість A × вибірковість B.

Проблема - корельовані колонки:

SELECT * FROM addresses WHERE city = 'Львів' AND region = 'Львівська';

Якщо city = 'Львів' дає 2% рядків, а region = 'Львівська' - 5%, планувальник очікує 0.1%. Насправді ж майже всі Львови - у Львівській області, і результат близький до 2% - у 20 разів більше. З такою оцінкою він обирає Nested Loop там, де потрібен Hash Join, і запит сповільнюється на порядки.

Розширена статистика (PostgreSQL 10+) дозволяє зібрати статистику по комбінації колонок:

CREATE STATISTICS addresses_city_region (dependencies, ndistinct, mcv)
    ON city, region FROM addresses;

ANALYZE addresses;

Види:

• dependencies - функціональні залежності (місто визначає область).
• ndistinct - кількість унікальних комбінацій значень; важливо для оцінки GROUP BY city, region.
• mcv (PostgreSQL 12+) - найчастіші комбінації значень з частотами; найточніша для конкретних пар значень.

З PostgreSQL 14 статистику можна збирати й для виразів: CREATE STATISTICS ... ON (lower(email)) FROM users.

Як зрозуміти, що це потрібно: EXPLAIN ANALYZE показує оцінку rows, що в десятки разів відрізняється від actual rows, на умові з кількома колонками однієї таблиці, а статистика окремих колонок свіжа.

Обмеження: допомагає лише для колонок однієї таблиці і лише для умов рівності й групування. Статистика займає місце і збирається під час ANALYZE, тож створюють її точково - для проблемних запитів.

Докладніше в документації: CREATE STATISTICS