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

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

Представь себе, у тебя есть таблица, ну, скажем, users. И ты пишешь запрос: «Дай мне всех юзеров со статусом 'ACTIVE'». А статус 'ACTIVE' — это, допустим, 80% всех записей. Ну, ёпта, почти все живые. Так вот, если у тебя есть индекс по полю status, то что сделает база? Она пойдет в этот индекс, быстро найдет там все ссылки на эти 80% строк, а потом — внимание — начнет делать хуеву тучу случайных чтений, чтобы по каждой этой ссылке сходить в саму таблицу и достать user_id, name и email. Это, блядь, как вместо того чтобы прочитать книгу подряд, ты сначала выпишешь оглавление, а потом будешь листать туда-сюда по рандомным страницам. Овердохуища лишних движений!

А теперь смотри на альтернативу: FULL TABLE SCAN. База просто берет и читает таблицу последовательно, блок за блоком, как нормальный человек книгу. Для выборки большой доли данных это часто оказывается быстрее, потому что последовательное чтение с диска — это святое, оно очень эффективное. Вот тебе и первый кейс: низкая селективность. Если твой WHERE отбирает больше 20-30% строк — готовься, что оптимизатор махнет рукой и пойдет читать всё.

Дальше, случай номер два. Допустим, тебе нужно почти все колонки из строки. SELECT * FROM orders WHERE user_id = 12345. Казалось бы, вот он, идеальный кандидат для индекса по user_id! Ан нет. Если в таблице orders 50 полей, а в индексе только user_id, то база сначала по индексу найдет строчки, а потом ей придется для КАЖДОЙ найденной строчки идти в саму таблицу и тащить оттуда все 50 полей. Это называется TABLE ACCESS BY INDEX ROWID, и это может быть пиздец как накладно. Если бы у тебя был покрывающий индекс (covering index), который включает ВСЕ нужные поля запроса, тогда да. А так — опять может выгоднее оказаться прочитать всю таблицу разом, если нужных записей много.

Третий момент — вообще смешной. Таблица маленькая. Ну правда, если твоя таблица занимает, условно, 5 блоков на диске, то какой смысл заморачиваться с индексом? База прочитает эти 5 блоков быстрее, чем ты успеешь сказать «ёперный театр». Это как искать спичку в коробке, перебирая каждую по номеру, вместо того чтобы высыпать все на стол.

Ну и четвертое — технические штуки. Иногда полное сканирование специально делают, чтобы собрать статистику по таблице побыстрее. Типа команда DBMS_STATS в Oracle. Это уже админские телодвижения.

Смотри на примеры, тут всё наглядно:

-- Скорее всего, вызовет FULL SCAN, если статус 'ACTIVE' имеют >40% записей.
SELECT user_id, name, email FROM users WHERE status = 'ACTIVE';

-- Индекс по user_id будет эффективен, так как выбирается одна запись.
SELECT * FROM orders WHERE user_id = 12345;

Суть в чем, чувак? Современные СУБД — они не дуры. Оптимизатор, будь то Oracle или PostgreSQL, смотрит на статистику (сколько строк, как данные распределены) и сам решает, что выгоднее: индекс или полное сканирование. Но если ты, как разработчик, этого не понимаешь, то можешь наломать дров: наклепать кучу ненужных индексов, которые только тормозить будут, потому что база будет их использовать там, где они неэффективны. Понимание этой простой логики — уже половина успеха в написании адекватных запросов и проектировании нормальной схемы. Волнение ебать, но это так.