Перечитав прошлый совет, я понял, что он нуждается в технической демке. Быстренько покажу, на что влияет индекс внешнего ключа.

Уточнение: будет много планов. Поскольку они длинные, их трудно записать в Телеграме в понятном виде. Здесь только фрагменты, а в конце – ссылка на гист.

Итак, делаем две таблицы, пользователей и профили:

create table users (
    id serial primary key
);

create table profiles (
    id serial primary key,
    user_id integer not null references users(id)
);

Вставим миллион пользователей:

insert into users
select x
from generate_series(1, 999999) as seq(x);

Профилей тоже миллион, но есть нюанс. Я не хочу, чтобы n-ая строка профиля ссылалась на n-ую строку пользователя. Это слишком хорошо и не отражает действительности. Поэтому первый профиль ссылается на последнего пользователя, второй – на предпоследнего и так далее. Другими словами, максимально усложним базе жизнь в плане соединений:

insert into profiles
select x, 1000000 - x
from generate_series(1, 999999) as seq(x);

Проверим, что в таблицах:

table users limit 100;

┌─────┐
│ id  │
├─────┤
│   1 │
│   2 │
│   3 │
│   4 │
│   5 │
│   6 │
│   7 │
│   8 │
│   9 │
│  10 │

table profiles limit 100;

┌─────┬─────────┐
│ id  │ user_id │
├─────┼─────────┤
│   1 │  999999 │
│   2 │  999998 │
│   3 │  999997 │
│   4 │  999996 │
│   5 │  999995 │
│   6 │  999994 │
│   7 │  999993 │
│   8 │  999992 │
│   9 │  999991 │

Индекса на user_id пока что нет. Что будет, если выбрать профили ста определенных пользователей? Посмотрим план:

explain
select * from profiles
where user_id between 450000 and 450100;

Gather  (cost=1000.00..11684.39 rows=94 width=8)
Workers Planned: 2
Parallel Seq Scan on profiles
  (cost=0.00..10674.99 rows=39 width=8)
Filter: ((user_id >= 450000) AND (user_id <= 450100))

Видим, что было полное сканирование профилей. При этом Postgres сделал это параллельно в два процесса (Parallel Seq Scan, Workers =2), а потом объединил результат (шаг Gather). Итоговая стоимость довольно высокая (11684).

Предположим, я делаю отчет по всем пользователям и профилям: тупо соединяю две таблицы джоином:

explain
select *
from users u, profiles p
where u.id = p.user_id;

Hash Join  (cost=30831.98..59602.98 rows=999999 width=12)
Hash Cond: (p.user_id = u.id)
Seq Scan on profiles p
  (cost=0.00..14424.99 rows=999999 width=8)
Hash  (cost=14424.99..14424.99 rows=999999 width=4)
Seq Scan on users u
   (cost=0.00..14424.99 rows=999999 width=4)

То, что был seq scan по таблице users, это нормально – мы выбираем все записи. Однако по таблице profiles тоже был full scan, при этом стоимость хэширования огромна (cost=14424.99..14424.99). Финальный обход хэшей тоже дорогой: 59602.98

Другой пример: предположим, мы выбрали малую часть пользователей и нужно присоединить им профили. Для этого рассмотрим запрос:

explain
with some_users as (
    select * from users
    where id between 450000 and 450100
)
select *
from some_users sm
join profiles p on sm.id = p.user_id;

Считаем, что выборка some_users даст всего 100 пользователей. В реальности мы бы фильтровали по городу или другому признаку, но поскольку у нас только id, остается по нему.

Gather  (cost=1007.61..10702.82 rows=98 width=12)
Workers Planned: 2
Hash Join  (cost=7.61..9693.02 rows=41 width=12)
Hash Cond: (p.user_id = users.id)
Parallel Seq Scan on profiles p
  (cost=0.00..8591.66 rows=416666 width=8)
Hash  (cost=6.39..6.39 rows=98 width=4)
Index Only Scan using users_pkey on users
  (cost=0.42..6.39 rows=98 width=4)
Index Cond: ((id >= 450000) AND (id <= 450100))

Этот план говорит о следующем: выборка some_users сработала моментально, но дальше все плохо. Чтобы найти профили, Postgres опять зарядил два параллельных потока с объединением. Итоговая стоимость (10702.82) высокая.

Добавим индекс на поле user_id и посмотрим, как все изменится:

create unique index idx_profiles_user_id_u
on profiles using btree (user_id);

analyze profiles;

Простой пример: найти профили по диапазону пользователей:

explain
select * from profiles
where user_id between 450000 and 450100;

Index Scan using idx_profiles_user_id_u on profiles
  (cost=0.42..10.37 rows=97 width=8)
Index Cond: ((user_id >= 450000) AND (user_id <= 450100))

Очевидно, стоимость копеечная.

Далее: имея узкую выборку пользователей (например, по городу), присоединить им профили:

explain
with some_users as (
    select * from users
    where id between 450000 and 450100
)
select *
from some_users sm
join profiles p on sm.id = p.user_id;

Nested Loop
  (cost=0.85..825.75 rows=98 width=12)
Index Only Scan using users_pkey on users
  (cost=0.42..6.39 rows=98 width=4)
Index Cond: ((id >= 450000) AND (id <= 450100))
Index Scan using idx_profiles_user_id_u on profiles p  (cost=0.42..8.36 rows=1 width=8)
Index Cond: (user_id = users.id)

Было два прохода по индексам, итоговые данные соединены вложенным циклом (Nested Loop). Сложность такого соединения квадратична, но на малых объемах выигрывает. Стоимость 825 – низкая.

И наконец, узрите истинную мощь Merge Join – соединения слиянием. Повторим запрос, в котором мы соединяем две таблицы без каких-либо фильтров:

explain
select *
from users u, profiles p
where u.id = p.user_id;

Вы, наверное, думаете, что все полетит? Нет, план останется таким же, то есть очень медленным:

Hash Join
  (cost=30831.98..59602.98 rows=999999 width=12)
...

Будет два seq scan-а по обеим таблицам и ручное хеширование. Напомню, я специально создал худший сценарий, когда порядок профилей обратный: первый профиль ссылается на последнего пользователя и так далее. Это делает хэширование дорогим. Стоимость первой строки равна 30831 – это значит, что перед получением данных база будет тупить – считать хэши.

Сделаем вот что: в запросе выше заменим profiles на подзапрос, отсортированный по user_id:

explain
select *
from users u, (select * from profiles order by user_id) p
where u.id = p.user_id
order by u.id;

План:

Merge Join  (cost=5.14..81384.50 rows=999999 width=12)
Merge Cond: (profiles.user_id = u.id)
Index Scan using idx_profiles_user_id_u on profiles  (cost=0.42..30408.41 rows=999999 width=8)
Index Only Scan using users_pkey on users u  (cost=0.42..25980.41 rows=999999 width=4)

Оба набора упорядочены по тем полям, по которым их соединяют, поэтому сработает соединение слиянием. Стоимость его гораздо ниже, чем у хеширования. Первая строка доступна уже через 5.14 единиц стоимости – а было 30831.98. Четыре порядка! Когда я вижу в плане Merge Join, все мои железы активируются и выделяют жидкости.

Планы – полезная вещь. Читайте их, экспериментируйте, пытайтесь понять, почему план именно такой, а не другой.

Ссылка на код.

← Совет дня №35

Совет дня №37 →