Почему запрос с LIKE '%…' или функцией от столбца в WHERE не использует индекс?

ЗнаниеMiddleИногда

Проверила команда СопроводОбновлено

Короткий ответ

B-tree хранит значения столбца отсортированными и находит строки по началу значения. У LIKE '%ова' начало неизвестно, а extract(year FROM birth_date) — другое значение, которого в индексе нет, поэтому планировщик читает таблицу целиком. Помогают условие по самому столбцу, индекс по выражению, класс text_pattern_ops для префикса или триграммы pg_trgm.

Почему этот вопрос задают

Именно такие запросы чаще всего тормозят в рабочей базе, хотя индекс на столбце вроде бы есть. Интервьюер хочет услышать причину из устройства B-tree и исправление под каждый случай.

Как рассуждать

  1. Начните с устройства B-tree: значения отсортированы, поиск идет от начала значения
  2. Объясните, почему '%ова' и функция от столбца не дают точки входа в индекс
  3. Покажите план: Seq Scan с Filter вместо Index Cond
  4. Предложите исправление под каждый случай: диапазон, индекс по выражению, pg_trgm

Ответ с примером

B-tree упорядочивает значения столбца, как словарь. По LIKE 'Ив%' база находит место, где начинаются фамилии на «Ив», и читает только этот участок. Шаблон '%ова' задает лишь конец фамилии, начало может быть любым, точки входа нет, и планировщик выбирает Seq Scan. С функцией та же история: индекс построен по birth_date, условие проверяет extract(year FROM birth_date), а таких значений в индексе нет.

Регистратура поликлиники ищет пациентов в таблице на 20 000 строк. Если база работает не в локали C, обычный индекс для LIKE не годится, поэтому индекс по фамилии построен с классом text_pattern_ops. Год рождения перепишите диапазоном birth_date >= '1985-01-01' AND birth_date < '1986-01-01', и запрос возьмет индекс. Для lower(last_name) создайте индекс по выражению, для поиска по середине фамилии — GIN-индекс с gin_trgm_ops.

SQL
EXPLAIN (COSTS OFF) SELECT id FROM patients WHERE last_name LIKE 'Ив%';
EXPLAIN (COSTS OFF) SELECT id FROM patients WHERE last_name LIKE '%ова';
EXPLAIN (COSTS OFF)
SELECT id FROM patients WHERE extract(year FROM birth_date) = 1985;
Вывод
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on patients
   Filter: (last_name ~~ 'Ив%'::text)
   ->  Bitmap Index Scan on patients_last_name_idx
         Index Cond: ((last_name ~>=~ 'Ив'::text) AND (last_name ~<~ 'Иг'::text))
(4 rows)

              QUERY PLAN
---------------------------------------
 Seq Scan on patients
   Filter: (last_name ~~ '%ова'::text)
(2 rows)

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on patients
   Filter: (EXTRACT(year FROM birth_date) = '1985'::numeric)
(2 rows)

Как ответить сильнее

Что добавит опытный кандидат

Что спросят следом

Доступно по подписке

Приемы опытных и готовые ответы

Частые ошибки

  • «Индекс не используется, потому что PostgreSQL его не видит, надо пересоздать»

    Откройте план EXPLAIN и посмотрите на условие: если там Filter с функцией или шаблон с процентом в начале, пересоздание индекса ничего не даст

  • «Для поиска по середине строки хватит обычного B-tree»

    Скажите, что B-tree ищет только по началу значения. Для '%ова%' нужен GIN-индекс на триграммах из расширения pg_trgm

  • «extract(year FROM birth_date) = 1985 возьмет индекс по birth_date»

    Перепишите условие диапазоном по самому столбцу: birth_date >= '1985-01-01' AND birth_date < '1986-01-01'. Тогда в плане появится Index Cond

  • «LIKE с шаблоном 'Ив%' всегда идет по индексу»

    Уточните: если база работает не в локали C, обычный индекс не подойдет, нужен индекс с классом text_pattern_ops

