Когда мы попадаем в индекс, желательно понимать, что при этом происходит. В Postgres btree-индекс – это дерево распределительных узлов, и на последнем уровне хранятся пары: значение -> номер страницы (блока). Поэтому даже если вы попали в индекс, предстоит некоторая работа: проверить, есть ли такой блок в буферном кэше, если нет – подгрузить его с диска. Затем пройтись по всем строкам блока (линейным перебором) и найти ту, что подходит условию.

В редких случаях можно срезать угол: взять данные только из индекса, не обращаясь к диску.

Если выполнить такой запрос:

select id from users where id = 123456

, то значение id будет взято из индекса – нет смысла обращаться в таблицу. Конечно, это наивный пример: мы и так знаем айдишку. Но он полезен для диапазонов: скажем, выбрать айдишки между 100 и 200 или <= 10000. В этом случае они будут взяты из индекса.

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

create table users (
    id integer primary key,
    email text not null
);

Уникальность гарантирует специальный индекс:

create unique index idx_users_email
on users (email);

На практике индекс строят по выражению trim(lower(email)), но сейчас это не важно.

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

insert into users
select x, format('email_%s@test.com', x)
from generate_series(1, 1000000) as seq(x);

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

select id from users
where email = 'email_500000@test.com';
-- 500000

Он довольно шустрый:

┌──────────────────────────────────────────
│ Index Scan using idx_users_email on users
│   Index Cond: (email = 'email_500000@test
│ Planning Time: 0.155 ms
│ Execution Time: 0.896 ms
└──────────────────────────────────────────

Теперь удалим этот индекс и построим немного другой:

drop index idx_users_email;

create unique index idx_users_email
on users (email) include (id);

Такой индекс называется покрывающим из-за выражения include (id). Смысл в том, что значение id хранится в дереве btree как метаданные. Это увеличивает размер индекса, зато позволяет взять указанное поле без обращения к диску. Вот что показывает новый план:

┌───────────────────────────────────────────────
│ Index Only Scan using idx_users_email on users
│   Index Cond: (email = 'email_500000@test.com'
│   Heap Fetches: 0
│ Planning Time: 0.830 ms
│ Execution Time: 0.141 ms
└───────────────────────────────────────────────

Обратите внимание на “Index Only Scan” – только индекс без обращения к диску. Время выполнения ниже, стоимость тоже.

Если прогреть индекс idx_users_email (см. прошлый совет), он будет находиться в памяти. В этом случае резолв айдишки по почте не отличается от Redis: все происходит в памяти без обращения к диску.

Прием тем эффективней, чем больше столбцов и записей в таблице. Не обязательно пихать его тут и там, но в нужный момент он очень полезен.

← Совет дня №23

Совет дня №25 →