DML и индексы

Все примеры в лабораторной выполняются на учебной базе demo, развёрнутой в лабораторной работе 3. Подключитесь к базе и установите схему поиска

\c demo
SET search_path TO bookings;

Убедитесь, что таблицы схемы bookings доступны

\dt

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

Временная таблица создаётся командой CREATE TEMP TABLE и автоматически удаляется при закрытии сеанса. Если копия повреждена — достаточно переподключиться и создать её заново. Если же изменена постоянная таблица (например, командой без WHERE) и требуется восстановить исходное состояние — базу можно пересоздать из дампа demo_small.sql.

Версия демонстрационной базы. Все запросы в работе написаны для редакции 2017 года (demo_small.sql), как и в лабораторной 5. Начиная с 1 сентября 2025 года ее обновили, ознакомиться с актуальной версией можно на сайте Postgres Pro.

Подготовка рабочей таблицы

Для экспериментов создадим копию aircrafts, над которой можно выполнять любые INSERT, UPDATE и DELETE без риска испортить данные. Конструкция WITH NO DATA копирует только структуру таблицы, без строк

CREATE TEMP TABLE aircrafts_tmp AS
SELECT * FROM aircrafts WITH NO DATA;

ALTER TABLE aircrafts_tmp ADD PRIMARY KEY (aircraft_code);
ALTER TABLE aircrafts_tmp ADD UNIQUE (model)

Ограничения целостности (PRIMARY KEY, UNIQUE) при копировании структуры не переносятся, поэтому накладываются отдельно командой ALTER TABLE

Проверка созданной таблицы

\d aircrafts_tmp

Вставка строк: INSERT

С INSERT мы уже познакомились в лабораторной работе №3, здесь рассмотрим ситуации, которые уже чуть больше приближены к реальным практическим вариантам использования.

INSERT … SELECT. Заполнить таблицу данными из другой таблицы проще всего подзапросом, без ручного перечисления строк

INSERT INTO aircrafts_tmp
SELECT * FROM aircrafts

После выполнения команды PostgreSQL сообщит о количестве вставленных строк (INSERT 0 9). Первое число — исторический OID строки, в современных версиях PostgreSQL (12 и новее) он всегда равен 0, поскольку поддержка таблиц WITH OIDS удалена. Второе число — количество затронутых строк. Проверка результата

SELECT * FROM aircrafts_tmp ORDER BY model

Количество атрибутов и их типы во вставляемых строках должны быть согласованы с целевой таблицей. При явном перечислении столбцов (INSERT INTO t (a, b, c)) подзапрос допускает меньше столбцов, чем есть в таблице — недостающие получат значения по умолчанию или NULL

Предложение RETURNING. Любая команда INSERT, UPDATE или DELETE в PostgreSQL способна возвращать строки, над которыми она отработала. В конец команды добавляется RETURNING с перечислением нужных столбцов или *

INSERT INTO aircrafts_tmp
VALUES ('IL9', 'Ilyushin IL96', 9800)
RETURNING *

Команда выведет добавленную строку. Это удобно для проверки результата и особенно полезно при генерации первичного ключа СУБД — RETURNING сразу возвращает сгенерированное значение без повторного SELECT

Предложение RETURNING — расширение PostgreSQL, не входящее в стандарт SQL

Разрешение конфликтов. При вставке строки возможно нарушение ограничения первичного или уникального ключа. По умолчанию PostgreSQL прерывает команду с ошибкой. Для пропуска такой строки без ошибки используется предложение ON CONFLICT DO NOTHING

INSERT INTO aircrafts_tmp
VALUES ('SU9', 'Sukhoi SuperJet-100', 3000)
ON CONFLICT DO NOTHING

Команда вернёт INSERT 0 0 — ноль вставленных строк, без ошибки. Без указания конкретного ограничения проверка идёт по всем: первичному ключу и всем уникальным. Для явного указания столбца, по которому допустим конфликт, используется запись ON CONFLICT (aircraft_code)

