Как гарантируется порядок данных в столбцах для индекса BRIN?

Question

[artemfisher]

Добрый день!

Пункт 11.2.6 документации гласит:

BRIN-индексы (сокращение от Block Range INdexes, Индексы зон блоков) хранят обобщённые сведения о значениях, находящихся в физически последовательно расположенных блоках таблицы. Поэтому такие индексы наиболее эффективны для столбцов, значения в которых хорошо коррелируют с физическим порядком столбцов таблицы.

Пункт 7.5 документации гласит:

После того как запрос выдал таблицу результатов (после обработки списка выборки), её можно отсортировать. Если сортировка не задана, строки возвращаются в неопределённом порядке. Фактический порядок строк в этом случае будет зависеть от плана соединения и сканирования, а также от порядка данных на диске, поэтому полагаться на него нельзя. Определённый порядок выводимых строк гарантируется, только если этап сортировки задан явно.

Как понять расположены ли данные в таблице физически последовательно и можно ли применять индекс BRIN? Если даже в самой документации написано, что гарантии никакой нет относительно сортировки, если не применять ORDER BY.

Comments

[Akina]

Пункт 11.2.6 документации гласит:
Пункт 7.5 документации гласит:

Какое отношение имеет одно к другому?

Как понять расположены ли данные в таблице физически последовательно и можно ли применять индекс BRIN?

Тот же вопрос.

Answer 1

[galaxy]

Пункт 7.5 говорит о порядке строк в результатах конкретного запроса, ни к физическому порядку, ни к BRIN индексам это отношения не имеет.

BRIN индекс вы всегда сможете применить (в смысле создать). Будет ли он использоваться, определяет планировщик, исходя из статистики данных и структуры запроса. Будет ли такой индекс эффективен, зависит действительно от данных.

Наверно, лучше описать простыми словами, как работает BRIN, а вы уж сами думайте дальше.

BRIN хранит небольшую выжимку об нескольких последовательных (в смысле размещения на диске) блоках данных таблицы. Поэтому он, как правило, очень эффективен по объему: по сравнению с B-tree и другими индексами он очень невелик.
При запросе такой индекс отвечает на вопрос: совместима ли выжимка с условиями запроса, т.е. могут ли в блоках данных на диске быть подходящие под запрос строки. Например, для сортируемых типов данных индекс может хранить MIN и MAX значения колонки в пределах блоков, которые описывает выжимка.
Индекс может выдавать ложноположительный ответ, но не может ложноотрицательный. Допустим, его спрашивают: "найди строки с x = 5", он видит у себя группу блоков с MIN=3 и MAX=20 и отвечает: "тут может быть строка с x = 5". А для группы с MIN=13 и MAX=88, например, он ничего не ответит, т.к. 5 там содержаться не может. СУБД, получив данные по такому индексу, обязана перепроверить строки на предмет ложноположительных результатов.

Такой индекс лучше всего работает на данных, которые физически определенным образом расположены на диске (например, отсортированы по нужной колонке). Данные на диске лежат обычно в том порядке, как их положили (если потом их активно не стирали и перезаписывали).
Допустим, если у вас есть архивная таблица с колонкой, содержащей дату создания, или с автоинкрементым полем, BRIN по этим полям будет эффективен.

Если данные не отсортированы, BRIN будет выдавать слишком много блоков-кандидатов, в которых будет, скорее всего, сравнительно немного подходящих строк.