Привет, Хабр!
Когда мы говорим о PostgreSQL и оптимизации запросов, большинство тут же вспоминает B-Tree индексы, GIN, GiST и так далее. Но вот BRIN иногда остается в тени, хотя в некоторых сценариях он способен творить чудеса с производительностью, особенно когда ваши таблицы размером с космический лифт, а места на диске жалко. Сегодня я расскажу, как именно работает BRIN.
BRIN расшифровывается как Block Range Index. Представьте обычный индекс, который пытается знать все и о каждом ряду. BRIN — это противоположность. Он хранит сводную информацию о группах страниц (так называемых «зонах»), а не о каждой строке. Проще говоря, вместо того, чтобы индексировать каждую запись, BRIN делит таблицу на блоки — например, каждые 32 страницы считаются одной зоной — и для этой зоны хранит минимальную и максимальную величину столбца (или другую обобщённую характеристику, в зависимости от класса операторов). Когда вы делаете запрос, BRIN быстро определяет, какие зоны могут содержать нужные данные, и «отбрасывает» все остальное. Неточно? Да, ведь внутри зоны придется все равно фильтровать строки. Но стоимость фильтрации намного меньше, чем полный проход по всей таблице.
Когда BRIN — хороший выбор?
Огромные таблицы. Допустим, есть таблица с миллионами, а то и миллиардами строк. Классический B-Tree индекс начинает раздуваться и занимает большое пространство, тогда как BRIN остается микроскопическим.
Естественная корреляция данных. Например, у вас есть столбец
order_date
в таблице заказов. Вполне логично, что старые заказы в начале таблицы, новые — в конце. Если вы ищете заказы за конкретный месяц, BRIN определит диапазон зон, где могут быть эти даты.Экономия дискового пространства. BRIN-индексы невероятно компактны. Они хранят не все значения, а только агрегированные метаданные по зонам.
Минусы
BRIN — неточный индекс. Он скажет: «Тут могут быть нужные данные!», но внутри зоны придётся проверить каждый кортеж.
Если данные в столбце разбросаны хаотично, выгоды от BRIN будет мало. Он все равно не сможет эффективно отсеивать зоны.
Как создать BRIN-индекс?
Немного SQL:
CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);
Параметр pages_per_range
определяет, сколько страниц попадает в одну зону. По дефолту это 128 страниц, но можно подобрать другое число. Чем меньше pages_per_range
, тем точнее индекс, но больше размер индекса. Чем больше — тем компактнее индекс, но менее точен. И тут надо экспериментировать.
Есть еще неплохие опции. К примеру, можно использовать autosummarize
через расширения, чтобы не вручную вызывать brin_summarize_new_values
. Это обновит сводные данные автоматически.
BRIN-операторы: minmax, inclusion
BRIN опирается на так называемые классы операторов, чтобы понять, как сохранять сводные данные. Основных подхода два:
Minmax-классы: хранят минимальное и максимальное значение для зоны. Отлично подходят для типов, где есть естественный порядок: числа, даты, строки.
Inclusion-классы: для более сложных типов данных, например, геометрия (box), IP-сети или диапазоны. Индекс хранит «объемное» представление, например bounding box или объединение диапазонов.
Чем интересны inclusion-классы? Тем, что они могут вычленить информацию о более сложных структурах данных — например, определить, что у нас в зоне могут быть объекты, пересекающие некую область.
Таблица встроенных классов операторов BRIN
Для каждого типа данных свой класс.
Имя |
Тип данных |
Операторы |
---|---|---|
abstime_minmax_ops |
abstime |
|
int8_minmax_ops |
bigint |
|
bit_minmax_ops |
bit |
|
varbit_minmax_ops |
bit varying |
|
box_inclusion_ops |
box |
`<< &< && &> >> ~= @> <@ &< |
bytea_minmax_ops |
bytea |
|
bpchar_minmax_ops |
character |
|
char_minmax_ops |
"char" |
|
date_minmax_ops |
date |
|
float8_minmax_ops |
double precision |
|
inet_minmax_ops |
inet |
|
network_inclusion_ops |
inet |
|
int4_minmax_ops |
integer |
|
interval_minmax_ops |
interval |
|
macaddr_minmax_ops |
macaddr |
|
name_minmax_ops |
name |
|
numeric_minmax_ops |
numeric |
|
pg_lsn_minmax_ops |
pg_lsn |
|
oid_minmax_ops |
oid |
|
range_inclusion_ops |
любой тип диапазона |
`<< &< && &> >> @> <@ - |
float4_minmax_ops |
real |
|
reltime_minmax_ops |
reltime |
|
int2_minmax_ops |
smallint |
|
text_minmax_ops |
text |
|
tid_minmax_ops |
tid |
|
timestamp_minmax_ops |
timestamp without time zone |
|
timestamptz_minmax_ops |
timestamp with time zone |
|
time_minmax_ops |
time without time zone |
|
timetz_minmax_ops |
time with time zone |
|
uuid_minmax_ops |
uuid |
|
Для большинства типов есть minmax-операции, а для более экзотичных структур — inclusion-операторы. То есть можно индексировать не только простые числа и строки, но и сложные типы. К примеру, box_inclusion_ops
позволит искать объекты в определенной географической области, быстро отсекая зоны, где точно нет подходящих объектов.
Пример оптимизации выборки
Предположим, есть таблица заказов с кучей строк:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id BIGINT NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Зальем кучу данных:
INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
(DATE '2023-01-01' + (RANDOM()*365)::INT),
(RANDOM()*1000000)::BIGINT,
(RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;
Теперь создаем BRIN-индекс:
CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);
date_minmax_ops
говорит: мы будем хранить минимальную и максимальную дату для каждой 64-страничной зоны.
При запросе:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';
План покажет, что PostgreSQL сначала обратится к BRIN-индексу, определит какие зоны могут содержать нужные даты, и пропустит остальное. Если данные приблизительно по дате отсортированы (например, по мере вставки), количество лишних зон будет минимальным.
Тонкости параметров
pages_per_range:
Чем меньше — тем точнее фильтрация (каждая зона меньше, значит меньше мусора), но индекс растет. Чем больше — тем меньше размер индекса, но хуже точность. Нужно тестировать на реальных данных.-
autovacuum и brin_summarize_new_values:
BRIN не всегда автоматом обновляет сводные данные для новых зон. Можно вызывать:SELECT brin_summarize_new_values('orders');
Или настроить
autovacuum
, чтобы этот процесс шел регулярно. В новых версиях можно использоватьautosummarize
опцию через расширения, чтобы автоматизировать это дело. Кастомные классы операторов:
Если стандартных нет, можнго писать свои классы операторов. Это уже хардкор: придется реализовывать C-функции, описывающие, как агрегировать значения, как объединять зоны, как проверять пересечения с запросами. Но зато можно индексировать действительно экзотические структуры.
Заключение
BRIN не заменит B-Tree везде и повсюду. Но в тех случаях, когда данные упорядочены или имеют естественную корреляцию с их физическим размещением, BRIN может дать потрясающую производительность при минимальном размере индекса.
На этом всё. Желаю вам продуктивных поисков оптимальной стратегии индексации!
В завершение приглашаю на бесплатный вебинар «Строим архитектуру DWH по методу „борща“». Регистрация доступна по ссылке. А на странице курса вы можете посмотреть записи ранее прошедших вебинаров.
mmandaliev
Такая exadata на минималках. Не рассказали, что будет если поле все-таки не NOT NULL...