INSERT INTO aircrafts_tmp
VALUES ('SU9', 'Sukhoi SuperJet-100', 3000)
ON CONFLICT (aircraft_code) DO NOTHING
RETURNING *

Здесь проверяется конфликт только по aircraft_code. Если вставляемая строка конфликтует по другому ключу (например, уникальному model), команда всё равно завершится ошибкой, поскольку этот конфликт не заявлен в ON CONFLICT

INSERT ... ON CONFLICT — расширение PostgreSQL. В стандарте SQL аналогичную задачу решает команда MERGE (PostgreSQL поддерживает её с версии 15)

На любимиом нами сайте sql-ex ознакомтесь с информацией в разделе: Оператор INSERT. Упражнения — в отдельном DML-этапе sql-ex.ru. Доступ к нему открывается после прохождения обучающего (SELECT) этапа

Также советую ознакомиться с документацией в официальном разделе документации Postgres: PostgreSQL docs — INSERT, ON CONFLICT.

Задание

1. Попробуйте вставить в aircrafts_tmp строку с уже существующим aircraft_code — получите сообщение об ошибке. Повторите команду с ON CONFLICT (aircraft_code) DO NOTHING и убедитесь, что ошибки больше нет
2. Добавьте в aircrafts_tmp две новые строки (например, Ilyushin IL96 и Ilyushin IL96-300) одной командой INSERT ... VALUES (...), (...) RETURNING *. Проверьте, что выводятся обе добавленные строки

Обновление строк: UPDATE

Команда UPDATE изменяет существующие строки таблицы. Его обязательная составляющая ключевое слово SET, задающее, какие столбцы и на какие значения обновить. А при помощи WHERE можно ограничить множество строк, к которым применяется обновление

UPDATE aircrafts_tmp
SET range = range * 1.2
WHERE model ~ '^Bom'

Команда увеличивает на 20% дальность полёта всех самолётов, название которых начинается с «Bom» (регулярное выражение ^Bom). Оператор ~ — проверка на соответствие регулярному выражению POSIX (расширение PostgreSQL, в стандарте SQL соответствующий оператор — SIMILAR TO)

Как и у INSERT, в конец команды UPDATE можно добавить RETURNING, чтобы увидеть изменённые строки

UPDATE aircrafts_tmp
SET range = range * 1.2
WHERE model ~ '^Bom'
RETURNING *

Обратите внимание! Самая частая и опасная ошибка при работе с UPDATE — пропущенное предложение WHERE. Без него команда обновит все строки таблицы. То же самое относится к DELETE. Безопасный приём — сперва выполнить SELECT с тем же WHERE и проверить, какие строки отбираются, а затем заменить SELECT на UPDATE или DELETE

Задание

1. В aircrafts_tmp одной командой увеличьте range на 10% для всех самолётов Airbus и верните изменённые строки через RETURNING.

Удаление строк: DELETE

Команда DELETE удаляет строки таблицы. Как и у UPDATE, ключевая часть — предложение WHERE. Удаление из рабочей копии aircrafts_tmp моделей Bombardier

DELETE FROM aircrafts_tmp
WHERE model ~ '^Bom'

Предложение RETURNING возвращает удалённые строки, что удобно для проверки правильности фильтра

DELETE FROM aircrafts_tmp
WHERE model ~ '^Bom'
RETURNING *

TRUNCATE: быстрая очистка таблицы. Для удаления всех строк таблицы вместо DELETE FROM t без условий предпочтительна команда TRUNCATE

TRUNCATE aircrafts_tmp

DELETE обходит таблицу построчно и для каждой строки записывает факт удаления в журнал операций. TRUNCATE сразу освобождает всё хранилище таблицы, записывая только одну операцию. На больших таблицах это даст существенный выигрыш в скорости выполнения операции. Однако, у TRUNCATE есть свои ограничения: невозможно сделать откат на уровне отдельной строки, команда не применяется к таблице, на которую ссылаются внешние ключи без CASCADE и не активирует построчные триггеры BEFORE DELETE и AFTER DELETE (Триггеры - это набор функций, который вызывается при совершении определнных действий над таблицей / набором данных).

