Преподаватель открывает терминал СУБД и демонстрирует реальный план выполнения запроса до и после создания составного индекса.
Прямая речь преподавателя:
«Взгляните на экран проектора. Мы выполняем поиск заказов клиента:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 502 AND status = 'NEW';
И что мы видим в выводе?
Seq Scan on orders (cost=0.00..45210.00 rows=12 width=80)
Filter: ((customer_id = 502) AND (status = 'NEW'))
Rows Removed by Filter: 1999988
Смотрите на цифру
Rows Removed by Filter: 1999988! База данных честно прочитала 2 миллиона записей с накопителя, выбросила почти все в корзину и вернула всего 12 строк. Стоимость (cost) — 45 210 условных единиц.
Теперь создаем правильный составной индекс:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
Повторяем тот же самый EXPLAIN ANALYZE:
Index Scan using idx_orders_customer_status on orders (cost=0.43..8.50 rows=12 width=80)
Index Cond: ((customer_id = 502) AND (status = 'NEW'))
Стоимость упала с 45 210 до 8.5! Читаются только 12 листовых записей, время отклика — 0.04 миллисекунды.
И вторая демонстрация: таблица
order_items, ссылающаяся на
orders. Внешний ключ не проиндексирован. Администратор удаляет старый тестовый заказ:
DELETE FROM orders WHERE id = 1;. Что делает СУБД? Чтобы убедиться, что в
order_items нет позиций этого заказа, она накладывает эксклюзивную блокировку на ВСЮ таблицу
order_items и запускает Seq Scan. В этот момент весь интернет-магазин перестает оформлять заказы. Добавляем
CREATE INDEX ON order_items (order_id); — и проверка ссылочной целостности происходит за одну операцию поиска по индексу без блокировки таблицы».