Що таке частковий індекс і коли він доречний?

Частковий індекс (PostgreSQL) індексує лише рядки, що відповідають умові WHERE. Він менший, швидше оновлюється і краще тримається в пам'яті.

1. Запити завжди дивляться на малу частину таблиці:

-- 99% замовлень завершені, але черга обробки дивиться лише на pending
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

Індекс займає частку від повного, і запит WHERE status = 'pending' ORDER BY created_at бере з нього кілька рядків.

2. Унікальність лише серед частини рядків - найкорисніше застосування:

-- Email унікальний лише серед не видалених (soft delete)
CREATE UNIQUE INDEX users_email_active_uniq ON users (email) WHERE deleted_at IS NULL;

-- Лише одна активна підписка на користувача
CREATE UNIQUE INDEX subscriptions_one_active ON subscriptions (user_id) WHERE active;

Без часткового індексу видалений користувач «займав» би email назавжди, і зареєструватися знову було б неможливо.

Обмеження:

• Планувальник використає індекс, лише якщо з умови запиту можна вивести умову індексу. WHERE status = 'pending' - так; WHERE status = $1 з параметром, невідомим на етапі планування, - не завжди.
• MySQL часткових індексів не має. Альтернативи - згенерована колонка, яка NULL для «неактивних» рядків (унікальний індекс пропускає NULL), або функціональний індекс.

Докладніше в документації: Часткові індекси