Как обычно, предлагаю ознакомиться и попрактиковаться на сайте PostgresPro, раздел операторы UPDATE и DELETE. Упражнения — в DML-этапе вместе с задачами на INSERT

Более подробная и расширенная документация на сайте PostgresPro: PostgreSQL docs — UPDATE, DELETE, TRUNCATE

Задание

1. В aircrafts_tmp одной командой удалите все модели Bombardier (model ~ '^Bom') с выводом удалённых строк через RETURNING. Убедитесь, что количество строк в таблице уменьшилось на ожидаемое число

Индексы: общая информация

На малых таблицах SQL-команды выполняются практически мгновенно. На больших таблицах (миллионы и десятки миллионов строк) время выполнения запроса становится ощутимым и зависит от того, какие индексы построены на таблице

Индекс — вспомогательная структура данных, связанная с таблицей и построенная на основе её столбцов. Строки таблицы хранятся неупорядоченно, в порядке вставки, а записи индекса упорядочены. Каждая запись индекса содержит значение индексируемого столбца и указатель на соответствующую строку таблицы. При поиске СУБД обращается не к полному перебору таблицы, а к отсортированному индексу (поиск за логарифмическое время), после чего переходит к таблице по прямой ссылке

По умолчанию PostgreSQL создаёт индексы типа B-tree (сбалансированное дерево). Этот тип подходит для большинства типовых задач — поиск по равенству и диапазону, сортировка, соединения. Существуют и другие типы — Hash, GiST, SP-GiST, GIN, BRIN — применяемые в более узких случаях (полнотекстовый поиск, геоданные, массивы и т. п.). Мы остановимся только на наиболее простых и классических - B-tree

Индексы, создаваемые автоматически. При наложении на таблицу ограничения PRIMARY KEY или UNIQUE PostgreSQL автоматически создаёт уникальный индекс для поддержки этого ограничения — проверка дубликатов через индекс выполняется за логарифмическое время. Индексы таблицы boarding_passes

\d boarding_passes

В выводе присутствует раздел «Индексы»

Индексы:
    "boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
    "boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
    "boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no)

Имена индексов, созданных автоматически, имеют узнаваемый вид: для первичного ключа — <таблица>_pkey, для уникального ключа — <таблица>_<столбцы>_key

Создание индекса вручную. Пользовательские индексы создаются командой CREATE INDEX

CREATE INDEX
ON airports (airport_name)

Имя индекса необязательно — PostgreSQL сгенерирует его автоматически в виде <таблица>_<столбец>_idx. Список индексов таблицы

\d airports

В списке появится строка "airports_airport_name_idx" btree (airport_name). Удаление индекса — команда DROP INDEX

DROP INDEX airports_airport_name_idx

Полный список индексов текущей базы выводится командой утилиты psql

\di

Измерение эффекта индекса. Для оценки эффекта индекса на реальном запросе в psql включается встроенный секундомер

\timing on

После каждой команды выводится время её выполнения. Точное имя пассажира зависит от редакции базы, поэтому сперва подбирается существующее имя, встречающееся достаточно часто

SELECT passenger_name
FROM tickets
GROUP BY passenger_name
HAVING count(*) > 50
LIMIT 1

Полученное имя подставляется в запрос по столбцу passenger_name, по которому индекс ещё не построен (в примере ниже стоит 'IVAN IVANOV', в актуальной базе его нужно заменить на реально встречающееся значение)

SELECT count(*)
FROM tickets
WHERE passenger_name = 'IVAN IVANOV'

Время выполнения фиксируется, после чего создаётся индекс по столбцу

CREATE INDEX tickets_passenger_name_idx
ON tickets (passenger_name)

При повторении того же SELECT время сократится на порядок-другой. Вместо полного перебора таблицы tickets (в зависимости от редакции — сотни тысяч или миллионы строк) СУБД теперь ищет в упорядоченном индексе

Прим. Абсолютные значения времени на конкретной машине будут отличаться от приведённых в учебных материалах и зависят от оборудования, версии PostgreSQL и состояния кеша. Сравнивать имеет смысл относительные цифры — во сколько раз один запрос быстрее другого

