Глава 7 из 28

7. Индексы: B-tree, составные, частичные, покрывающие

Индекс — отдельная структура, которая позволяет найти нужные строки, не перебирая таблицу целиком. Работает как оглавление книги: вместо чтения всех страниц вы открываете указатель и сразу идёте на нужную. Но у оглавления есть цена: его нужно хранить и обновлять при каждой правке книги. В этой главе разберём, какой индекс когда нужен, как проверить, что база его действительно использует, и почему «проиндексировать всё» плохая стратегия.

Таблица в примерах одна: 100 000 заказов, плюс таблица пользователей для одного примера. Запросы можно запускать кнопкой «Выполнить». Время у вас будет другим, а число строк и вид плана совпадут. Скрипт с данными лежит в конце главы. Читать планы вы уже умеете из прошлой главы.

B-tree: основной тип индекса

Если написать CREATE INDEX без уточнений, получится B-tree, сбалансированное дерево. Значения в нём хранятся упорядоченно, поэтому он ускоряет сравнения =, <, >, BETWEEN, поиск по началу строки и сортировку. В большинстве случаев вам нужен именно он.

Начнём с запроса без индекса: все заказы одного клиента.

sql

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id = 777;

код

Seq Scan on orders  (cost=0.00..1988.00 rows=10 width=27) (actual time=0.383..6.911 rows=12 loops=1)
  Filter: (customer_id = 777)
  Rows Removed by Filter: 99988
Execution Time: 6.944 ms

База прочитала все 100 000 строк, чтобы вернуть двенадцать. Создадим индекс по customer_id и повторим:

sql

CREATE INDEX orders_customer_idx ON orders (customer_id);

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id = 777;

код

Bitmap Heap Scan on orders  (cost=4.37..41.00 rows=10 width=27) (actual time=0.062..0.084 rows=12 loops=1)
  Recheck Cond: (customer_id = 777)
  Heap Blocks: exact=12
  ->  Bitmap Index Scan on orders_customer_idx  (cost=0.00..4.37 rows=10 width=0) (actual time=0.043..0.043 rows=12 loops=1)
        Index Cond: (customer_id = 777)
Execution Time: 0.148 ms

Вместо 100 000 строк база прочитала 12 страниц таблицы (Heap Blocks: exact=12), а время упало примерно с 7 до 0.15 мс. Принцип плана Bitmap Index Scan → Bitmap Heap Scan разобран в прошлой главе.

Откуда ещё берутся индексы? PRIMARY KEY и UNIQUE создают индекс сами. Посмотрим, что есть у таблицы orders:

sql

SELECT indexname
FROM pg_indexes
WHERE schemaname = current_schema() AND tablename = 'orders'
ORDER BY indexname;

код

      indexname
---------------------
 orders_customer_idx
 orders_pkey

orders_pkey появился вместе с таблицей. Индекс на customer_id пришлось создать самим: PostgreSQL не создаёт индексы под внешние ключи. Если колонка ссылается на другую таблицу, индекс на неё почти всегда нужен.

Индекс стоит места

Индекс занимает диск и хранится отдельно от таблицы:

sql

SELECT pg_size_pretty(pg_relation_size('orders'))              AS table_size,
       pg_size_pretty(pg_relation_size('orders_customer_idx')) AS index_size;

код

 table_size | index_size
------------+------------
 5904 kB    | 920 kB

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

Селективность: когда индекс не помогает

Индекс выгоден, когда условие отбирает малую долю строк. Проверим на колонке status. В данных 60% заказов paid и около 1% new.

sql

CREATE INDEX orders_status_idx ON orders (status);

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'paid';

код

Seq Scan on orders  (cost=0.00..1988.00 rows=59177 width=27) (actual time=0.019..13.052 rows=59637 loops=1)
  Filter: (status = 'paid'::text)
  Rows Removed by Filter: 40363
Execution Time: 15.371 ms

Индекс есть, а база читает таблицу целиком. Это не ошибка: если нужно 60% строк, проще пройти таблицу подряд, чем 59 637 раз прыгать по индексу туда и обратно. Планировщик сравнивает стоимости и выбирает дешёвый путь. Редкое значение выглядит иначе:

