Привет, Хабр!

Когда мы говорим о PostgreSQL и оптимизации запросов, большинство тут же вспоминает B-Tree индексы, GIN, GiST и так далее. Но вот BRIN иногда остается в тени, хотя в некоторых сценариях он способен творить чудеса с производительностью, особенно когда ваши таблицы размером с космический лифт, а места на диске жалко. Сегодня я расскажу, как именно работает BRIN.

BRIN расшифровывается как Block Range Index. Представьте обычный индекс, который пытается знать все и о каждом ряду. BRIN — это противоположность. Он хранит сводную информацию о группах страниц (так называемых «зонах»), а не о каждой строке. Проще говоря, вместо того, чтобы индексировать каждую запись, BRIN делит таблицу на блоки — например, каждые 32 страницы считаются одной зоной — и для этой зоны хранит минимальную и максимальную величину столбца (или другую обобщённую характеристику, в зависимости от класса операторов). Когда вы делаете запрос, BRIN быстро определяет, какие зоны могут содержать нужные данные, и «отбрасывает» все остальное. Неточно? Да, ведь внутри зоны придется все равно фильтровать строки. Но стоимость фильтрации намного меньше, чем полный проход по всей таблице.

Когда BRIN — хороший выбор?

  1. Огромные таблицы. Допустим, есть таблица с миллионами, а то и миллиардами строк. Классический B-Tree индекс начинает раздуваться и занимает большое пространство, тогда как BRIN остается микроскопическим.

  2. Естественная корреляция данных. Например, у вас есть столбец order_date в таблице заказов. Вполне логично, что старые заказы в начале таблицы, новые — в конце. Если вы ищете заказы за конкретный месяц, BRIN определит диапазон зон, где могут быть эти даты.

  3. Экономия дискового пространства. 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 опирается на так называемые классы операторов, чтобы понять, как сохранять сводные данные. Основных подхода два:

  1. Minmax-классы: хранят минимальное и максимальное значение для зоны. Отлично подходят для типов, где есть естественный порядок: числа, даты, строки.

  2. 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 по методу „борща“». Регистрация доступна по ссылке. А на странице курса вы можете посмотреть записи ранее прошедших вебинаров.

Комментарии (1)


  1. mmandaliev
    11.12.2024 15:22

    Такая exadata на минималках. Не рассказали, что будет если поле все-таки не NOT NULL...