Фактический способ выполнения запроса — полный перебор или поиск по индексу — показывает команда EXPLAIN, выводящая план выполнения

EXPLAIN
SELECT count(*) FROM tickets WHERE passenger_name = 'IVAN IVANOV'

В плане будет либо строка Seq Scan on tickets (последовательный перебор), либо Index Scan using ... on tickets или Bitmap Index Scan (поиск по индексу). Команда EXPLAIN ANALYZE дополнительно выполняет запрос и показывает фактическое время

Созданный индекс удаляется

DROP INDEX tickets_passenger_name_idx

Цена индекса. Индекс ускоряет чтение, но замедляет запись. При каждом INSERT, UPDATE (при изменении индексируемого столбца) и DELETE PostgreSQL поддерживает индекс в актуальном состоянии. Индекс также занимает место на диске, пропорциональное размеру таблицы. Поэтому индексы создаются прицельно — по столбцам, регулярно используемым в WHERE, ORDER BY или условиях соединения

Индекс малополезен при низкой селективности выборки. Если запрос извлекает 50% или больше строк таблицы, полный перебор работает не медленнее поиска по индексу с последующим обращением к половине строк. Максимальный эффект от индекса достигается при выборке малой доли строк — единицы процентов или меньше

Типы индексов

Индексы по нескольким столбцам. Индекс строится по одному столбцу или по нескольким сразу. В последнем случае записи индекса упорядочены сначала по первому столбцу, при совпадении — по второму и далее по списку (лексикографическая сортировка)

CREATE INDEX flights_dep_arr_idx
ON flights (departure_airport, arrival_airport)

Такой индекс эффективен для запросов с условием по первому столбцу (WHERE departure_airport = 'SVO') и по обоим столбцам вместе (WHERE departure_airport = 'SVO' AND arrival_airport = 'LED'). Если запрос фильтрует только по второму столбцу (WHERE arrival_airport = 'LED' без условия на departure_airport), индекс по паре (departure_airport, arrival_airport) не поможет — планировщик выберет последовательный перебор. Отсюда вытекает правило: порядок столбцов в составном индексе важен

Уникальные индексы. Уникальный индекс гарантирует уникальность значений столбца (или комбинации столбцов) и одновременно ускоряет поиск по нему

CREATE UNIQUE INDEX aircrafts_model_uniq
ON aircrafts_tmp (model)

Попытка вставки строки с дублирующим значением приводит к ошибке нарушения ограничения. Специфика NULL: в уникальном индексе NULL считается несовпадающим ни с одним другим значением, в том числе с другим NULL. Две строки с NULL в уникальном столбце не конфликтуют — если требуется обратное поведение, столбец объявляется NOT NULL

Прим. Создание уникального индекса — альтернатива ограничению UNIQUE в CREATE TABLE или ALTER TABLE. Функционально они эквивалентны: за UNIQUE внутри стоит тот же уникальный индекс

Документация с подробным. пояснением и примерами: PostgreSQL docs — Indexes, Multicolumn Indexes

Задание

1. На таблице ticket_flights выполните SELECT count(*) FROM ticket_flights WHERE fare_conditions = 'Business' несколько раз, замерив среднее время. Создайте индекс по fare_conditions и повторите эксперимент.

Прим. После завершения экспериментов пользовательские индексы удаляются командой DROP INDEX имя_индекса. Временные таблицы пропадают сами при отключении от базы, а созданные на постоянных таблицах индексы остаются и влияют на выполнение последующих работ. Список пользовательских индексов выводится командой \di

Дополнительно можно потренироваться (по желанию):

• DML-этап sql-ex.ru — задачи на INSERT, UPDATE, DELETE и MERGE с автоматической проверкой. Выполняются на учебных базах sql-ex.ru (корабли, компьютерная фирма, переработка вторсырья), а не на схеме bookings
• pgexercises.com — раздел Updates (INSERT, UPDATE, DELETE) на учебной базе PostgreSQL, в свободном доступе, без регистрации