Сигналы для интервьюера

Сильный ответ

Объясняет поведение через устройство B-tree, читает план с Seq Scan и под каждый случай предлагает свое исправление

Тревожный сигнал

Знает правило про процент в начале шаблона, но не может его объяснить и не видит проблемы с функцией от столбца

Красный флаг

Предлагает создать еще один обычный индекс на тот же столбец и считает, что индекс работает для любого условия

Как еще могут спросить

  • Почему индекс не работает, если в WHERE стоит функция от столбца?
  • Почему поиск LIKE с процентом в начале идет полным сканированием таблицы?
  • Как сделать быстрым поиск по части фамилии в PostgreSQL?
  • В каких случаях PostgreSQL не использует существующий индекс?

Похожие вопросы

ЗнаниеJuniorЧасто

Что такое индекс и почему не стоит создавать его на каждый столбец?

Индекс — отдельная структура, в которой значения столбца хранятся упорядоченно вместе с адресами строк. По умолчанию в PostgreSQL это B-дерево: поиск по нему проходит несколько шагов вместо чтения всей таблицы. Индекс на каждый столбец вредит: вставка пишет во все индексы, обновление индексированного столбца тоже, индексы занимают место, а для условия на большую долю строк планировщик индекс даже не возьмет.

  • индексы
  • PostgreSQL
275
ЗнаниеSeniorИногда

Чем материализованное представление отличается от обычного и когда оно ускорит отчет по таблице событий?

Обычное представление хранит только текст запроса: каждый раз, когда дашборд его открывает, PostgreSQL заново считает агрегат по всей таблице событий. Материализованное хранит готовый результат, и отчет читает тысячи строк вместо сотен миллионов. Цена — данные на момент последнего REFRESH. Такое представление окупается, когда тяжелый агрегат читают часто, а задержка в час или сутки допустима.

  • оптимизация запросов
  • PostgreSQL
195
ЗнаниеMiddleЧасто

Как прочитать план EXPLAIN ANALYZE и найти в нем узел, на который уходит время?

План читают от вложенных узлов к верхнему: каждый узел получает строки от дочерних. Время в actual time включает время детей, поэтому собственное время узла — его время минус время дочерних узлов. Если узел запускался несколько раз, умножьте время на loops. Ищите узел с самым большим собственным временем и сверяйте оценку строк с фактом.

  • EXPLAIN
  • оптимизация запросов
263
ЗадачаJuniorЧасто

Найдите клиентов не из Москвы, включая тех, у кого город не указан

Условие city <> 'Москва' не вернет клиентов без города: сравнение с NULL дает NULL, и WHERE отбрасывает такую строку. Напишите WHERE city IS DISTINCT FROM 'Москва' или добавьте OR city IS NULL. Оба варианта вернут пять клиентов из восьми, простое неравенство — только три. Проговорите вслух, почему так происходит.

  • NULL
  • WHERE
245

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Скажите, что запустите EXPLAIN и посмотрите на узел чтения таблицы. Seq Scan с Filter значит, что индекс не помог, Index Scan или Bitmap Index Scan с Index Cond — что помог. Реальное время покажет EXPLAIN ANALYZE.

Ничего: таблицу в несколько страниц быстрее прочитать целиком, чем ходить через индекс. Планировщик сравнивает стоимость вариантов, и Seq Scan здесь честный выбор. Проверяйте индексы на объеме, близком к рабочему.

Главы документации PostgreSQL о типах индексов, индексах по выражениям и классах операторов, а также раздел о расширении pg_trgm. Читайте их с открытым psql и проверяйте каждый пример через EXPLAIN на своей таблице.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

Поговорите сначала с каждым по отдельности: что случилось, с чего началось и что мешает работать. Потом соберите обоих вместе и обсуждайте работу: кто за какие данные и шаги отвечает. За такой ссорой часто стоит размытая ответственность. Запишите договоренности, назовите новый срок для сорванного отчета и через пару недель проверьте, что все работает.

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227