sql

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'new';

код

Bitmap Heap Scan on orders  (cost=11.91..801.57 rows=983 width=27) (actual time=0.392..1.356 rows=1024 loops=1)
  Recheck Cond: (status = 'new'::text)
  Heap Blocks: exact=563
  ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..11.66 rows=983 width=0) (actual time=0.309..0.309 rows=1024 loops=1)
        Index Cond: (status = 'new'::text)
Execution Time: 1.505 ms

Отсюда правило: индекс по колонке с несколькими повторяющимися значениями (статус, пол, флаг) для общих запросов бесполезен. Он хорош для редких значений, и ниже мы научимся строить такой индекс без лишнего веса. Пока удалим этот индекс, чтобы он не мешал следующим примерам:

sql

DROP INDEX orders_status_idx;

Составной индекс и порядок колонок

Часто в условии участвует несколько колонок. Типичная задача: «три последних заказа клиента». Сейчас у нас есть индекс только по customer_id:

sql

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE customer_id = 777
ORDER BY created_at DESC
LIMIT 3;

код

Limit  (cost=41.13..41.14 rows=3 width=27) (actual time=0.079..0.080 rows=3 loops=1)
  ->  Sort  (cost=41.13..41.16 rows=10 width=27) (actual time=0.077..0.078 rows=3 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on orders  (cost=4.37..41.00 rows=10 width=27) (actual time=0.034..0.053 rows=12 loops=1)
              Recheck Cond: (customer_id = 777)
              Heap Blocks: exact=12
              ->  Bitmap Index Scan on orders_customer_idx  (cost=0.00..4.37 rows=10 width=0) (actual time=0.027..0.027 rows=12 loops=1)
                    Index Cond: (customer_id = 777)
Execution Time: 0.134 ms

База находит все 12 заказов клиента, а потом сортирует их (узел Sort), чтобы взять три. У одного клиента это дёшево, а у клиента с 50 000 заказов сортировка уже заметна. Создадим индекс по двум колонкам сразу:

sql

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE customer_id = 777
ORDER BY created_at DESC
LIMIT 3;

код

Limit  (cost=0.42..13.67 rows=3 width=27) (actual time=0.038..0.057 rows=3 loops=1)
  ->  Index Scan Backward using orders_customer_created_idx on orders  (cost=0.42..44.59 rows=10 width=27) (actual time=0.037..0.055 rows=3 loops=1)
        Index Cond: (customer_id = 777)
Execution Time: 0.084 ms

Узла Sort больше нет. Внутри индекса строки одного клиента лежат уже упорядоченными по created_at, и база идёт по ним с конца (Index Scan Backward), останавливаясь на третьей. Для клиента с миллионом заказов это разница между чтением миллиона строк и чтением трёх.

Порядок колонок в составном индексе принципиален. Индекс (customer_id, created_at) устроен как телефонный справочник, отсортированный по фамилии, а внутри фамилии по имени. По фамилии искать легко. По одному имени, без фамилии, справочник бесполезен. Так и здесь: индекс работает, если в условии есть ведущие колонки слева направо. Запрос только по created_at его не использует:

sql

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE created_at >= '2025-03-15' AND created_at < '2025-03-16';

код

Seq Scan on orders  (cost=0.00..2238.00 rows=378 width=27) (actual time=2.308..9.439 rows=360 loops=1)
  Filter: ((created_at >= '2025-03-15 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-16 00:00:00+00'::timestamp with time zone))
  Rows Removed by Filter: 99640
Execution Time: 9.468 ms

Для таких запросов нужен индекс, где created_at стоит первым:

sql

CREATE INDEX orders_created_idx ON orders (created_at);

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE created_at >= '2025-03-15' AND created_at < '2025-03-16';

код

Index Scan using orders_created_idx on orders  (cost=0.29..16.95 rows=333 width=27) (actual time=0.047..0.109 rows=360 loops=1)
  Index Cond: ((created_at >= '2025-03-15 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-03-16 00:00:00+00'::timestamp with time zone))
Execution Time: 0.146 ms

Практические правила для порядка колонок:

Сначала колонки, которые сравниваются на равенство (=), потом колонка с диапазоном или сортировкой.
Колонка нужна в индексе первой, если по ней одной тоже часто ищут. Иначе нужен отдельный индекс.
Не добавляйте колонки «на всякий случай»: каждая раздувает индекс.

Теперь о лишних индексах. Составной индекс (customer_id, created_at) умеет всё то же, что и одиночный (customer_id), потому что customer_id в нём ведущая колонка. Одиночный стал дублем: он занимает место и замедляет запись, но ничего не добавляет. Удалим его и убедимся, что запрос по одному customer_id работает:

sql

DROP INDEX orders_customer_idx;

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id = 777;

код

Bitmap Heap Scan on orders  (cost=4.50..41.13 rows=10 width=27) (actual time=0.027..0.042 rows=12 loops=1)
  Recheck Cond: (customer_id = 777)
  Heap Blocks: exact=12
  ->  Bitmap Index Scan on orders_customer_created_idx  (cost=0.00..4.49 rows=10 width=0) (actual time=0.020..0.021 rows=12 loops=1)
        Index Cond: (customer_id = 777)
Execution Time: 0.086 ms

Запрос выполняется так же быстро, теперь по составному индексу.

Частичный индекс: только нужные строки

Вернёмся к status = 'new'. Допустим, приложение постоянно показывает последние необработанные заказы:

sql

SELECT * FROM orders WHERE status = 'new' ORDER BY created_at DESC LIMIT 10;

Обычный индекс (status, created_at) с этим справится:

sql

CREATE INDEX orders_status_created_idx ON orders (status, created_at);

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE status = 'new'
ORDER BY created_at DESC
LIMIT 10;

код

Limit  (cost=0.42..21.92 rows=10 width=27) (actual time=0.053..0.061 rows=10 loops=1)
  ->  Index Scan Backward using orders_status_created_idx on orders  (cost=0.42..2193.55 rows=1020 width=27) (actual time=0.052..0.058 rows=10 loops=1)
        Index Cond: (status = 'new'::text)
Execution Time: 0.083 ms

Но в нём лежат все 100 000 строк, хотя запрос интересуют только 1% из них. Частичный индекс хранит лишь строки, подходящие под условие WHERE:

sql

CREATE INDEX orders_new_idx ON orders (created_at) WHERE status = 'new';

EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE status = 'new'
ORDER BY created_at DESC
LIMIT 10;

код

Limit  (cost=0.28..0.74 rows=10 width=27) (actual time=0.041..0.049 rows=10 loops=1)
  ->  Index Scan Backward using orders_new_idx on orders  (cost=0.28..46.27 rows=1000 width=27) (actual time=0.040..0.046 rows=10 loops=1)
Execution Time: 0.070 ms

Планировщик выбрал новый индекс. Сравним размеры:

sql

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE schemaname = current_schema()
  AND indexname IN ('orders_status_created_idx', 'orders_new_idx')
ORDER BY indexname;

код

         indexname         |  size
---------------------------+---------
 orders_new_idx            | 40 kB
 orders_status_created_idx | 3112 kB

Частичный индекс меньше примерно в 75 раз: он быстрее читается, лучше помещается в кэш, и при записи заказов с другим статусом его вообще не надо обновлять. Ограничение у него одно: он работает только для запросов, где условие индекса явно присутствует. Запрос с status = 'paid' этим индексом воспользоваться не сможет.

Частичные индексы подходят там, где интересна небольшая «живая» часть таблицы: необработанные заказы, неотправленные письма, активные записи при «мягком» удалении, ненулевые значения в разреженной колонке. Ещё одно применение: уникальность только среди части строк, например CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL разрешает повторно зарегистрировать удалённый адрес.

Покрывающий индекс и Index Only Scan

Обычно база находит строку по индексу и идёт в таблицу за остальными колонками. Если все нужные колонки уже лежат в самом индексе, поход в таблицу не нужен. Такое чтение называется Index Only Scan. Возьмём запрос на сумму заказов клиента:

sql

EXPLAIN (ANALYZE)
SELECT sum(amount) FROM orders WHERE customer_id = 777;

код

Aggregate  (cost=41.16..41.17 rows=1 width=32) (actual time=0.064..0.065 rows=1 loops=1)
  ->  Bitmap Heap Scan on orders  (cost=4.50..41.13 rows=10 width=6) (actual time=0.031..0.047 rows=12 loops=1)
        Recheck Cond: (customer_id = 777)
        Heap Blocks: exact=12
        ->  Bitmap Index Scan on orders_customer_created_idx  (cost=0.00..4.49 rows=10 width=0) (actual time=0.022..0.022 rows=12 loops=1)
              Index Cond: (customer_id = 777)
Execution Time: 0.133 ms

Колонки amount в индексе нет, поэтому приходится читать таблицу. Добавим её в индекс через INCLUDE. Такая колонка хранится в индексе, но по ней нельзя искать и сортировать, она нужна только для чтения:

sql

CREATE INDEX orders_customer_amount_idx ON orders (customer_id) INCLUDE (amount);

EXPLAIN (ANALYZE)
SELECT sum(amount) FROM orders WHERE customer_id = 777;

код

Aggregate  (cost=41.16..41.17 rows=1 width=32) (actual time=0.135..0.136 rows=1 loops=1)
  ->  Bitmap Heap Scan on orders  (cost=4.50..41.13 rows=10 width=6) (actual time=0.105..0.124 rows=12 loops=1)
        Recheck Cond: (customer_id = 777)
        Heap Blocks: exact=12
        ->  Bitmap Index Scan on orders_customer_amount_idx  (cost=0.00..4.49 rows=10 width=0) (actual time=0.091..0.091 rows=12 loops=1)
              Index Cond: (customer_id = 777)
Execution Time: 0.198 ms

Индекс подходящий, но Index Only Scan не появился. Причина в устройстве MVCC (глава 15): индекс не знает, видна ли строка текущей транзакции, это известно только таблице. Чтобы не ходить за каждой строкой в таблицу, PostgreSQL ведёт карту видимости — отметки о страницах, где все строки видны всем. Карту заполняет VACUUM. После загрузки данных она пуста, и обход таблицы остаётся необходим. Запустим VACUUM и повторим:

sql

VACUUM orders;

EXPLAIN (ANALYZE)
SELECT sum(amount) FROM orders WHERE customer_id = 777;

код

Aggregate  (cost=4.62..4.63 rows=1 width=32) (actual time=0.061..0.062 rows=1 loops=1)
  ->  Index Only Scan using orders_customer_amount_idx on orders  (cost=0.42..4.59 rows=10 width=6) (actual time=0.045..0.049 rows=12 loops=1)
        Index Cond: (customer_id = 777)
        Heap Fetches: 0
Execution Time: 0.118 ms

Теперь Index Only Scan, а Heap Fetches: 0 означает, что в таблицу не заглянули ни разу. Если там стоит большое число, карта видимости устарела: таблица часто меняется, и VACUUM не успевает. На таблицах, которые в основном читаются, выигрыш заметен. На активно изменяемых он тает.

Покрывающие индексы стоит заводить для горячих запросов, а не для всех подряд. Каждая включённая колонка делает индекс шире.

Индекс по выражению

Если в условии стоит функция от колонки, обычный индекс по колонке не подходит (это мы видели в прошлой главе). Типичный случай — регистронезависимый поиск по почте. В таблице users адреса записаны вперемешку с заглавными буквами:

sql

EXPLAIN (ANALYZE)
SELECT * FROM users WHERE lower(email) = 'user777@example.com';

код

Seq Scan on users  (cost=0.00..1086.00 rows=250 width=23) (actual time=0.542..24.149 rows=1 loops=1)
  Filter: (lower(email) = 'user777@example.com'::text)
  Rows Removed by Filter: 49999
Execution Time: 24.169 ms

Индекс можно построить по самому выражению:

sql

CREATE INDEX users_email_lower_idx ON users (lower(email));

EXPLAIN (ANALYZE)
SELECT * FROM users WHERE lower(email) = 'user777@example.com';

код

Bitmap Heap Scan on users  (cost=10.35..340.94 rows=250 width=23) (actual time=0.062..0.063 rows=1 loops=1)
  Recheck Cond: (lower(email) = 'user777@example.com'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on users_email_lower_idx  (cost=0.00..10.29 rows=250 width=0) (actual time=0.057..0.057 rows=1 loops=1)
        Index Cond: (lower(email) = 'user777@example.com'::text)
Execution Time: 0.101 ms

Выражение в запросе должно совпадать с выражением в индексе дословно: lower(email) подойдёт, а upper(email) или email ILIKE ... нет. Функция в индексе обязана быть неизменной (IMMUTABLE): для одних аргументов всегда один результат. Поэтому нельзя, например, проиндексировать now().

Другие типы индексов

B-tree покрывает большую часть задач, но не все.

Тип	Для чего	Пример
`GIN`	значения, состоящие из частей: `jsonb`, массивы, полнотекстовый поиск	`CREATE INDEX ON docs USING gin (tags)`
`GiST`	геометрия, диапазоны, ближайшие соседи	`CREATE INDEX ON places USING gist (location)`
`BRIN`	огромные таблицы, где значение растёт вместе с положением строки	`CREATE INDEX ON events USING brin (created_at)`
`Hash`	только точное равенство; B-tree обычно не хуже	редко нужен

BRIN хранит не каждое значение, а минимум и максимум для блока страниц. Поэтому он работает только там, где физический порядок строк совпадает со значениями. У заказов это так: они добавлялись по времени. Мерой служит корреляция в статистике планировщика:

sql

SELECT attname, round(correlation::numeric, 2) AS correlation
FROM pg_stats
WHERE schemaname = current_schema()
  AND tablename = 'orders'
  AND attname IN ('created_at', 'customer_id')
ORDER BY attname;

код

   attname   | correlation
-------------+-------------
 created_at  |        1.00
 customer_id |        0.00

У created_at корреляция 1: чем позже заказ, тем дальше он лежит в таблице. У customer_id близко к нулю: клиенты перемешаны. Для created_at BRIN годится, для customer_id нет. Сравним размеры с B-tree по той же колонке:

sql

CREATE INDEX orders_created_brin ON orders USING brin (created_at);

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE schemaname = current_schema()
  AND indexname IN ('orders_created_idx', 'orders_created_brin')
ORDER BY indexname;

код

      indexname      |  size
---------------------+---------
 orders_created_brin | 24 kB
 orders_created_idx  | 2208 kB

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

Индексы не бесплатны

Каждый INSERT добавляет запись в каждый индекс таблицы. UPDATE колонки, которая входит в индекс, обновляет и сам индекс. DELETE оставляет мусор, который потом убирает VACUUM. Чем больше индексов, тем медленнее запись. Посмотрим на итог:

sql

SELECT s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
WHERE s.schemaname = current_schema()
ORDER BY pg_relation_size(s.indexrelid) DESC, s.indexrelname;

код

            index            |  size
-----------------------------+---------
 orders_status_created_idx   | 3112 kB
 orders_customer_amount_idx  | 3104 kB
 orders_customer_created_idx | 3104 kB
 orders_created_idx          | 2208 kB
 orders_pkey                 | 2208 kB
 users_email_lower_idx       | 2008 kB
 users_pkey                  | 1112 kB
 orders_new_idx              | 40 kB
 orders_created_brin         | 24 kB

Сравним с таблицей:

sql

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders'))  AS all_indexes;

код

 table_size | all_indexes
------------+-------------
 5904 kB    | 14 MB

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

Найти индексы, которыми никто не пользуется, помогает счётчик обращений. Выполняйте запрос на рабочей базе, которая проработала достаточно долго (неделя-месяц, чтобы пройти все периодические отчёты):

sql

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Индексы с idx_scan = 0 — кандидаты на удаление. Не удаляйте слепо: индекс под UNIQUE, PRIMARY KEY или внешний ключ нужен, даже если по нему никто не читает, а статистика обнуляется при сбросе. Перед удалением сохраните определение (pg_get_indexdef), чтобы его можно было вернуть.

Создание индекса на живой базе

Обычный CREATE INDEX блокирует запись в таблицу на всё время построения: все INSERT, UPDATE и DELETE встают в очередь. На больших таблицах это минуты и часы простоя. Для рабочей базы используйте CONCURRENTLY:

sql

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

Запись не блокируется, но построение идёт дольше, и команду нельзя выполнить внутри транзакции. Если построение оборвётся (отмена, нехватка места, ошибка уникальности), останется индекс со статусом INVALID: он занимает место и замедляет запись, но запросы им не пользуются. Найдите такие индексы в pg_index (колонка indisvalid = false), удалите и создайте заново. Удалять тоже лучше без блокировки: DROP INDEX CONCURRENTLY. Перестроить раздутый индекс помогает REINDEX INDEX CONCURRENTLY.

Чек-лист

Начните с плана запроса, а не с индекса. Нужен ли он, видно по Rows Removed by Filter и по тому, что запрос тормозит именно на чтении.
Проверьте селективность: условие должно отбирать малую долю строк. Если речь о десятках процентов таблицы, индекс вряд ли будет использован.
Колонки с равенством ставьте в составном индексе первыми, колонку диапазона или сортировки последней.
Для узкой «живой» части таблицы берите частичный индекс.
После создания индекса снова запустите EXPLAIN ANALYZE и убедитесь, что он использован.
Раз в квартал проверяйте дубли и индексы с idx_scan = 0.

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

Индекс «на каждую колонку». Они замедляют запись, занимают место и по отдельности не помогают запросам по нескольким колонкам сразу.
Не создать индекс на внешний ключ. Соединения и удаление родительской строки читают дочернюю таблицу целиком.
Функция или приведение типа над колонкой в WHERE. Индекс по колонке не используется. Либо перепишите условие, либо постройте индекс по выражению.
Ждать пользы от индекса по колонке с двумя-тремя значениями. Для частых значений база всё равно прочитает таблицу.
LIKE '%текст'. Шаблон с процентом в начале не использует B-tree. Поиск по началу строки (LIKE 'текст%') работает с B-tree только при подходящей сортировке: в базе с не-C локалью индекс нужно строить с классом операторов text_pattern_ops. Для поиска по любой части строки нужно расширение pg_trgm и индекс GIN.
Дублирующие индексы. (a) и (a, b) одновременно: первый лишний.
CREATE INDEX без CONCURRENTLY на большой рабочей таблице. Запись встанет на всё время построения.
Забыть про VACUUM. Без него Index Only Scan не получает выигрыша.

Построение индексов и чтение планов отрабатывается на заданиях в курсе «Оптимизация и индексы».

Данные для примеров

Этот блок выполняется перед примерами главы. SET max_parallel_workers_per_gather = 0 отключает параллельное чтение, чтобы планы оставались простыми. Заказы создаются в хронологическом порядке (дата растёт вместе с номером), как в реальной таблице, куда записи только добавляются. Статусы распределены неравномерно: примерно 1% new и 60% paid.

sql

SET max_parallel_workers_per_gather = 0;

CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_id int NOT NULL,
  status text NOT NULL,
  amount numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL
);

SELECT setseed(0.42);
INSERT INTO orders (customer_id, status, amount, created_at)
SELECT 1 + (random()*9999)::int,
       CASE WHEN r < 0.01 THEN 'new'
            WHEN r < 0.61 THEN 'paid'
            WHEN r < 0.81 THEN 'shipped'
            WHEN r < 0.99 THEN 'done'
            ELSE 'cancelled' END,
       round((random()*5000)::numeric, 2),
       timestamptz '2025-01-01' + g * interval '4 minutes'
FROM (SELECT g, random() AS r FROM generate_series(1, 100000) g) s;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text NOT NULL
);
INSERT INTO users (email)
SELECT 'User' || g || '@' || (ARRAY['Example.com','Mail.ru','Yandex.ru'])[1 + g % 3]
FROM generate_series(1, 50000) g;

ANALYZE orders;
ANALYZE users;

Откройте остальные главы

Ещё 20 глав: внутреннее устройство, оптимизация, администрирование. Покупка разовая, доступ навсегда.

илиОткрыть с подпиской Pro

НазадКак читать EXPLAIN и EXPLAIN ANALYZE ДальшеCTE, рекурсия и LATERAL