Глава 4. Индексирование JSON
Главы
- Введение в документы
- Базовые возможности JSON
- JSON в таблицах
- Индексирование JSON
- Ссылки и ограничения в документах
- Язык путей JSONPath
- Отчеты и функции
- Функции на языке Python
- Версионирование и архивация документов
- Релевантный поиск
Содержание
- Главы
- Знакомство с EXPLAIN
- Простые Btree-индексы
- Еще раз о UUID v7
- Индексирование полей JSON
- Индексирование дат
- Коротко о hash-индексе
- Статистика индексов
- Составные индексы
- Поиск по шаблону
- Триграммный поиск по многим полям
- Коротко о ранжировании
- Регулярные выражения
- Индексирование документа целиком
В прошлой главе мы опробовали поиск документов по разным полям. На тот момент нас интересовало, можно ли это делать в принципе. Мы упомянули, что отложим вопрос быстродействия на потом, когда будем разбираться с индексами. Этим мы и займемся в этой главе: узнаем, как индексировать документы по отдельным полям и целиком, какие бывают индексы и многое другое.
Слово “индекс” в переводе латинского означает “указатель”. Индексы широко используются в программировании, книжном деле, документации и других сферах. В широком смысле задача индекса в том, чтобы свести долгий поиск к быстрому. Это достигается за счет небольшого документа, который вместо данных хранит позицию данных в каком-то массиве. Получив на вход критерий поиска, индекс направляет нас туда, где содержится полная информация.
Пример индекса в реальной жизни – оглавление книги или предметный указатель. По номеру главы или термину мы переходим на нужную страницу. Такой поиск тоже требует перебора: когда мы ищем главу, то скользим взглядом по оглавлению – обходим его. Однако поиск в оглавлении на порядок быстрее, чем просмотр всей книги.
В прежние времена, когда выпускали серии энциклопедий, к ним прилагалась отдельная книга-индекс. Она целиком состояла из терминов в алфавитном порядке, а напротив каждого указывали том, главу и страницу. Также подобные индексы составляли вручную для фамильных библиотек.
Индексы встречаются в коде на языках вроде Java или Python. Предположим, есть
список пользователей, и нужно искать их по номеру. Чтобы не сканировать список
каждый раз, строится словарь вида id->user, и тогда пользователя можно извлечь
за константное время. Чтобы построить такой словарь, потребуется один
обход. Приведем код на Python:
users = [
{"id": 101, "name": "Ivan", "age": 14},
{"id": 202, "name": "John", "age": 34},
{"id": 303, "name": "Juan", "age": 51},
]
id_to_user = {
user["id"]: user for user in users
}
user = id_to_user[202]
print(user)
# {'id': 202, 'name': 'John', 'age': 34}
Подобные словари называются индексными или просто индексами. Выше индекс
id_to_user сопоставляет номер пользователя и объект. Его можно упростить:
вместо самого пользователя хранить его позицию в списке. По id мы получим
позицию, а по ней – словарь пользователя:
id_to_index = {
user["id"]: i for i, user in enumerate(users)
}
index = id_to_index[202] # 1
user = users[index]
print(user)
# {'id': 202, 'name': 'John', 'age': 34}
Аналогично строятся индексы по другим полям, например имени или электронной
почте. В отличие от ID, эти поля не уникальны, поэтому в значениях словаря будет
не один элемент, а их список. Ниже индекс name_to_ids показывает, что в
системе три пользователя по имени John и кодами 202, 345 и 582.
name_to_ids = {
'Ivan': [101],
'John': [202, 345, 582],
}
Для баз данных индексы особенно важны. Если искать записи линейным обходом, с ростом таблицы это займет все больше и больше времени. А задача базы в обратном: обеспечить быструю выборку даже на огромных данных.
По умолчанию выборка строк устроена линейно. Алгоритм перебирает записи по одной и проверяет, подходит ли она условию WHERE. Если да, запись попадает в итоговую выборку. Чем больше таблица, тем дольше выполняется обход. Ситуация, когда когда запрос проверяет все записи, называется full sequence scan или, на жаргоне специалистов по базам данных, “фул-скан”.
В единичных “фул-сканах” нет ничего страшного. Если это сложный отчет или сверка расчетов, другого выбора у нас нет. Но если каждый запрос порождает сканирование таблицы и таких запросов много, база замедляется.
Чтобы ускорить поиск в таблице, используют индексы. Реляционные базы чаще всего полагаются на индексы B-tree. Это структура данных, которая связывает значение с номером страницы, где находится строка. Чаще всего значение – колонка, однако может быть и сложным выражением: конкатенацией полей, вызовом функции и так далее.
Индекс B-tree устроен как дерево узлов; в свою очередь узлы делятся на уровни. Если это не самый нижний уровень, узлы хранят значения и ссылки на нижележащие узлы. Нижний уровень содержит ссылки на страницы таблицы. На схеме ниже показан типичный индекс B-tree:
┌──────────────────────────────────────────────────────────┐
│ ┌───────────┐ │
│ │ Metadata │ │
│ └─────┬─────┘ │
│ │ │
│ values ┌─────▼─────┐ │
│ lvl 1 ┌──────────│ 4 32 64 ├──────────┐ │
│ │ └─────┬─────┘ │ │
│ │ │ │ │
│ values ┌─────▼─────┐ ┌─────▼─────┐ ┌─────▼─────┐ │
│ lvl 2 │ 4 16 25 ◀────▶ 32 43 49 ◀────▶ 64 79 89 │ │
│ └──┬──┬──┬──┘ └──┬──┬──┬──┘ └──┬──┬──┬──┘ │
│ │ │ │ │ │ │ │ │ │ │
│ ┌──▼──▼──▼──┐ ┌──▼──▼──▼──┐ ┌──▼──▼──▼──┐ │
│ pages │104 523 662│ │223 303 315│ │662 824 908│ │
│ └───────────┘ └───────────┘ └───────────┘ │
└──────────────────────────────────────────────────────────┘
Если требуется найти в таблице строку со значением 25, происходит следующее. Сперва мы оказываемся на первом уровне индекса. Для простоты он содержит всего три значения: 4, 32 и 64, но на практике их гораздо больше. Промышленный индекс содержит два в степени N элементов в одном узле, например 256 или 512, что делает невозможным его реалистичное изображение.
Элементы каждого узла упорядочены. Двоичным поиском находится позиция элемента, за которым следует искомое число. В нашем случае 25 следует за 4; в свою очередь 4 ссылается на левый узел второго уровня. Переходим на этот узел и повторяем алгоритм. Поскольку второй уровень – последний, это гарантирует, что 25 содержится в узле. Так и есть: это третий элемент, и его позицию тоже находят двоичным поиском.
Числу 25 сопоставлен номер страницы, где находится искомая строка – 662. Зная номер страницы, легко найти ее позицию в файле: нужно умножить номер (662) на 8192 (размер страницы в байтах), что даст смещение в 5.423.104 байта. Далее Postgres читает из файла 8 килобайт с найденным смещением – это и есть страница 662.
Страница помещается в буферный кэш; в среднем она содержит 25-100 строк. Чтобы найти среди них нужную, Postgres действует перебором: берет строки одну за другой и проверяет, что определенное поле равно 25. Поскольку строки уже в памяти, обращение к диску не требуется, и перебор происходит быстро.
Это было упрощенное описание Btree-индекса в Postgres; на практике он учитывает множество деталей. Так, узлы одного уровня ссылаются на соседей (горизонтальные стрелки на рисунке выше), чтобы ускорить переход от одного узла к другому в рамках уровня. Узлы индекса заполняются не полностью, а примерно на 90%, чтобы перестроение дерева случалось реже. Вместе со ссылкой на страницу индекс может хранить дополнительные поля; этот трюк используют для того, чтобы читать данные из индекса без обращения к таблице.
В интернете доступны приложения на Javascript, которые визуализируют устройство Btree. Можно указать размер узла, порог разбиения и другие начальные факторы, после чего добавлять и удалять элементы. При этом на холсте показано дерево узлов с плавными анимациями. Одно из таких приложений доступно по адресу btree.app; предлагаем читателю поработать с ним, чтобы лучше понять устройство Btree.
Индексы бывают разных структур и топологий: кроме btree, Postgres поддерживает hash-индексы, семейство GIN и GiST для типов-коллекций, BRIN для чрезвычайно больших таблиц. Расширение PostGis предлагает пространственные индексы для поиска на плоскости и в пространстве, индексы заполняющей кривой и так далее. Некоторые из них мы рассмотрим в этой и следующих главах. Заинтересованным читателям еще раз порекомендуем книги и документацию компании Postgres Pro, где индексы описаны во всех подробностях.
Знакомство с EXPLAIN
Вспомнив теорию, вернемся к нашему миллиону заявок. Когда мы объявили таблицу
applications, для первичного ключа автоматически создался индекс. Каждый раз,
когда мы ищем документ по полю id, база обращается к нему. Это легко проверить,
предварив запрос командой EXPLAIN. Вот что вернет следующий запрос:
explain
select id, doc from applications
where id = '00000000-0000-0000-0000-000000411999';
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using applications_pkey on applications (cost=0.42..8.44 rows=1 width=1729) │
│ Index Cond: (id = '00000000-0000-0000-0000-000000411999'::uuid) │
└─────────────────────────────────────────────────────────────────────────────────────────┘
В полученном отчете нас интересует, было ли попадание в индекс. Выражение Index Scan подтверждает это. Выборка документа будет быстрой – не более нескольких миллисекунд независимо от того, где физически находится документ: в начале, конце или середине таблицы.
Мы применили команду EXPLAIN, чтобы узнать, как именно выполнялся запрос. По ходу главы мы будем часто ей пользоваться, поэтому коротко обсудим, что именно делает команда и как читать ее вывод.
Если запрос начинается с EXPLAIN, мы получим не данные, которые он бы вернул при обычных обстоятельствах, а план выполнения. План описывает стратегию, которую выбрал сервер, чтобы выполнить запрос. Ключевые его элементы – это таблицы, метод доступа к ним (полное сканирование или при помощи индекса), тип соединения в случае JOIN и многое другое.
План имеет вложенную структуру и в целом повторяет иерархию запроса. Например, если мы выбираем данные из двух подзапросов, в плане будут отдельные узлы для каждого из них, а выше – узел объединения результатов. У каждого узла свои промежуточные показатели; узел выше по иерархии накапливает показатели своих потомков. Приложения вроде PGAdmin показывают план графически.
У команды EXPLAIN могут быть параметры, которые влияют на то, какую информацию мы получим. Чаще всего встречается ANALYZE – с ним команда не только строит план, но и выполняет сам запрос. Без ANALYZE все показатели носят оценочный характер, а с ним – фактический. Мы получим затраты на выполнение каждого узла, количество прочитанных страниц, попадание в буферных кэш и многое другое, в том числе время, потраченное на построение плана и его выполнение.
На тему того, как правильно читать план, написаны многие статьи. Поскольку наша книга посвящена работе с JSON, мы не будем рассматривать EXPLAIN во всех деталях. Перечислим только те моменты, на которых будем заострять внимание. Заинтересованным читателям рекомендуем книги “PostgreSQL 16. Оптимизация запросов” и “PostgreSQL. Профессиональный SQL”, где команда EXPLAIN описана подробно.
Итак, в выводе EXPLAIN ANALYZE нас интересует следующее. Прежде всего это показатель cost, который выражен двумя числами через две точки. В примере выше это cost=0.42..8.44. Cost, что по-английски “стоимость”, описывает затраты на выполнение запроса в абстрактных единицах. Это не секунды и не мегабайты памяти, а условная валюта, в которой сервер оценивает усилия.
Первое число cost означает затраты до момента, пока не поступит первая строка результата. Второе – затраты на получение всех строк. Важно понимать эту разницу. Если мы выберем миллион приложений из базы, второй cost будет высоким, потому что на передачу всех документов уйдет время. Однако первый cost будет мал, потому что отдача данных начнется сразу же. Покажем это на примере:
explain
select id, doc from applications;
┌─────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on applications (cost=0.00..359524.33 rows=965633 width=1729) │
└─────────────────────────────────────────────────────────────────────────┘
Если добавить агрегацию или сортировку по полю без индекса, это усложнит запрос: перед тем как отдать первую строку, сервер должен обработать их все. В результате первый cost вырастет катастрофически:
explain
select id, doc from applications
order by (doc->>'application_id')::int
┌──────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────┤
│ Gather Merge (cost=1000457.74..1094345.26 rows=804694 width=1733) │
│ Workers Planned: 2 │
│ -> Sort (cost=999457.72..1000463.59 rows=402347 width=1733) │
│ Sort Key: (((doc ->> 'application_id'::text))::integer) │
│ -> Parallel Seq Scan on applications (cost=0.00..356909.07 rows=402347 width=1733) │
└──────────────────────────────────────────────────────────────────────────────────────────────┘
В идеале запросы должны иметь как можно меньшие показатели cost – как первого, так и второго. Не всегда это возможно: порой не хватает времени и бюджета. Иной раз оптимизация cost не имеет смысла: если отчет выполняется ночью, его стоимость может быть крайне высокой. Однако множество мелких, но неоптимальных запросов могут замедлить базу. В этой главе мы делаем акцент на втором случае: рассмотрим поиск документов по разным критериям, полагая, что много таких запросов исходит от потребителей.
Параметр ANALYZE добавляет в план новый критерий: фактическое время запроса. Это строка Execution Time; в примере ниже она показывает 3.9 миллисекунды:
explain analyze
select id, doc from applications
where id = '00000000-0000-0000-0000-000000411999';
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using applications_pkey on applications (cost=0.42..8.44 rows=1 width=1830) (actual time=3.786..3.789 rows=1 loops=1) │
│ Index Cond: (id = '00000000-0000-0000-0000-000000411999'::uuid) │
│ Planning Time: 0.179 ms │
│ Execution Time: 3.906 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Чем меньше этот показатель, тем лучше. Трудно обозначить порог, выше которого запрос считается медленным – это зависит от его природы и требований. В идеале запросы вида get-by-id (штучная запись по ключу) укладываются в несколько десятков миллисекунд (10-20), а выборка разумного числа записей по индексу – в несколько сотен (100-300). Однако эти числа условны и могут быть пересмотрены.
У сервера Postgresql есть особенность, которую, возможно, вы замечали. Если выполнить запрос дважды, во второй раз он сработает быстрее. Разница значительна: иной раз она составляет порядок, например: сначала 3 секунды, а затем 300 миллисекунд.
Так происходит потому, что сервер кэширует данные с диска во внутренних буферах. Первый запрос провоцирует чтение с диска и перенос данных в кэш. Второй запрос находит данные в памяти и тем самым выигрывает время. Подобные запросы называют холодными и горячими; также говорят, что первый запрос прогревает буферный кэш.
Выражение EXPLAIN BUFFERS добавляет в план данные о том, сколько страниц было
прочитано с диска, а сколько – из внутреннего кэша. Выполним один и тот же
запрос дважды и сравним планы:
explain (analyze, buffers)
select id, doc from applications
where id = '00000000-0000-0000-0000-000000411999';
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using applications_pkey on applications (cost=0.42..8.44 rows=1 width=1830) (actual time=2.820..2.824 rows=1 loops=1) │
│ Index Cond: (id = '00000000-0000-0000-0000-000000411999'::uuid) │
│ Buffers: shared read=4 │
│ Planning Time: 0.750 ms │
│ Execution Time: 2.957 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using applications_pkey on applications (cost=0.42..8.44 rows=1 width=1830) (actual time=0.054..0.055 rows=1 loops=1) │
│ Index Cond: (id = '00000000-0000-0000-0000-000000411999'::uuid) │
│ Buffers: shared hit=4 │
│ Planning Time: 0.105 ms │
│ Execution Time: 0.070 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Отличие в строках shared read=4 и shared hit=4. Слово “read” означает “прочитано” (с диска), а “hit” (“попадание”) – удачный поиск в буферном кэше. Обратите внимание, что второй запрос отработал в 40 раз быстрее – вот какое ускорение дает буферный кэш.
Отсюда вывод: даже медленный запрос ускорится, если прогреть его несколькими запусками. Порой это вводит разработчика в заблуждение. Он рассуждает так: хотя в первый раз запрос был медленным, на боевом сервере он выполняется часто и, следовательно, будет прогрет. Его среднее время будет как во втором и последующих запусках.
Увы, это не так: сервер выполняет множество запросов одновременно, и содержимое буферов постоянно меняется. Даже если один и тот же запрос выполняется часто, у него могут быть разные параметры. Это, в свою очередь, вынудит базу обращаться к разным областям диска, читать другие страницы и замещать данные в кэше. Поэтому вызовы одного и того же запроса могут быть холодными.
Наш совет в том, чтобы оценивать запрос по первому запуску, не полагаясь на второй. К сожалению, мы не можем явно скомандовать Postgres: очисти все буферы (под “явно” имеется в виду без низкоуровневых средств операционной системы). Если разработка ведется локально, перезагрузите сервер, и буферы очистятся. Если это удаленный сервер, переключитесь на отладку других запросов, и со временем они вытеснят текущие данные из кэша.
Вместо очистки Postgres предлагает противоположное действие: принудительно наполнить буферный кэш данными из таблицы или индекса. Их последующее чтение будет горячим. Технику называют прогревом (по-английски prewarm); для работы с ней служит расширение pg_prewarm, которое мы рассмотрим по ходу книги.
Еще один важный показатель EXPLAIN – метод доступа к таблице. Когда в запросе не участвует индекс, метод равен “Full Scan” – полное сканирование. Этих случаев мы стараемся избежать. Выражение Index Scan означает, что обход совершен с помощью индекса. При этом указано его имя и показатели, например число прочитанных страниц.
Сканирование индекса тоже бывает разным: кроме обычного “Index Scan” встречается
“Index Only Scan”. Этот метод означает, что данные взяты из индекса без
обращения к диску. Метод Bitmap Index Scan подразумевает два действия: в первый
раз строится битовая карта страниц, которые подходят условию. Битовая карта –
это массив, где бит с номером N означает какой-то признак, например подходит
запись условию или нет. Карт может быть несколько: для условия where field1 =
'abc' and field2 = 'xyz' могут быть построены две отдельных карты. Далее они
объединяются битовым AND, и по итоговой карте обходится основная таблица.
Факт попадания в индекс в целом положительный, но не решающий. Ниже мы рассмотрим случаи, когда даже с индексом запрос недостаточно быстрый. На то, брать во внимание индекс или нет, влияют многие факторы, в том числе объем данных и их разнообразие. Именно поэтому мы уделили внимание правдоподобности данных при генерации. Иначе окажется, что запрос, оптимизированный под тестовые данные, работает медленно на боевом сервере и наоборот.
Мы поверхностно рассмотрели вывод EXPLAIN и что нас в нем интересует. Вернемся к главной теме главы – работе с индексами.
Простые Btree-индексы
Итак, у нас есть индекс для первичного ключа, чтобы искать документы по полю
id. Другой частый случай – вывести документы дате создания или за период:
прошлую неделю или квартал. Для этого нужен индекс btree по полю
created_at. Вот как его создать:
create index if not exists idx_applications_created_at
on applications
using btree (created_at desc);
Сразу после создания выполните команду ANALYZE с именем таблицы:
analyze applications;
Эта команда обновляет статистику: строит гистограмму (распределение) значений и выделяет те, что встречаются часто. Статистика крайне важна для построения плана. Иногда запрос не использует индекс, потому что данные изменились (был крупный UPDATE), а статистика все еще содержит старые показатели. Postgres обновляет статистику по расписанию, но мы вызвали команду явно. Предполагается, что после каждого действия над индексом вы тоже вызываете ANALYZE.
Убедимся, что индекс работает. Для этого выберем 100 последних документов и проверим план. Выражение Index Scan показывает, что мы попали в индекс:
explain
select id, doc from applications
order by created_at desc
limit 100;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.42..37.19 rows=100 width=1736) │
│ -> Index Scan using idx_applications_created_at on applications (cost=0.42..368293.21 rows=1001719 width=1736) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Вариант с диапазоном: ограничим время создания документа каким-то днем. Для этого служит оператор between, где левое и правое значения – границы:
explain
select id, doc from applications
where created_at between '2025-07-30 00:00:00Z' and '2025-07-30 23:59:59Z'
order by created_at desc;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_created_at on applications (cost=0.42..10918.55 rows=2711 width=1738) │
│ Index Cond: ((created_at >= '2025-07-30 03:00:00+03'::timestamp with time zone) AND (created_at <= '2025-07-31 02:59:59+03'::timestamp with time zone)) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Мы задали начало и конец дня вручную. Вот как получить оба значения программно, зная дату:
('2025-07-30'::timestamptz)
('2025-07-30'::timestamptz + interval '1 day' - interval '1 second')
В первом случае мы приводим дату к типу timestamptz, во втором – прибавляем
день и вычитаем секунду.
Обратите внимание, что мы создали индекс в режиме по убыванию (слово desc после created_at). Мы выбрали этот способ, потому что чаще всего в работе нужны последние документы. Это упрощает работу сервера: если направления индекса и сортировки запроса совпадают (оба asc или оба desc), индекс обходится в прямом порядке. Если нет, то с конца.
При создании индекса мы указали тип btree. Этого можно было не делать, потому что btree является типом по умолчанию; все же мы оставили его для ясности. Postgresql поддерживает многие виды индексов, каждый со своими особенностями. При создании индекса важно понимать, почему выбран тот или иной тип и какие возможности он предлагает.
Как мы упоминали, индекс btree устроен как сбалансированное дерево узлов. Элементы добавляются в узлы так, чтобы при обходе индекса он выглядит как упорядоченный список. Ключевая особенность btree в том, что он поддерживает операторы <, <=, =, =>, > (для краткости – больше, меньше и равно). Такой индекс находит точное значение, а также значения меньше или больше указанного.
Если для поля создан индекс btree, вот какие возможности он дает в запросах:
- поиск по точному значению (оператор =);
- сортировка по возрастанию и убыванию (
order by ... asc/desc); - поиск по условию “меньше” и “больше” (<, <=, >, =>);
- отбор по диапазону (оператор between).
Во всех случаях мы попадем в индекс, если, конечно, нет причин, которые этому препятствуют.
Индекс btree идеально подходит числам и датам. Обобщая, можно сказать, что btree работает с любым типом, который поддерживает равенство и сравнение, в том числе text или jsonb. Однако для этих типов будет правильным использовать другие индексы, и ниже мы рассмотрим их.
Еще раз о UUID v7
На текущий момент в таблице заявок индексы для полей id и created_at. Пока мы не ушли дальше, давайте вспомним про UUID версии 7. Ниже мы рассмотрим, как с его помощью совместить эти индексы в одном.
Первый индекс по id служит для поиска документов по ключу. Предполагается, что ключ нам известен, и мы получаем по нему документ. Для краткости такие запросы называют “get by id”. При этом сортировка документов по ключу не имеет смысла: каждый из них случаен, поэтому строки идут вразнобой.
Во время генерации данных мы произвели ключи так, чтобы они отталкивались от счетчика, например:
00000000-0000-0000-0000-000000411997
00000000-0000-0000-0000-000000411998
00000000-0000-0000-0000-000000411999
Это сделано исключительно для экспериментов. В промышленном запуске ключи случайны и выглядят примерно так:
f1f0a631-a393-47d6-be8d-e082222a9b9a
d108a560-98a4-4c6a-b63e-d4d4f90cc3f7
b5efc9a5-8d27-44e6-a5d8-d81c519820fa
Поскольку алгоритм UUID v4 случаен, ключ не связан с датой его производства. Если упорядочить документы по первичному ключу, сегодняшний документ окажется в середине выборки, прошлогодний – первым, вчерашний – ближе к концу и так далее. Поиск по диапазону ключей тоже бесполезен, поскольку не согласован со временем.
Индекс по created_at, напротив, дает осмысленный порядок документов. Он не позволит найти конкретный документ, потому в один момент могут быть созданы несколько документов. Однако легко выбрать документы за период и отсортировать их.
Хорошая новость в том, что UUID версии 7 совмещает обе возможности. Это по-прежнему уникальный идентификатор, который указывает на конкретный документ. А поскольку первые его 48 бит – время (число миллисекунд эпохи Unix), идентификатор поддается сортировке: каждый следующий больше предыдущего.
Схематично UUID v7 представлен ниже. Его лидирующая часть – число микросекунд эпохи Unix. Автор произвел три идентификатора, и временная метка каждый раз увеличивалась.
┌─────────────────────┬───────┬───────────────┐
│Unix timestamp, ms │Seq │Random part │
├─────────────────────┼───────┼───────────────┤
│1778344072736 │1 │6AC83E922 │
├─────────────────────┼───────┼───────────────┤
│1778344106125 │3 │7C4C36B36 │
├─────────────────────┼───────┼───────────────┤
│1778344112067 │2 │7A42AC302 │
└─────────────────────┴───────┴───────────────┘
После времени Unix несколько бит выделено под счетчик в рамках одной миллисекунды. Современные компьютеры столь быстры, что производят сотни идентификаторов за одну миллисекунду. Если заполнить оставшиеся биты случайно, теряется упорядоченность – ключевое свойство алгоритма. Чтобы упорядочить идентификаторы, в рамках каждой миллисекунды запускается счетчик. Скажем, если в момент времени 1778344106125 приложение произвело четыре идентификатора, то у последнего в поле seq будет записано число 3 (если считать от нуля). Оставшиеся после счетчика биты заполняются случайно (поле Random part).
Это было приблизительное описание того, как устроен UUID 7. Существует несколько версий алгоритма, каждая из которых по-своему заполняет биты после времени: случайно, счетчиком или даже двумя счетчиками. За технической информацией обратитесь к следующим страницам:
- UUID v7: Time-Ordered UUIDs for Better Database Performance
- UUIDv7 Comes to PostgreSQL 18
- RFC 9562 Universally Unique IDentifiers (UUIDs)
- Postgres Pro: функции генерирования UUID
Имея идентификатор седьмой версии, легко получить из него время и наоборот: из времени произвести идентификатор. С таким подходом индекс created_at оказывается не нужен. Действительно: если отсортировать документы по убыванию ключа, это равносильно сортировке по убыванию даты создания.
Алгоритм UUID v7 появился в Postgres с версии 18, которая вышла осенью 2025 года. Если вы еще не обновились, возможны следующие варианты:
- установите стороннее расширение pg_uuidv7;
- создавайте идентификаторы в приложении силами Python, Java и других языков;
- напишите функцию на встроенном языке Postgres. Примеры и обсуждение доступны в фрагменте gist по ссылке.
Покажем теперь, как искать записи по датам, используя идентификатор. Заявки, что мы произвели в прошлой главе, не подходят для этой цели – их ключи не являются UUID v7. Нам понадобится таблица с двумя полями: ключом и временем.
create table uuid_v7_demo(
id uuid not null primary key,
created_at timestamptz not null
);
Вставим в нее тысячу записей, при этом ключ формируется функцией uuidv7. Важно: код ниже сработает только в Postgres версии 18 и выше.
insert into uuid_v7_demo
select
uuidv7(interval '-1 day' * x),
current_timestamp + interval '-1 day' * x
from
generate_series(1, 9999) as seq(x);
Без параметров функция uuidv7 производит идентификатор с текущим временем. Можно передать интервал (в том числе отрицательный), который играет роль смещения. Запрос производит десять тысяч записей, при этом из created_at вычитается день, два и так далее. То же самое касается ключа: при каждой генерации от текущей метки Unix timestamp отнимается нарастающий интервал. Если выбрать строки по возрастанию ключа, порядок будет согласован с датой создания:
select * from uuid_v7_demo
order by id limit 100;
┌──────────────────────────────────────┬───────────────────────────────┐
│ id │ created_at │
├──────────────────────────────────────┼───────────────────────────────┤
│ 00d4e887-b56f-7584-9190-d522f266d4bf │ 1998-12-23 20:31:14.630735+03 │
│ 00d4edae-116f-757d-a295-7f89e12c8bda │ 1998-12-24 20:31:14.630735+03 │
│ 00d4f2d4-6d6f-7575-b5d0-621727a083ed │ 1998-12-25 20:31:14.630735+03 │
│ 00d4f7fa-c96f-756c-999d-2c22d558596e │ 1998-12-26 20:31:14.630735+03 │
│ 00d4fd21-256f-7567-8158-5acbfc067301 │ 1998-12-27 20:31:14.630735+03 │
│ 00d50247-816f-755d-8377-c12809f0a1c3 │ 1998-12-28 20:31:14.630735+03 │
│ 00d5076d-dd6f-7550-b939-7c10f9f9ce5f │ 1998-12-29 20:31:14.630735+03 │
│ 00d50c94-396f-754d-bb77-9d663b4f31ea │ 1998-12-30 20:31:14.630735+03 │
│ 00d511ba-956f-7544-a16d-e9c7f8613f04 │ 1998-12-31 20:31:14.630735+03 │
│ 00d516e0-f16f-753e-8a0d-1854d65ae69e │ 1999-01-01 20:31:14.630735+03 │
Время из ключа извлекается функцией uuid_extract_timestamp. Видим, что она
совпадает с created_at с точностью до миллисекунд:
select
*,
uuid_extract_timestamp(id) as ts
from uuid_v7_demo
order by id limit 100;
┌──────────────────────────────────────┬───────────────────────────────┬────────────────────────────┐
│ id │ created_at │ ts │
├──────────────────────────────────────┼───────────────────────────────┼────────────────────────────┤
│ 00d4e887-b56f-7584-9190-d522f266d4bf │ 1998-12-23 20:31:14.630735+03 │ 1998-12-23 20:31:14.671+03 │
│ 00d4edae-116f-757d-a295-7f89e12c8bda │ 1998-12-24 20:31:14.630735+03 │ 1998-12-24 20:31:14.671+03 │
│ 00d4f2d4-6d6f-7575-b5d0-621727a083ed │ 1998-12-25 20:31:14.630735+03 │ 1998-12-25 20:31:14.671+03 │
│ 00d4f7fa-c96f-756c-999d-2c22d558596e │ 1998-12-26 20:31:14.630735+03 │ 1998-12-26 20:31:14.671+03 │
│ 00d4fd21-256f-7567-8158-5acbfc067301 │ 1998-12-27 20:31:14.630735+03 │ 1998-12-27 20:31:14.671+03 │
│ 00d50247-816f-755d-8377-c12809f0a1c3 │ 1998-12-28 20:31:14.630735+03 │ 1998-12-28 20:31:14.671+03 │
│ 00d5076d-dd6f-7550-b939-7c10f9f9ce5f │ 1998-12-29 20:31:14.630735+03 │ 1998-12-29 20:31:14.671+03 │
│ 00d50c94-396f-754d-bb77-9d663b4f31ea │ 1998-12-30 20:31:14.630735+03 │ 1998-12-30 20:31:14.671+03 │
│ 00d511ba-956f-7544-a16d-e9c7f8613f04 │ 1998-12-31 20:31:14.630735+03 │ 1998-12-31 20:31:14.671+03 │
│ 00d516e0-f16f-753e-8a0d-1854d65ae69e │ 1999-01-01 20:31:14.630735+03 │ 1999-01-01 20:31:14.671+03 │
│ 00d51c07-4d6f-7536-af14-ebc968cfa7b4 │ 1999-01-02 20:31:14.630735+03 │ 1999-01-02 20:31:14.671+03 │
Предположим, требуется выбрать записи за два месяца назад относительно текущего
дня. Для этого заключите id в оператор between, где левая граница – ключ с
интервалом '-3 months', а второй – '-2 months':
select * from uuid_v7_demo
where id between
uuidv7(interval '-3 months')
and
uuidv7(interval '-2 months')
order by id
limit 100;
┌──────────────────────────────────────┬───────────────────────────────┐
│ id │ created_at │
├──────────────────────────────────────┼───────────────────────────────┤
│ 019c489b-7949-7d87-aa8f-de9eeb8726e4 │ 2026-02-10 20:31:14.630735+03 │
│ 019c4dc1-d549-7d7c-85f6-ba7201c61a63 │ 2026-02-11 20:31:14.630735+03 │
│ 019c52e8-3149-7d76-b205-897059e6d8ff │ 2026-02-12 20:31:14.630735+03 │
│ 019c580e-8d49-7d68-88d7-e846afc2c5b5 │ 2026-02-13 20:31:14.630735+03 │
│ 019c5d34-e949-7d61-8199-7ce0fed26860 │ 2026-02-14 20:31:14.630735+03 │
│ 019c625b-4549-7d5a-971e-fa0b9a827175 │ 2026-02-15 20:31:14.630735+03 │
│ 019c6781-a149-7d4f-a4f3-3f3ea25bcb2e │ 2026-02-16 20:31:14.630735+03 │
│ 019c6ca7-fd49-7d45-9449-ae8b3c2f8a9f │ 2026-02-17 20:31:14.630735+03 │
│ 019c71ce-5949-7d3f-84a5-f11c99d9e3cf │ 2026-02-18 20:31:14.630735+03 │
│ 019c76f4-b549-7d35-821c-f64da5398f11 │ 2026-02-19 20:31:14.630735+03 │
│ 019c7c1b-1149-7d2c-af1e-2b27042b726f │ 2026-02-20 20:31:14.630735+03 │
│ 019c8141-6d49-7d26-8e6e-a9cab44b4b64 │ 2026-02-21 20:31:14.630735+03 │
│ 019c8667-c949-7d1f-b9f5-7cf09ff6c908 │ 2026-02-22 20:31:14.630735+03 │
Если предварить запрос выражением EXPLAIN ANALYZE, увидим попадание в индекс:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.29..54.38 rows=100 width=24) (actual time=16.618..16.744 rows=28.00 loops=1) │
│ Buffers: shared hit=134 │
│ -> Index Scan using uuid_v7_demo_pkey on uuid_v7_demo (cost=0.29..601.26 rows=1111 width=24) (actual time=16.615..16.737 rows=28.00 loops=1) │
│ Filter: ((id >= uuidv7('-3 mons'::interval)) AND (id <= uuidv7('-2 mons'::interval))) │
│ Rows Removed by Filter: 9971 │
│ Index Searches: 1 │
│ Buffers: shared hit=134 │
│ Planning Time: 0.139 ms │
│ Execution Time: 16.767 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Таким образом, UUID v7 позволяет нам упразднить индекс по дате создания. Кому-то это покажется незначительным фактом, но выигрыш все-таки есть. Каждый индекс несет накладные расходы, и общее правило таково: чем меньше индексов – тем лучше. Если вам удалось избавиться хотя бы от одного индекса, это уже достижение.
Автор надеется, что читатель осведомлен о преимуществах UUID v7 и с радостью применит их. Далее по тексту мы будем пользоваться UUID v4 – случайным и неупорядоченным. Цель этого решения в том, чтобы примеры из книги работали со многими версиями Postgres, а не только 18.
Индексирование полей JSON
Перейдем к следующему критерию поиска. У документа есть поле application_id –
внутренний числовой номер. Им активно пользуются сотрудники, потому что
запомнить его проще, чем длинный идентификатор.
select id, (doc->>'application_id') as app_id
from applications
limit 10;
┌──────────────────────────────────────┬────────┐
│ id │ app_id │
├──────────────────────────────────────┼────────┤
│ 00000000-0000-0000-0000-000000009985 │ 9985 │
│ 00000000-0000-0000-0000-000000009986 │ 9986 │
│ 00000000-0000-0000-0000-000000009987 │ 9987 │
│ 00000000-0000-0000-0000-000000009988 │ 9988 │
│ 00000000-0000-0000-0000-000000009989 │ 9989 │
│ 00000000-0000-0000-0000-000000009990 │ 9990 │
│ 00000000-0000-0000-0000-000000009991 │ 9991 │
│ 00000000-0000-0000-0000-000000009992 │ 9992 │
│ 00000000-0000-0000-0000-000000009993 │ 9993 │
│ 00000000-0000-0000-0000-000000009994 │ 9994 │
└──────────────────────────────────────┴────────┘
Требуется искать и сортировать документы по номеру. Для этого объявим индекс btree, где поле приводится к числу:
create index if not exists
idx_applications_application_id
on applications using btree
(((doc->>'application_id')::int));
Проверим операции, доступные индексу btree. Прежде всего это поиск по точному значению:
explain
select id, doc from applications
where ((doc->>'application_id')::int) = 550433;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_application_id on applications (cost=0.42..8.44 rows=1 width=1730) │
│ Index Cond: (((doc ->> 'application_id'::text))::integer = 550433) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
Отбор по диапазону с сортировкой:
explain
select id, doc from applications
where ((doc->>'application_id')::int) between 550000 and 550099
order by ((doc->>'application_id')::int) asc;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_application_id on applications (cost=0.42..286.56 rows=94 width=1734) │
│ Index Cond: ((((doc ->> 'application_id'::text))::integer >= 550000) AND (((doc ->> 'application_id'::text))::integer <= 550099)) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Оба плана содержат строку Index Scan, которая означает, что индекс принял участие в запросе. Время выполнения меньше по сравнению с ситуацией, когда индекса не было.
Заметим, что индекс сработает лишь в том случае, когда выражение WHERE точно ему соответствует. В противном случае Postgres решит, что подходящего индекса нет и выполнит полное сканирование.
Вот как это продемонстрировать: изменим WHERE так, чтобы результат был тем же самым, но синтаксис отличался. Для этого вместо двойной стрелки поставим двойной уголок с массивом:
doc #>> '{application_id}'
Напомним, что операторы ->> и #>> возвращают элемент JSON в виде
строки. Отличаются они методом доступа к элементу: ->> принимает имя поля, а
#>> – их массив для вложенных случаев. И хотя выражения
(doc->>'application_id')::int
(doc #>> '{application_id}')::int
аналогичны, во втором случае план удручающий:
explain analyze
select id, doc from applications
where ((doc #>> '{application_id}')::int) = 550099;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Gather (cost=1000.00..259833.33 rows=5000 width=1830) (actual time=268.194..453.280 rows=1 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Parallel Seq Scan on applications (cost=0.00..258333.33 rows=2083 width=1830) (actual time=88.504..146.584 rows=0 loops=3) │
│ Filter: (((doc #>> '{application_id}'::text[]))::integer = 550099) │
│ Rows Removed by Filter: 333333 │
│ Planning Time: 2.099 ms │
│ Execution Time: 453.380 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Мы получили тот же документ 550099, но это заняло почти полсекунды и обход всей таблицы.
При построении запроса следите за тем, чтобы условие поиска совпадало с индексом. В случае ORM определите константу, которая хранит выражение индекса. Пример на псевдокоде, близком к Python:
FIELD_APP_ID = SQL.raw("((doc #>> '{application_id}')::int)")
Applications.all().filter(FIELD_APP_ID.eq(1199)).query()
Обратите внимание, что индекс приводит поле к числу (часть ::int). Сравнить
такой индекс можно только с числом. Попытка передать строку окончится ошибкой
еще до выполнения запроса.
Что случится, если убрать из индекса часть ::int? В этом случае индекс хранит
строки, а номер документа тоже передают строкой. Покажем это на примере:
create index if not exists
idx_applications_application_id_text
on applications using btree
((doc->>'application_id'));
explain analyze
select id, doc
from applications
where (doc->>'application_id') = '550099';
Поиск по точному номеру работает быстро: план подтверждает, что индекс используется.
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_application_id_text on applications (cost=0.42..8.44 rows=1 width=1830) (actual time=0.060..0.062 rows=1 loops=1) │
│ Index Cond: ((doc ->> 'application_id'::text) = '550099'::text) │
│ Planning Time: 0.196 ms │
│ Execution Time: 0.085 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Однако нас ждет разочарование с сортировкой. Если вывести документы по возрастанию номера, трактуя его как текст, вот что мы получим:
select
(doc->>'application_id') as doc_id
from
applications
order by 1
limit 100
┌─────────┐
│ doc_id │
├─────────┤
│ 1 │
│ 10 │
│ 100 │
│ 1000 │
│ 10000 │
│ 100000 │
│ 1000000 │
│ 100001 │
│ 100002 │
│ 100003 │
│ 100004 │
│ 100005 │
│ 100006 │
Номер 1000000 (миллион) идет перед 100001 (сто тысяч один), потому считается меньше. В отличие от чисел, строки сравниваются посимвольно слева направо. Первые символы обеих строк – единицы; далее сравниваются вторые символы – нули и так далее. На шестой позиции возникает отличие: единица и ноль. Код единицы больше кода нуля, поэтому сравнение завершается в пользу 100001.
Вот зачем мы проводим поле к числу: чтобы сортировка работала по принципу чисел,
а не строк. Кроме того, приведение типа гарантирует, что в базе не окажется
документов, где application_id отличен от числа. При попытке добавить документ
как в примере ниже:
insert into applications (doc)
values ($${
"some_field": 42,
"application_id": "123a456"
}$$::jsonb);
получим ошибку:
ERROR: invalid input syntax for type integer: "123a456"
Если в документе нет поля application_id или оно содержит null, выражение
индекса тоже вернет null, и нарушений не будет. Позже мы рассмотрим, как сделать
некоторые поля документа обязательными (не NULL) на уровне базы.
Индексирование дат
Документы хранят в себе множество дат. Две из них – создания и последнего
изменения – настолько важны, что их выносят в поля таблицы created_at и
updated_at. Чтобы искать по ним документы, добавьте обычный btree-индекс. Если
в качестве ключа используется UUID версии 7, он уже хранит временную метку, так
что индекс по created_at может и не потребоваться.
Документ содержит даты, отражающие бизнес-процессы. Это могут быть даты
одобрения заявки, ее отклонения, сдачи в архив, сроки рассмотрения. Довольно
часто документы ищут по этим критериям. Представим, что заявка содержит поле
approved_at – дату одобрения в формате ISO. Вот как назначить случайную дату в
рамках прошлого года всем заявкам:
update applications
set doc['approved_at'] = to_json(current_timestamp - (interval '1 year') * random());
Быстрая проверка:
select id, doc['approved_at'] from applications limit 10;
┌──────────────────────────────────────┬────────────────────────────────────┐
│ id │ doc │
├──────────────────────────────────────┼────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000009985 │ "2025-10-23T15:59:46.943586+03:00" │
│ 00000000-0000-0000-0000-000000009986 │ "2025-12-17T17:25:57.205986+03:00" │
│ 00000000-0000-0000-0000-000000009987 │ "2025-11-18T21:23:10.137186+03:00" │
│ 00000000-0000-0000-0000-000000009988 │ "2026-01-22T15:32:48.498786+03:00" │
│ 00000000-0000-0000-0000-000000009989 │ "2025-07-20T23:52:08.908386+03:00" │
│ 00000000-0000-0000-0000-000000009990 │ "2025-08-07T10:45:12.690786+03:00" │
│ 00000000-0000-0000-0000-000000009991 │ "2025-12-06T19:23:07.660386+03:00" │
│ 00000000-0000-0000-0000-000000009992 │ "2026-05-04T18:27:36.681186+03:00" │
│ 00000000-0000-0000-0000-000000009993 │ "2025-11-16T02:07:33.791586+03:00" │
│ 00000000-0000-0000-0000-000000009994 │ "2025-11-03T05:24:20.783586+03:00" │
└──────────────────────────────────────┴────────────────────────────────────┘
Требуется найти заявки, одобренные за указанный период. Для этого построим
индекс btree, аналогичный application_id. Разница в том, что поле приводится к
типу timestamp, а не int:
create index if not exists
idx_applications_approved_at
on applications using btree
(((doc->>'approved_at')::timestamptz at time zone 'utc'));
Увы, Postgres не примет команду из-за ошибки:
functions in index expression must be marked IMMUTABLE
Дело в том, что выражения и функции, которые входят в индекс, должны быть неизменяемыми – носить свойство IMMUTABLE. Оно гарантирует, что вычисления зависят только от входных данных и ни в коем случае – от глобальных настроек. Приведение строки к дате не считается неизменяемым, поскольку зависит от формата данных, текущей временной зоны и параметра DateStyle.
Если строка содержит дату в формате ISO, вывод времени все-таки будет неизменяемым. В нашем случае это так, потому что перед вставкой мы проверяем документ схемой следующей схемой:
{ “type”: “object”, “properties”: { “approved_at”: { “type”: “string”, “format”: “date-time” } } }
Спецификация JSON Schema предлагает формат date-time для строки, который гарантирует, что внутри – дата и время в формате ISO.
Поэтому, чтобы заставить Postgres построить индекс, обернем приведение типа в функцию с тегом IMMUTABLE:
create or replace function to_tsz_immutable(text)
returns timestamptz
language sql immutable strict parallel safe
return $1::timestamptz;
Уточним: технически возможно написать функцию, которая объявлена неизменяемой, но не является таковой. Postgres никак это не проверяет; ответственность за тег immutable лежит на вас. Если построить индекс с функцией, которая зависит от внешних факторов, вас ждет странное поведение.
Новый индекс не вызовет нареканий:
create index if not exists
idx_applications_approved_at
on applications using btree
(to_tsz_immutable(doc->>'approved_at'));
Выборка за период:
explain analyze
select id from applications
where to_tsz_immutable(doc->>'approved_at') between
current_timestamp - interval '3 months'
and
current_timestamp - interval '1 months'
limit 100;
План:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.43..401.63 rows=100 width=16) (actual time=0.080..0.247 rows=100 loops=1) │
│ -> Index Scan using idx_applications_approved_at on applications (cost=0.43..20060.44 rows=5000 width=16) (actual time=0.078..0.233 rows=100 loops=1) │
│ Index Cond: ((to_tsz_immutable((doc ->> 'approved_at'::text)) >= (CURRENT_TIMESTAMP - '3 mons'::interval)) AND (to_tsz_immutable((doc ->> 'approved_at'::text)) <= (CURRENT_TIMESTAMP - '1 mon'::interval))) │
│ Planning Time: 0.257 ms │
│ Execution Time: 0.277 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
При работе с ORM поместите выражение to_tsz_immutable(doc->>'approved_at') в
константу, чтобы гарантировать попадание в индекс.
По аналогии строятся индексы для других типов. Если Postgres не принимает
выражение field::type из-за того, что не считает операцию иммутабельной,
напишите функцию, которая гарантирует это свойство и постройте по ней
индекс. Если вы обновили функцию, сразу же перестройте индекс командой:
REINDEX INDEX my_index_name;
Коротко о hash-индексе
Даже если поле нуждается в индексе, он не обязан быть btree. Когда требуется только равенство, а сортировка и диапазоны – нет, подойдет индекс hash. Он устроен как хэш-таблица, где ключи приводятся к числу специальной функцией. Мы не будем изучать устройство хэш-таблиц, потому что они подробно описаны в других материалах. Достаточно понимать, что поиск в них занимает постоянное время O(1). Таблицы не упорядочены, потому что не хранят порядок ключей. Отсюда невозможность обойти значения по убыванию или возрастанию.
Построим hash-индекс для поля organization.short_name с кодом клиента. Нам не
нужна сортировка этому полю, потому что коды клиентов не образуют
последовательности. В выражении индекса изменится только тип:
create index if not exists
idx_applications_org_short_name_hash
on applications using hash
(((doc #>> '{organization,short_name}')));
Убедимся, что индекс работает:
explain
select id
from applications
where (doc #>> '{organization,short_name}') = 'Organization 543'
limit 100;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.00..400.15 rows=100 width=16) │
│ -> Index Scan using idx_applications_org_short_name_hash on applications (cost=0.00..20007.50 rows=5000 width=16) │
│ Index Cond: ((doc #>> '{organization,short_name}'::text[]) = 'Organization 543'::text) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Практика показывает, что для чисел и небольших строк индекс hash оказывается примерно на 10% быстрее. Однако на больших строках индекс замедляется: хэш-функция перебирает символы один за другим, и чем длиннее строка, тем дольше вычисляется хэш. Исследование и замеры вы найдете в блоге компании Cybertec в статье “PostgreSQL hash index performance”.
Статистика индексов
Не добавляйте индексы без веской на то причины. Индексируйте только те поля, поиск по которым выполняется часто. Сюда относятся запросы от API или интерфейса программы. Добавлять индексы ради отчетов или служебных запросов не стоит. Лучше выполнить их в нерабочее время или на резервной копии (реплике).
Каждый индекс отъедает ресурсы: место на диске и такты процессора. Чем больше индексов, тем медленней происходят действия с таблицей. Добавить индекс легко, а избавиться – не так просто. Бывает, у таблицы два десятка индексов, и никто не знает, в каком месте они используются – если вообще используются, а не остались от чьих-то экспериментов.
К счастью, Postgres ведет статистику обращения к индексам. Посмотреть ее можно в
представлении pg_stat_user_indexes следующим запросом:
select * from pg_stat_user_indexes
where relname = 'applications'
-- order by last_idx_scan desc nulls last
limit 10;
Частичный результат:
┌──────────────────────────────────────────────────┬──────────┬──────────────┬───────────────┐
│ indexrelname │ idx_scan │ idx_tup_read │ idx_tup_fetch │
├──────────────────────────────────────────────────┼──────────┼──────────────┼───────────────┤
│ applications_pkey │ 4 │ 4 │ 4 │
│ idx_applications_created_at │ 5 │ 258830 │ 1 │
│ idx_applications_application_id │ 1 │ 100 │ 100 │
│ idx_applications_org_short_name_hash │ 6 │ 4790 │ 790 │
│ idx_applications_status │ 6 │ 1495416 │ 0 │
│ idx_applications_status_org_short_name │ 1 │ 263 │ 263 │
│ idx_applications_application_id_trgm │ 1 │ 20 │ 0 │
│ idx_applications_application_org_short_code_trgm │ 2 │ 2000 │ 0 │
│ idx_applications_application_trgm_pattern │ 4 │ 4138 │ 0 │
│ idx_applications_application_id_text │ 2 │ 2 │ 2 │
└──────────────────────────────────────────────────┴──────────┴──────────────┴───────────────┘
Среди прочих полей нас интересует idx_scan – число обращений к
индексу. Очевидно, если индекс не используется, показатель будет низким;
сортировка по idx_scan выявит кандидатов на удаление.
Еще одно полезное поле – last_idx_scan, которое хранит дату и время последнего
обращения к индексу. Выше мы закомментировали сортировку по нему, потому что
поле доступно только с версии 16. С ним выявить лишние индексы еще легче. В
самом деле: даже если индекс не используется, у него может быть высокий
показатель idx_scan, накопленный за всю историю базы. Однако last_idx_scan
точно покажет, что в последний раз к индексу обращались полгода назад, а значит
– пора его удалить.
Полное описание pg_stat_user_indexes вы найдете в документации
Postgres.
Составные индексы
Мы выяснили, как создать индекс для отдельного поля. Также мы выяснили, что индексов не должно быть чрезмерно много. Принцип понятен, но следовать ему на практике нелегко. Часто нас просят добавить индекс на еще одно поле, второе, третье… их число растет, а производительность запросов – не особо. Все потому, что массовое добавление индексов не решает проблему их качества. Ниже мы рассмотрим случай, когда один составной индекс полезнее двух простых.
Предположим, у сущности три поля: A, B и C. Все они покрыты индексом
btree. Каждый запрос попадает в свой индекс без нареканий:
select from entity where a = 1;
select from entity where b = 'test';
select from entity where c = true;
Что случится, если добавить условие на два поля одновременно? Или даже на три – как поведет себя запрос?
select from entity
where
a = 1
and b = 'test'
and c = true;
Часто рассуждают так: если каждое из полей индексировано, их композиция тоже индексирована, и беспокоиться не о чем. Что ж, давайте это проверим!
Для начала подумаем, в когда нам понадобятся несколько индексов. Предположим, когда сотрудник открывает программу для работы с заявками, главный экран показывает закрепленные за ним активные заявки. С точки зрения базы это два условия:
- поле
assigned_toравно текущему пользователю; - статус заявки равен
active.
Вместе они дают запрос:
select id, doc
from applications
where
doc->>'assigned_to' = 'ivanov@acme.com'
and doc->>'status' = 'active';
Активные заявки – центральное место приложения: они показывают сотруднику, над чем ему сегодня работать. Запрос будет выполняться часто, и мы должны обеспечить быстродействие.
Чтобы приблизить ситуацию к реальности, обновим поля заявок. Будем считать, что
в штате не более тысячи сотрудников. Каждое значение assigned_to (назначенный
пользователь) будет почтой вида user_xxx@test.com, где xxx – остаток от
деления номера заявки на тысячу. Напомним, что номер заявки уникален и находится
в диапазоне от единицы до миллиона.
Чуть сложнее поле статуса. На этапе генерации мы получили его случайно из массива:
((array['active', 'pending', 'approved', 'deleted'])[ceil(random() * 4)])
Это не совсем верно: в реальности большинство заявок имеют финальный статус (одобрено или отклонено) и лишь малая часть находится на рассмотрении.
В прошлой главе мы упоминали генерацию с учетом весов. На примере городов мы рассмотрели, как распределить по ним пользователей согласно доле населения. На ее базе напишем функцию generate_status, которая производит статусы со следующим распределением:
- 65% – заявка сдана в архив;
- 20% – одобрена;
- 10% – отклонена;
- оставшиеся 5% – в работе.
Код функции:
create or replace function generate_status() returns text
language plpgsql strict parallel safe
as $func$
declare
r float4;
begin
r := random();
case
when r < 0.65 then return 'archived'; -- 65%
when r < 0.85 then return 'approved'; -- 20%
when r < 0.95 then return 'rejected'; -- 10%
else return 'active'; -- 5%
end case;
end;
$func$;
С учетом сказанного обновим заявки:
update applications set
doc['assigned_to'] = to_jsonb(format('user_%s@test.com', (doc->>'application_id')::int % 1000)),
doc['status'] = to_jsonb(generate_status());
Проверим распределение статусов:
select
to_char(count(id) / 1000000.0, '0.9999') as ratio,
doc->>'status' as status
from applications
group by 2
order by 1 desc;
┌─────────┬──────────┐
│ ratio │ status │
├─────────┼──────────┤
│ 0.6498 │ archived │
│ 0.2001 │ approved │
│ 0.1002 │ rejected │
│ 0.0499 │ active │
└─────────┴──────────┘
Теперь когда оба поля готовы, объявим для каждого btree-индекс:
create index if not exists
idx_applications_status
on applications using btree
((doc->>'status'));
create index if not exists
idx_applications_assigned_to
on applications using btree
((doc->>'assigned_to'));
analyze applications;
Проверим, как поведет себя запрос с условием по двум полям:
explain analyze
select id, doc from applications
where
(doc->>'status') = 'active'
and (doc->>'assigned_to') = 'user_999@test.com';
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on applications (cost=407.13..526.75 rows=30 width=1915) (actual time=8.974..9.047 rows=45 loops=1) │
│ Recheck Cond: (((doc ->> 'assigned_to'::text) = 'user_999@test.com'::text) AND ((doc ->> 'status'::text) = 'active'::text)) │
│ Heap Blocks: exact=45 │
│ -> BitmapAnd (cost=407.13..407.13 rows=30 width=0) (actual time=8.938..8.940 rows=0 loops=1) │
│ -> Bitmap Index Scan on idx_applications_assigned_to (cost=0.00..8.99 rows=609 width=0) (actual time=0.361..0.361 rows=1000 loops=1) │
│ Index Cond: ((doc ->> 'assigned_to'::text) = 'user_999@test.com'::text) │
│ -> Bitmap Index Scan on idx_applications_status (cost=0.00..397.88 rows=30060 width=0) (actual time=7.956..7.956 rows=49918 loops=1) │
│ Index Cond: ((doc ->> 'status'::text) = 'active'::text) │
│ Planning Time: 0.316 ms │
│ Execution Time: 9.338 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
План говорит, что использовались оба индекса. Сначала Postgres обошел индекс
idx_applications_assigned_to и пометил в битовой карте кандидатов по полю
assigned_to. Далее составил такую же карту для статуса (индекс
idx_applications_status). Обе карты объединяются битовым AND; в ней остались
только те единичные биты, что были в двух исходных одновременно. По итоговой
карте Postgres выбрал строки и еще раз проверил условия. В выборке оказалось 45
документов. На исполнение ушло 9 миллисекунд – хороший показатель, который,
однако, можно улучшить.
Добавим составной индекс на пару полей [статус, пользователь] одновременно:
create index if not exists
idx_applications_status_assigned_to
on applications using btree
((doc->>'status'), (doc->>'assigned_to'));
Выполним анализ таблицы
analyze applications;
и повторим запрос explain analyze. Новый план:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_status_assigned_to on applications (cost=0.42..185.65 rows=51 width=1915) (actual time=0.139..0.223 rows=45 loops=1) │
│ Index Cond: (((doc ->> 'status'::text) = 'active'::text) AND ((doc ->> 'assigned_to'::text) = 'user_999@test.com'::text)) │
│ Planning Time: 1.733 ms │
│ Execution Time: 0.270 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Запрос занял 0.27 миллисекунды – в сорок раз ниже! Разумеется, от запуска к запуску эта цифра плавает: выдает значения от 0.1 до 0.4. Разница на порядок, однако, сохраняется. Это подтверждает тезис о том, что поиск по составному индексу быстрее, чем два промежуточных поиска с объединением результатов.
Еще одна разновидность сложных индексов – это условные индексы с выражением
where. Особенность в том, что они охватывают только часть таблицы. Индекс
становится меньше, и тем самым ускоряется его обход.
Если взглянуть на индекс по паре [статус, пользователь], заметно следующее. Чтобы показать пользователю, над какими заявками ему работать, мы указываем статус active. Другие статусы нас не интересуют. Раз так, можно создать индекс по полю assigned_to, но так, чтобы он охватывал только активные заявки. Вот как это сделать:
create index if not exists
idx_applications_assigned_to_active
on applications using btree
((doc->>'assigned_to'))
where (doc->>'status' = 'active');
В условии индекса (части where) могут быть только литералы – явно указанные
значения. Параметры и переменные не допускаются. Новый план:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_applications_status_assigned_to on applications (cost=0.42..185.65 rows=51 width=1915) (actual time=0.139..0.223 rows=45 loops=1) │
│ Index Cond: (((doc ->> 'status'::text) = 'active'::text) AND ((doc ->> 'assigned_to'::text) = 'user_999@test.com'::text)) │
│ Planning Time: 1.733 ms │
│ Execution Time: 0.270 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Скорость запроса в целом такая же: около 0.2 миллисекунды. И хотя в данном случае мы ничего не выиграли, условный индекс дает возможности для оптимизации.
Предположим, заявка на кредит может быть разных типов: обычной, когда заемщиком
выступает организация, и государственной, когда деньги занимает целая
страна. Пусть это будет поле credit_type со значениями org и
country. Назначим их заявкам следующим образом:
create or replace function generate_credit_type() returns text
language plpgsql strict parallel safe
as $func$
declare
r float4;
begin
r := random();
case
when r < 0.85 then return 'org'; -- 85%
else return 'country'; -- 15%
end case;
end;
$func$;
update applications set
doc['credit_type'] = to_jsonb(generate_credit_type());
Требования таковы, что рядовые сотрудники не должны видеть заявки от государств, потому что их рассматривает особое звено. Поэтому в запрос, который выводит активные заявки сотрудника, придется добавить условие, что тип не равен “country”:
(doc->>'credit_type') != 'country'
Можно сделать иначе: построить индекс по полю assigned_to, покрывающий только
активные заявки с типом 'org':
create index if not exists
idx_applications_active_org_assigned_to
on applications using btree
((doc->>'assigned_to'))
where
(doc->>'status' = 'active')
and (doc->>'credit_type') = 'org';
Если выбрать документы следующим запросом:
explain analyze
select id, doc from applications
where
(doc->>'status') = 'active'
and (doc->>'credit_type') = 'org'
and (doc->>'assigned_to') = 'user_999@test.com'
, то по условиям (doc->>'status') = 'active' и (doc->>'status') = 'active'
база определит, что конкретно этому запросу подходит индекс
idx_applications_active_org_assigned_to. Он хранит не всю тройку полей, а
только пользователей. Такой индекс компактен, и поиск по нему быстрее. При этом
никакой фильтрации по статусу и типу заявки не было – мы просто взяли нужный
индекс.
У составного индекса тоже есть преимущество. Если он составлен по тройке (A, B,
C), то будет работать, даже если известны A и B или даже просто A. Обобщая,
можно сказать, что составной индекс работает, если известны его лидирующие
компоненты. Ситуция (A, C) не подходит под этот случай, потому что неизвестен
компонент B посередине. Такой вариант возможен, но скорее всего Postgres
посчитает его дорогим и предпочтет полный обход таблицы.
Проектируя составной индекс, располагайте компоненты так, чтобы покрыть как можно больше сценариев. Если вы фильтруете записи по полям A и B и создали индекс (A, B), это позволит фильтровать в том числе по A. Если же индекс объявлен как (B, A), то условию по A понадобится отдельный индекс. В этом случае проще удалить (B, A) и создать (A, B).
Поиск по шаблону
До сих пор мы искали документы по точным значениям, например бухгалтерскому номеру. Но что делать, если номер известен частично? Пользователям трудно запомнить десятизначные номера, и они помнят их подмножества: последние цифры, удачные комбинации. В этом случае нужен поиск по вхождению.
Для этих целей Postgres предлагает операторы like, ilike и ~ (тильда). Первые
два проверяют текст на вхождение другого текста, при этом поддерживаются простые
шаблоны. Тильда сопоставляет текст с регулярным выражением, которые мощнее
шаблонов. Расширение pg_trgm предлагает индексы для этих и других
операторов. Ниже мы рассмотрим детали.
Начнем с оператора like. Слева от него находится исходная строка, а справа – шаблон. Символы шаблона читаются как есть за исключением двух:
- подчеркивание (
_) означает строго один любой символ; - процент (
%) означает ноль и более любых символов.
Вот и вся шаблонная система. Приведем несколько примеров:
select '123456789' like '123'
Вернет ложь, потому что это буквальное сравнение, и строки не равны.
select '123456789' like '123%'
Вернет истину, потому что процент поглощает символы 4, 5 и до конца строки.
select '1a3' like '1_3';
Вернет истину, потому что символ “a” удовлетворяет подчеркиванию (любому символу).
Оператор ilike (insensitive like) действует аналогично, но без учета регистра. С
цифрами этого не заметно, поэтому приведем пример с электронной почтой. Ниже мы
проверяем, какие адреса на домене gmail.com содержат имя john:
select
email,
email ilike '%john%@gmail.com' as matches
from (values
('JOHN_SMITH@Gmail.com'),
('Adam.Smith@GMAIL.com'),
('John+Anderson@gmail.com'),
('johnson@gmail.com')
) as vals(email);
Результат:
┌─────────────────────────┬─────────┐
│ email │ matches │
├─────────────────────────┼─────────┤
│ JOHN_SMITH@Gmail.com │ t │
│ Adam.Smith@GMAIL.com │ f │
│ John+Anderson@gmail.com │ t │
│ johnson@gmail.com │ t │
└─────────────────────────┴─────────┘
Возвращаемся к нашим заявкам. Предположим, о номере заявки мы помним только то, что в нем была комбинация 12345. Чтобы найти все варианты, выполним запрос:
select id from applications
where (doc->>'application_id') like '%12345%'
limit 100;
Частичный результат:
┌──────────────────────────────────────┐
│ id │
├──────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000012345 │
│ 00000000-0000-0000-0000-000000112345 │
│ 00000000-0000-0000-0000-000000123450 │
│ 00000000-0000-0000-0000-000000123451 │
│ 00000000-0000-0000-0000-000000123452 │
│ 00000000-0000-0000-0000-000000123453 │
│ 00000000-0000-0000-0000-000000123454 │
│ 00000000-0000-0000-0000-000000123455 │
Данные придут с ощутимой задержкой. Даже без плана ясно, что запрос не оптимален: нет индекса, который бы его ускорял.
Нам понадобится расширение pg_trgm; оно встроено в Postgres и считается
доверенным. Последнее означает, что его может включить обычный пользователь, а
не только администратор. Сочетание trgm означает trigram – триграмма, часть
слова из трех букв. Расширение разбивает текст на триграммы и использует их в
различных операциях. Так, слово hello становится массивом триграмм как в примере
ниже:
{" h"," he",ell,hel,llo,"lo "}
Обратите внимание на частично пустые первую и последнюю триграммы: они содержат пробелы. Полученный массив сопоставляется с шаблоном, а также служит для вычисления схожести строк.
Подключите расширение командой create extension. Достаточно выполнить ее
однажды в разрезе базы данных:
create extension if not exists pg_trgm;
Создайте индекс как в примере ниже:
create index if not exists
idx_applications_application_id_trgm
on applications using gin
((doc->>'application_id') gin_trgm_ops);
Обратите внимание, что тип индекса – не btree, а GIN. Аббревиатура означает
Generalized Inverted Index – обобщенный инвертированный индекс. Этот тип
применяют в случаях, когда индексируемое значение состоит из элементов. Частный
случай GIN – поиск в массивах и триграммах. Переменная gin_trgm_ops задает
параметры, специфичные для триграмм.
Кроме GIN, индекс может быть объявлен с типом GiST, что означает Generalized Search Tree (обобщенное дерево поиска). У каждого из этих типов преимущества и недостатки; обобщая, можно сказать, что GIN производительней в чтении, но занимает больше места. GiST, напротив, компактней, но медленней в плане доступа. Также GiST опирается на хэширование элементов, поэтому для решения коллизий выражение индекса может вычисляться дважды.
В данной книге мы не будем сравнивать производительность GIN и GiST. Для триграмм документация Postgres рекомендует использовать тип GIN, что мы и сделаем. Предлагаем читателю создать индексы обоих типов и замерить скорость самостоятельно.
Убедимся, что поиск по частичному номеру попадает в индекс. Повторим недавний запрос:
explain analyze
select id from applications
where (doc->>'application_id') like '%12345%'
limit 100;
И проверим план:
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=32.77..428.27 rows=100 width=16) (actual time=0.629..0.732 rows=20 loops=1) │
│ -> Bitmap Heap Scan on applications (cost=32.77..428.27 rows=100 width=16) (actual time=0.628..0.729 rows=20 loops=1) │
│ Recheck Cond: ((doc ->> 'application_id'::text) ~~ '%12345%'::text) │
│ Heap Blocks: exact=13 │
│ -> Bitmap Index Scan on idx_applications_application_id_trgm (cost=0.00..32.75 rows=100 width=0) (actual time=0.601..0.601 rows=20 loops=1) │
│ Index Cond: ((doc ->> 'application_id'::text) ~~ '%12345%'::text) │
│ Planning Time: 0.646 ms │
│ Execution Time: 0.765 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Выражение Bitmap Index Scan присутствует, время выполнения – 0.7 миллисекунд. Индекс работает.
При работе с триграммами помните о следующих правилах. Первое – длина шаблона (за вычетом символа %) должна быть не менее трех символов. Если передать их меньше, индекс использоваться не будет. Правило легко запомнить: триграмма – это три. В запросе ниже база выполняет полное сканирование, хотя, казалось бы, шаблон состоит из четырех символов:
explain analyze
select id from applications
where (doc->>'application_id') like '%99%'
limit 100;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.00..874.50 rows=100 width=16) (actual time=0.096..5.397 rows=100 loops=1) │
│ -> Seq Scan on applications (cost=0.00..265000.00 rows=30303 width=16) (actual time=0.095..5.387 rows=100 loops=1) │
│ Filter: ((doc ->> 'application_id'::text) ~~ '%99%'::text) │
│ Rows Removed by Filter: 4378 │
│ Planning Time: 0.189 ms │
│ Execution Time: 5.424 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Если вы принимаете текст от пользователя, проверяйте его длину. Добавьте ограничение в интерфейс, чтобы поле ввода принимало не менее трех символов. Этим вы снизите число неудачных обращений к серверу.
Когда текст приводится к триграммам, из него удаляются незначащие символы. К ним
относятся знаки препинания, скобки, математические символы, всевозможные
амперсанды, проценты и так далее. Это легко проверить функцией show_trgm,
которая принимает текст и возвращает массив триграмм:
select show_trgm('#alpha!, ?beta=, ;gamma^');
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│ show_trgm │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ {" a"," b"," g"," al"," be"," ga",alp,amm,bet,eta,gam,"ha ",lph,"ma ",mma,pha,"ta "} │
└─────────────────────────────────────────────────────────────────────────────────────────┘
Видим, что все незначащие символы были отброшены. Будет неверно полагаться на поиск с учетом решетки, восклицательного знака, пунктуации и так далее: алгоритм их игнорирует.
Если шаблон передал пользователь, проверяйте символы процента и подчеркивания. Операторы (i)like трактуют их особым образом, что может привести к тому, что запрос выдал лишние записи. Если пользователь имел в виду именно подчеркивание или процент, экранируйте их – предварите обратной косой чертой. Например, чтобы проверить текст на вхождение строки “50%”, передайте следующий шаблон:
select 'more than 50% in total' ilike '%50\%%';
-- t
Иногда мы намеренно позволяем клиенту использовать шаблоны. Здесь возможно улучшение: традиционно в компьютерах используется несколько иная система шаблонов. Разница минимальна: один любой символ обозначают вопросом, цепочку любых символов (в том числе пустую) – звездочкой. Так, строке:
Замечания по договору 12352/AB/04
соответствует шаблон
*по договору 12352/??/??
Практика показывает, что вопрос и звездочку люди запоминают лучше, чем процент с подчеркиванием. Перевод одного шаблона к другому сводится к автозамене.
По аналогии добавьте триграммные индексы для других полей, например названию
фирмы или назначенному сотруднику (organization.short_name,
assigned_to). Проверьте план и производительность запросов.
Триграммный поиск по многим полям
Иногда поиска по одному полю недостаточно: нужно искать сразу по нескольким, например статусу, комментарию и пользователю. Можно выполнить три запроса, объединить результат и удалить повторяющиеся документы. Производительность такого решения будет под вопросом; попытаемся выразить поиск по многим полям в одном запросе.
Предположим, клиенты ищут документы по номеру, организации, закрепленному сотруднику, комментарию и так далее. Как при этом выглядит интерфейс программы? Обычный подход предполагает, что клиент увидит следующую форму:
┌───────────────────────────────────────────┐
│ │
│ Номер заявки │
│ ┌──────────────────────────┐ │
│ │12345 │ │
│ └──────────────────────────┘ │
│ │
│ Название организации │
│ ┌──────────────────────────┐ │
│ │Acme │ │
│ └──────────────────────────┘ │
│ │
│ Сотрудник │
│ ┌──────────────────────────┐ │
│ │Maria │ │
│ └──────────────────────────┘ │
│ │
│ Комментарий │
│ ┌──────────────────────────┐ │
│ │reconciliation │ │
│ └──────────────────────────┘ │
│ │
│ ┏━━━━━━━━━━━━━━┓ │
│ ┃ Найти ┃ │
│ ┗━━━━━━━━━━━━━━┛ │
│ │
└───────────────────────────────────────────┘
Каждое поле формы связано с атрибутом документа. Если клиент заполнил форму как в примере выше и нажал ввод, на сервер уйдет следующий JSON:
{
"application_id": "12345",
"org_name": "Acme",
"created_by": "Maria",
"comment": "reconciliation"
}
Предполагается, что поля объединены логикой AND, и каждое из них проверяется на вхождение. Сервер выполнит следующий запрос:
select
id, doc
from
applications
where
(doc #>> '{application_id}') ilike '%12345%'
and (doc #>> '{organization,short_name}') ilike 'Acme'
and (doc #>> '{created_by,name}') ilike '%Maria%'
and (doc #>> '{comment}') ilike '%reconciliation%'
limit
100;
Мы не знаем заранее, сколько получим критериев поиска. Понадобится библиотека, чтобы построить SQL без риска инъекций со стороны клиента. Проверьте, что клиент отправил хотя бы один критерий поиска. Иначе мы получим либо неверный синтаксис (пустой WHERE), либо запрос без WHERE, который читает всю таблицу.
Есть и другой подход к поиску: предоставить клиентам поле ввода “а-ля Гугл”. Его строка проверяет сразу несколько атрибутов. Это современный подход, и сегодня его воспринимают как должное. Например, если клиент ввел 12345, мы вернем документы, где эта строка встречается в номере, названии клиента, комментарии и других полях.
┌─────────────────────────────────────────────────────────────┐
│ │
│ Номер заявки, ФИО сотрудника, комментарий (см. справку) │
│ ┌──────────────────────────────────────────────┐┏━━━━━━━━━┓ │
│ │12345 │┃ Поиск ┃ │
│ └──────────────────────────────────────────────┘┗━━━━━━━━━┛ │
│ │
└─────────────────────────────────────────────────────────────┘
Рассмотрим, как сделать подобный поиск. Наивное решение в том, чтобы составить набор условий вида:
(doc #>> '{path.to.field}') ilike '%12345%'
и объединить их оператором OR: либо это поле, либо то, другое и так далее. Для каждого поля создайте триграммный индекс по образцу:
create index if not exists
idx_applications_trgm_path_to_field
on applications using gin
((doc #>> '{path.to.field}') gin_trgm_ops);
Предположим теперь, клиент ввел в поле поиска 12345 и нажал ввод. Сервер построит запрос:
select
id, doc
from
applications
where
(doc #>> '{application_id}') ilike '%12345%'
or (doc #>> '{organization,short_name}') ilike '%12345%'
or (doc #>> '{created_by,name}') ilike '%12345%'
or (doc #>> '{comment}') ilike '%12345%'
limit
100;
План этого запроса велик и не поместится на странице книги. Описать его можно так: четыре узла Bitmap Index Scan, каждый из которых сканирует свой индекс и строит битовую карту. Четыре карты объединяются методом BitmapOr. По ней база обходит таблицу applications и выбирает 20 записей. На ноутбуке автора запрос выполнялся 3 миллисекунды.
Преимущество в том, что новая форма в меньшей степени зависит от логики сервера. Если мы расширим поиск на еще один атрибут, форме потребуются только косметические доработки (скажем, справочный текст).
Тем не менее, на сервере каждый атрибут требует триграммного индекса, чего хотелось бы избежать. Если присмотреться, затетим, что выражение вида
(field1 ilike 'pattern') or (field2 ilike 'pattern') or ...
аналогично следующему:
(field1 || ' ' || field1 || ...) ilike 'pattern'
Другими словами: если объединить поля пробелом, достаточно одной проверки на соответствие шаблону. Результат объединения назовем поисковым агрегатом. При этом не важно, какая именно его часть совпала: та, что принадлежит первому полю, второму или последнему.
Рассмотрим, где хранить подобную строку-агрегат. Технически он может быть атрибутом документа или колонкой. Но встает вопрос синхронизации: как уследить, чтобы при изменении документа перестраивался и агрегат.
Postgres предлагает несколько способов это сделать, в том числе:
- объявить триггер на вставку и обновление документа. Триггер вычисляет агрегат и записывает в отдельное поле.
- Создать вычисляемый столбец с индексом. Такие столбцы обновляются автоматически при изменении записи.
- Построить индекс на выражение, которое вернет поисковой агрегат.
Каждый способ обладает преимуществами и недостатками. Поскольку в данной главе мы говорим об индексах, то выберем третий вариант. Триггеры и вычисляемые столбцы мы обсудим в следующих главах.
Подготовим выражение, которое объединяет атрибуты документа через пробел. Наивное решение:
(
(doc #>> '{application_id}') || ' '
|| (doc #>> '{organization,short_name}') || ' '
|| (doc #>> '{created_by,name}') || ' '
|| (doc #>> '{comment}')
)
Недостаток в том, что если в документе нет хотя бы одного поля, конструкция
“занулится” – вернет NULL. Для несуществующего поля оператор #>> производит
NULL, его соединение со строкой тоже даст NULL и так далее. Это легко проверить:
select 'foo' || null || 'bar' as result;
┌────────┐
│ result │
├────────┤
│ <null> │
└────────┘
Не хотелось терять документ из-за одного неудачного атрибута. Поступим умнее:
обернем операторы #>> формой coalesce с пустой строкой — умолчанием для
NULL:
(
coalesce((doc #>> '{application_id}'), '') || ' '
|| coalesce((doc #>> '{organization,short_name}'), '') || ' '
|| coalesce((doc #>> '{created_by,name}'), '') || ' '
|| coalesce((doc #>> '{comment}'), '')
)
Это выражение устойчиво к NULL, но выглядит слишком шумно. Более удачный вариант – взять функцию concat_ws. Она принимает разделитель и произвольные аргументы, которые приводятся к строке; значения NULL при этом игнорируются:
concat_ws(
' ',
(doc #>> '{application_id}'),
(doc #>> '{organization,short_name}'),
(doc #>> '{created_by,name}'),
(doc #>> '{comment}')
)
Осталось создать по нему триграммный индекс, и дело готово:
create index if not exists
idx_applications_application_trgm_pattern
on applications using gin
((concat_ws(
' ',
(doc #>> '{application_id}'),
(doc #>> '{organization,short_name}'),
(doc #>> '{created_by,name}'),
(doc #>> '{comment}')
)) gin_trgm_ops);
Однако нас ждет ошибка:
ERROR: functions in index expression must be marked IMMUTABLE
Как мы упоминали, в выражении индекса функции должны быть помечены как
неизменяемые (immutable). Для concat_ws это требование не выполняется. Почему?
Разве соединение строк пробелом носит побочный эффект?
Дело в том, что concat_ws работает в два этапа. Второй из них – соединение
текстовых фрагментов разделителем; это действие, конечно, иммутабельно. Первый
этап приводит все значения к тексту и передает второму. Типы вроде дат и времени
зависят от локали и глобальных настроек, из-за чего приведение их к тексту не
считается иммутабельным.
Функция, выполняющая второй этап, называется text_concat_ws. Она написана на
языке Си поэтому недоступна без ухищрений. Postgres позволяет создать
функцию-обертку, которая сводится к вызову внутренней (internal) функции. В
примере ниже функция immutable_concat_ws не имеет тела, а только передает
управление функции text_concat_ws:
create or replace function immutable_concat_ws(text, variadic text[])
returns text
language internal immutable parallel safe AS 'text_concat_ws';
Объявлять подобные обертки может только администратор, поскольку функции на Си, как правило, небезопасны.
Теперь когда у нас есть иммутабельная функция для склейки строк, построим индекс сразу на четыре поля:
create index if not exists
idx_applications_application_trgm_pattern
on applications using gin
((immutable_concat_ws(
' ',
(doc #>> '{application_id}'),
(doc #>> '{organization,short_name}'),
(doc #>> '{created_by,name}'),
(doc #>> '{comment}')
)) gin_trgm_ops);
Проверим поиск документов с новым индексом. Выполним запрос с шаблоном
%12345%:
select id from applications
where (immutable_concat_ws(
' ',
(doc #>> '{application_id}'),
(doc #>> '{organization,short_name}'),
(doc #>> '{created_by,name}'),
(doc #>> '{comment}')
)) ilike '%12345%'
limit 100;
В плане окажется один узел Bitmap Index Scan вместо четырех. Время выполнения – 0.6 миллисекунды. Эксперимент подтвердил тезис, что один сложный индекс, составленный под конкретные требования, лучше нескольких.
Строить запросы с таким условием затруднительно, а сами они занимают много места
в логах. Если перепутать поля местами или добавится новое поле, индекс не
сработает. Чтобы решить эти проблемы, напишем функцию
application_search_pattern. Она принимает документ и возвращает поисковой
агрегат:
create or replace function application_search_pattern(doc jsonb)
returns text
language sql immutable strict parallel safe
return immutable_concat_ws(
' ',
(doc #>> '{application_id}'),
(doc #>> '{organization,short_name}'),
(doc #>> '{created_by,name}'),
(doc #>> '{comment}')
);
Опробуем функцию, чтобы представлять, что она возвращает:
select id, application_search_pattern(doc)
from applications
limit 100;
┌──────────────────────────────────────┬────────────────────────────────────────────────────────┐
│ id │ application_search_pattern │
├──────────────────────────────────────┼────────────────────────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000010049 │ 10049 Organization 49 User 0049 Comment number #10049 │
│ 00000000-0000-0000-0000-000000010050 │ 10050 Organization 50 User 0050 Comment number #10050 │
│ 00000000-0000-0000-0000-000000010051 │ 10051 Organization 51 User 0051 Comment number #10051 │
│ 00000000-0000-0000-0000-000000010052 │ 10052 Organization 52 User 0052 Comment number #10052 │
Удалим старый индекс и построим новый, где вызов immutable_concat_ws со
множеством полей заменен функцией:
drop index if exists
idx_applications_application_trgm_pattern;
create index if not exists
idx_applications_application_trgm_pattern
on applications using gin
((application_search_pattern(doc)) gin_trgm_ops);
Новый запрос выглядит так:
select id from applications
where application_search_pattern(doc) ilike '%12345%'
limit 100;
Его план и быстродействие будут такими же, как в случае с конкатенацией. Однако
запрос сократился, и выражение application_search_pattern(doc) легче
запомнить. Если мы случайно ошиблись в названии функции, то получим ошибку, а не
молчаливый full seq scan.
Если состав атрибутов, по которым ищутся документы, изменится — появился новый или удалился прежний, — просто обновите функцию. Исправлять запросы при этом не понадобится. После обновления функции перестройте индекс командой REINDEX, чтобы избежать странного поведения.
Коротко о ранжировании
У оператора ilike недостаток: он не ранжирован. Даже если агрегат совпадает с шаблоном, мы не знаем, в каком месте это случилось: начале, середине или конце. Из-за этого не ясно, какое поле дало положительный результат: номер документа, название фирмы или комментарий. Порой эта неточность снижает качество выдачи.
Предположим, в базе данных есть документ с номером 123. Когда клиент вводит его в поиске, на первом месте он ожидает точное совпадение: документ №123, а только затем документы №1234, №4123 и другие. Сюда же относится код организации: на первом месте должно быть точнее совпадение, а за ним – вхождения. При этом номер документа в приоритете, потому что он описывает выборку точнее: документ №123 – единственный в базе, а у организации с кодом 123 может быть сотня заявок.
Будет удобно, если поиск по слову 123 покажет результаты в следующем порядке:
- документ с номером 123;
- документ, код организации которой равен 123;
- документы, номера которых содержат 123;
- документы, коды организаций которых содержат 123.
Оператор ilike, увы, не предлагает такой градации. Но не расстраивайтесь – к концу книги мы напишем поиск с весовым ранжированием. Пока что отметим, что ilike, при всей ограниченности, обладает и преимуществом: он дешевый и быстрый. Порой его достаточно, чтобы предоставить клиентам простой поиск, что явно лучше, чем ничего. Позже его заменяют на поиск с ранжированием.
Регулярные выражения
Прежде чем закончить с триграммами, расскажем о еще одной их возможности.
Триграммный индекс поддерживает не только шаблоны, но и регулярные
выражения. Предположим, нужно найти документы по какому-то сложному условию,
которое нельзя выразить шаблоном. Пусть это будет заявка организации с кодом 313
и набором символов #22313 в комментарии.
Вот как выглядит новый запрос. Вместо (i)like мы использовали оператор ~
(тильду), который сопоставляет строку слева с регулярным выражением
справа. Вариант со звездочкой ~* означает то же самое, но без учета
регистра. Ниже мы выбрали не документ, а поисковой агрегат, чтобы проверить
себя:
select id, application_search_pattern(doc)
from applications
where application_search_pattern(doc) ~ 'Organization 313.+?#22313'
limit 100;
Этому запросу удовлетворяет лишь одна запись в таблице:
┌──────────────────────────────────────┬──────────────────────────────────────────────────────────────────────────┐
│ id │ application_search_pattern │
├──────────────────────────────────────┼──────────────────────────────────────────────────────────────────────────┤
│ 00000000-0000-0000-0000-000000022313 │ 22313 Organization 313 User 0313 Comment number #22313 user_313@test.com │
└──────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────┘
План подтверждает, что мы попали в индекс: в нем присутствует выражение Bitmap Index Scan. Он не такой уж быстрый: время выполнения – почти 70 миллисекунд.
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=444.78..890.03 rows=100 width=48) (actual time=69.670..69.904 rows=1 loops=1) │
│ -> Bitmap Heap Scan on applications (cost=444.78..890.03 rows=100 width=48) (actual time=69.668..69.903 rows=1 loops=1) │
│ Recheck Cond: (application_search_pattern(doc) ~ 'Organization 313.+?#22313'::text) │
│ Rows Removed by Index Recheck: 19 │
│ Heap Blocks: exact=13 │
│ -> Bitmap Index Scan on idx_applications_application_trgm_pattern (cost=0.00..444.75 rows=100 width=0) (actual time=69.503..69.503 rows=20 loops=1) │
│ Index Cond: (application_search_pattern(doc) ~ 'Organization 313.+?#22313'::text) │
│ Planning Time: 1.735 ms │
│ Execution Time: 69.949 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
О регулярных выражениях скажем следующее. Во-первых, это самобытный язык, который выходит за рамки книги. Регулярные выражения описаны в различных статьях и руководствах, поэтому излагать их здесь будет избыточным. Предлагаем читателю ознакомиться с общими положениями в Википедии, а также документацией Postgres, где рассмотрены особенности их реализации.
Во-вторых, несмотря на всю мощь, регулярные выражения не столь полезны в поиске. Маловероятно, что клиенты настолько технически подкованы, что свободно ими пользуются. Если сервер принимает регулярные выражения, ими можно злоупотребить: составить такое выражение, которое выберет все записи из таблицы. Другая крайность – построить очень сложную конструкцию, которая нагрузит процессор. Примером служит регулярное выражение электронной почты из стандарта RFC822: в распечатанном виде оно занимает лист А4.
Регулярные выражения подходят для внутренних нужд, например выборки документов, подлежащих исправлению. Предоставить их потребителям будет рискованной идеей. Когда возможностей ilike и шаблонов не хватает, рассмотрите полнотекстовый поиск при помощи tsvector и tsquery. В заключительной главе книги мы изучим эти инструменты.
Индексирование документа целиком
Индексы btree и trigram, как бы хороши ни были, не покрывают все варианты поиска. Ниже мы рассмотрим задачу, которая требует дальнейших изысканий.
Одно из полей заявки называется “departments”. В нем структура из двух уровней: отделы (департаменты), связанные с заявкой, а ниже — сотрудники отделов, которые работали над ней. Приведем фрагмент данных:
"departments": [
{
"id": "224899ae-fac7-4555-8a94-f41f3c9ed634",
"code": "risk",
"name": "Risk Department",
"users": [
{
"id": "3f798e66-43a3-4d78-b5f9-3c3740b6676b",
"email": "mironov.p@acme.com",
"name": "Petr Mironov",
"role": "manager"
},
{
"id": "9ecef728-a3bb-4a1d-85d7-51dff2401c0b",
"email": "smith.k@acme.com",
"name": "Karol Smith",
"role": "analyst"
}
]
},
{
"id": "7513991b-9951-4bce-ad4a-470ff2d3febf",
"code": "analytics",
"name": "Analytics Department",
"users": [
{
"id": "361a08a5-3991-4b2a-9d49-8e4cf77b876b",
"email": "smirnov.i@acme.com",
"name": "Igor Smirnov",
"role": "reader"
},
{
"id": "11da81b9-b125-4a7e-9965-958cc5593a3e",
"email": "jackson.j@acme.com",
"name": "John Jackson",
"role": "support"
}
]
}
]
Структура не идеальна, и по-хорошему ей нужна нормализация. Однако за годы скопилось немало кода, который завязан именно на эту структуру данных. Миграция затронет разные команды, придется договариваться о синхронных изменениях. Подобная инициатива часто тонет в согласованиях. Все это печально, но остается правдой жизни в корпорациях.
Предположим, нам поставили задание: найти заявки, над которыми работал пользователь с определенной ролью. Скажем, в примере выше это Karol Smith в роли аналитика. Нужно найти все заявки с подобным условием.
Задание интересно тем, индекс btree нам не поможет. Путь к пользователю Karol Smith следующий:
departments,0,users,1
В отличие от прошлых случаев он включает массивы, точнее номера элементов. Если мы построим индекс btree для этого пути, то охватим лишь часть поля departments: строго первый отдел и второго пользователя. А в другой заявке Karol Smith может быть и вторым, и третьим, и двадцатым пользователем!
Может быть, построить индексы для всех комбинаций отделов и пользователей?
departments,0,users,0
departments,0,users,1
departments,1,users,0
departments,1,users,1
...
Это безумная идея: их получится слишком много. Мы не знаем точно, сколько всего отделов и пользователей, и любое предположение разойдется с реальностью.
Чтобы решить проблему, построим индекс GIN для всего поля doc. Вот как он выглядит:
create index if not exists
idx_applications_doc_gin_jsonb_ops
on applications using gin
(doc jsonb_ops);
Когда индекс GIN применяют к jsonb, можно указать опции. По умолчанию это
переменная jsonb_ops, и выше мы задали ее явно. Postgres предлагает второй
набор названием jsonb_path_ops; в чем его преимущество мы узнаем позже.
Индекс GIN устроен сложнее btree. Он обходит документ и строит множество пар путь -> значение. Например, документ:
{"users": [{"name": "Ivan"},
{"name": "Huan"}]}
раскладывается на следующие пары:
users.0.name = Ivan
users.1.name = Huan
Каждая пара индексируется, то есть связывается с документом, в котором находится. Пары упорядочены, за счет чего можно эффективно пропустить часть из них при сканировании.
Новый индекс поддерживает несколько операторов, которых не было в
btree. Например, с помощью знака вопроса (?) легко проверить, был ли в
документе определенный ключ:
select id from applications
where doc ? 'application_id'
limit 10;
В нашем случае запрос не особо полезен, потому что все заявки содержат поле application_id. Однако вы можете пометить документы флагами, чтобы позже обработать их, например так:
update applications
set doc['__migrate__'] = to_json(true)
where ...;
update applications
set ...
where doc ? '__migrate__';
Поиск по наличию поля работает быстрее, чем по значению.
Операторы ?| и ?& принимают массив строк. Первый возвращает истину, если
документ содержит хотя бы один ключ, а второй – если все. Эти проверки, хоть и
требуются редко, полезны в особых случаях, например поиске ошибочно заполненных
документов. Запрос ниже вернет заявки, где нет хотя бы одного из перечисленных
полей:
select id from applications
where doc ?& '{application_id,status,amounts}'
limit 10;
Наиболее важен оператор @> (правое мороженое) – рекурсивное вхождение одного
документа в другой. Напомним, мы изучили его во второй главе, когда только
знакомились с JSON. Чтобы найти в недрах departments пользователя user_123 в
роли “lead”, составим следующий запрос:
select
id,
jsonb_pretty(doc['departments'])
from applications
where doc @> $${
"departments": [{"users": [{
"email": "user_123@test.com",
"role": "lead"
}]}]
}$$::jsonb
limit 100;
Документ справа от @> является минимальным подмножеством заявки. У обеих
сторон есть поле “departments”, и далее их значения проверяются
рекурсивно. Алгоритм переходит к массивам, элементам, объектам с полями “users”
и так далее.
План запроса показывает, что мы попали в индекс; время выполнения составило 28 миллисекунд.
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=189.50..524.92 rows=100 width=48) (actual time=26.982..28.416 rows=100 loops=1) │
│ -> Bitmap Heap Scan on applications (cost=189.50..33731.03 rows=10000 width=48) (actual time=26.980..28.406 rows=100 loops=1) │
│ Recheck Cond: (doc @> '{"departments": [{"users": [{"email": "user_123@test.com"}]}]}'::jsonb) │
│ Heap Blocks: exact=100 │
│ -> Bitmap Index Scan on idx_applications_doc_gin_jsonb_ops (cost=0.00..187.00 rows=10000 width=0) (actual time=26.623..26.623 rows=3000 loops=1) │
│ Index Cond: (doc @> '{"departments": [{"users": [{"email": "user_123@test.com"}]}]}'::jsonb) │
│ Planning Time: 91.763 ms │
│ Execution Time: 28.460 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Индекс GIN и “мороженное” решают проблему массивов: можно найти элемент, зная его значение, а не позицию. Это выгодно отличает GIN от btree, который требует явного пути к значению и поэтому несовместим с массивами.
Поскольку индекс охватывает весь документ, это сказывается на его объеме. До сих пор мы не поднимали этот вопрос, но давайте проверим: сколько места занимает таблица и ее индексы? Нам помогут следующие запросы:
SELECT pg_size_pretty(pg_total_relation_size('applications'))
as app_table_size;
┌────────────────┐
│ app_table_size │
├────────────────┤
│ 4877 MB │
└────────────────┘
SELECT pg_size_pretty(pg_total_relation_size('idx_applications_created_at'))
as created_at_index_size;
┌───────────────────────┐
│ created_at_index_size │
├───────────────────────┤
│ 50 MB │
└───────────────────────┘
Хотя таблица и занимает почти пять гигабайт, индекс bree весьма компактный: всего 50 мегабайт, разница два порядка. Если же выполнить запрос для индекса GIN, результат удивит: полтора гигабайта!
SELECT pg_size_pretty(pg_total_relation_size('idx_applications_doc_gin_jsonb_ops'))
as gin_json_index_size;
┌─────────────────────┐
│ gin_json_index_size │
├─────────────────────┤
│ 1492 MB │
└─────────────────────┘
Чем больше индекс, тем выше его накладные расходы. Если очистить таблицу заявок и запустить генерацию заново, она продлится в разы дольше, потому что каждый документ индексируется целиком.
Одно из решений в том, чтобы индексировать не весь документ, а его подмножество. Поскольку пользователь находится в недрах поля “departments”, проиндексируем только это поле:
create index if not exists
idx_applications_doc_departments_gin_jsonb_ops
on applications using gin
((doc['departments']) jsonb_ops);
Запрос тоже изменится: слева от @> находится не документ, а та часть, что мы
индексируем. В свою очередь документу справа не понадобится поле “departments”:
это будет массив.
select
id,
jsonb_pretty(doc['departments'])
from applications
where doc['departments'] @> $$[
{"users": [{
"email": "user_123@test.com",
"role": "lead"
}]}
]$$::jsonb
limit 100;
Запрос выполнится быстрее: 22 миллисекунды против 28.5. В очередной раз мы подтвердили тезис, что чем меньше индекс, тем эффективней запрос. Если измерить пространство, получим результат на порядок меньше, чем в случае с полной индексацией:
SELECT pg_size_pretty(pg_total_relation_size('idx_applications_doc_departments_gin_jsonb_ops'))
as gin_json_deps_index_size;
┌──────────────────────────┐
│ gin_json_deps_index_size │
├──────────────────────────┤
│ 237 MB │
└──────────────────────────┘
Общее правило таково: индексируйте только ту часть документа, которая действительно того требует. В нашем случае это поле “departments”: в нем встречаются массивы, а те плохо сотрудничают с btree. Для остальных полей обычно хватает btree и триграмм.
Оператор @> возвращает истину или ложь, поэтому не может быть использован для
сортировки. Если выборка сортируется, Postgres оценивает, какой из двух
вариантов предпочесть. Первый — обойти записи по индексу сортировки и
отфильтровать их при помощи @>. Индекс GIN при этом использоваться не
будет. Второй вариант — обойти индекс GIN, получить строки и отсортировать их в
памяти. Выбор зависит от того, насколько точен индекс GIN. Если в запрос
одновременно содержит сортировку и оператор @>, обязательно проверьте план.
Индекс GIN поддерживает еще два оператора: поиск по пути с предикатом @@ и
поиск с подзапросом @?. Обе функции крайне мощны и представляют собой вещь в
себе. Чтобы познакомиться с ними, нам понадобится язык выражений JSON Path. Это
весьма обширная тема, и мы придержим ее для отдельной главы.
В числе прочего мы упомянули технику под названием “вычисляемые столбцы”. Их можно использовать как замену триггерам, а также в других нетривиальных ситуациях. Как и в случае с JSON Path, тема требует вводных данных, поэтому мы рассмотрим ее в следующей главе.
Нашли ошибку? Выделите мышкой и нажмите Ctrl/⌘+Enter