Индексы - на логическом* уровне это отсортированные ключи колонки таблицы (для которой и создаётся индекс), которые имеют указатели на местонахождение строк основной таблицы со значением колонки в индексе. Если сильно упростить, то индекс в базах данных очень похож на индекс в конце книг. Когда вы открываете индекс на последних страницах книги, у вас есть список ключевых слов, отсортированных от А до Я. Для каждого слова есть номера страниц, где это слово упоминается, что облегчает поиск. По такому же принципу работают индексы и в БД.
Важно сказать о том, что индексы в большей степени используются в традиционных реляционных СУБД (таких как PostgreSQL, MySQL, Microsoft SQL Server и т.д.). Современные колоночные решения, такие как Google BigQuery, Snowflake или Amazon Redshift используют специальный слой метаданных, который решает задачу повышения эффективности поиска и скорости выполнения запросов.

Когда использовать:
- на больших таблицах;
- когда увеличение скорости выполнения запросов за счёт индексов имеет больший профит, чем затраты на хранение и поддержку индексов;
- на часто используемых полях в фильтрах для часто используемых запросов.
Предположим, у нас есть большая таблица orders, и мы часто, запрашивая данные и з неё, фильтруем данные по полю segment (сегмент клиента, который совершил заказ). Например, мы пишем такой запрос:

SELECT * FROM orders WHERE segment = 'Corporate';

Мы можем создать индекс на столбец "segment", чтобы увеличить скорость выполнения запроса. Например, в PostgreSQL индекс можно создать так:

CREATE INDEX segment_idx on orders (segment);

Т.е. создавать индексы - быстро и просто.
Индексы могут быть также составными - включать несколько столбцов.

Когда не использовать:
- на небольших таблицах. Индексы требуют дополнительного пространства для их хранения. Поэтому, если скорость выполнения запросов без индекса вас вполне устраивает, то и не нужно использовать дополнительные ресурсы для хранения индексов;
- на колонках с большим количеством null-значений;
- на часто обновляющихся таблицах. Частые операции вставки и обновления полей таблиц повышают нагрузку на СУБД, так как значения нужно записать/обновить в двух местах - в основной таблице и в индексе. Если вы часто производите удаления в основной таблице, то индекс становится фрагментированным (т.е. включает в себя пустые "листы"), что приводит к его неэффективности;
- на колнках с низкой кардинальностью (низким количеством уникальных значений). Например, если ваша колонка, на которую вы создаёте индекс, включает в себя только True/False значения, создание индекса не принесёт вам много профита.

* Я не просто в определении индекса написал "на логическом уровне". Логический уровень - это абстракция, которая позволяет описать объект обобщённо и как можно проще без углубления в детали. Есть ещё физический уровень, который описывает, чем на самом деле "под капотом" являются индексы. На физическом уровне для создания индексов используются специальные структуры данных: как правило, это B+tree или hash-таблицы. О структурах данных я тоже напишу, но сейчас я хочу двигаться от логического уровня к физическому для упрощения восприятия.