Чому PostgreSQL не використовує індекс для запиту з LIKE?

B-tree індекс упорядковує рядки за значенням зліва направо — як словник за першою літерою. Він допомагає знайти все, що починається з певного префікса (LIKE 'ivan%'), але не все, що закінчується на щось (LIKE '%@gmail.com'). Для такого запиту довелося б переглянути весь індекс — і планувальник чесно обирає Seq Scan.

Варіанти:

1. Триграмний індекс — для пошуку підрядка в будь-якому місці:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY customers_email_trgm_idx
  ON customers USING gin (email gin_trgm_ops);

Після цього LIKE '%@gmail.com' і ILIKE '%ivan%' використовуватимуть індекс.

2. Окрема колонка з доменом — якщо пошук за доменом типовий:

ALTER TABLE customers ADD COLUMN email_domain text
  GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED;
CREATE INDEX CONCURRENTLY ON customers (email_domain);

SELECT id, email FROM customers WHERE email_domain = 'gmail.com' LIMIT 50;

Це найшвидший варіант для саме цієї задачі.

3. Для префіксного пошуку з не-C колацією потрібен індекс з text_pattern_ops:

CREATE INDEX CONCURRENTLY ON customers (email text_pattern_ops);

І не забудьте, що якщо пошук нечутливий до регістру (ILIKE або lower(email)), індекс теж має бути на lower(email) — або використовуйте тип citext.