Postgres выбирает BTREE вместо индекса BRIN

Я запускаю Postgres 9.5 и играю с индексами BRIN. У меня есть таблица фактов с примерно 150 миллионами строк, и я пытаюсь заставить PG использовать индекс BRIN. Мой запрос:

select sum(transaction_amt), 
       sum (total_amt) 
from fact_transaction 
where transaction_date_key between 20170101 and 20170201

Я создал как индекс BTREE, так и индекс BRIN (значение по умолчанию pages_per_range, равное 128) в столбце transaction_date_key (приведенный выше запрос относится к январю-февралю 2017 года). Я бы подумал, что PG выберет индекс BRIN, однако он идет с индексом BTREE. Вот план объяснения:

https://explain.depesz.com/s/uPI

Затем я удалил индекс BTREE, провел вакуум / анализ таблицы и повторно выполнил запрос, и он действительно выбрал индекс BRIN, однако время выполнения было значительно больше:

https://explain.depesz.com/s/5VXi

Фактически, все мои тесты были быстрее при использовании индекса BTREE, а не индекса BRIN. Я думал, должно быть наоборот?

Я бы предпочел использовать индекс BRIN из-за его меньшего размера, однако я не могу заставить PG его использовать.

Примечание. Я загрузил данные, начиная с января 2017 г. по июнь 2017 г. (определяется с помощью transaction_date_key), поскольку я прочитал, что порядок физических таблиц имеет значение при использовании индексов BRIN.

Кто-нибудь знает, почему PG выбирает индекс BTREE и почему BRIN в моем случае намного медленнее?


postgresql indexing postgresql-9.5
person Ryan    schedule 07.02.2017    source источник
comment
Можете ли вы показать нам результат explain (analyze, verbose, buffers, timing) вместо просто explain (analyze)   -  person a_horse_with_no_name    schedule 08.02.2017
comment
Конечно - запустил их сейчас.   -  person Ryan    schedule 08.02.2017
comment
B-Tree и BRIN: объяснять.depesz.com/s/S3Zp Только BRIN: объяснять.depesz.com/s/Z1A5   -  person Ryan    schedule 08.02.2017
comment
Я сам не использовал индексы BRIN, но, насколько я понимаю, они работают хорошо, только если ваши данные упорядочены на диске таким образом, чтобы соответствовать вашему запросу и индексу BRIN. Вы пробовали запустить КЛАСТЕР на своем столе?   -  person Mad Scientist    schedule 08.02.2017
comment
Извините, что не ходите туда. Я попытался сгруппировать таблицу, но похоже, что кластеризация невозможна с использованием индекса типа BRIN. Я набрал CLUSTER fact_transaction USING i_fact_transaction_transaction_date_key;, но он возвращает ERROR: cannot cluster on index "i_fact_transaction_transaction_date_key" because access method does not support clustering SQL state: 0A000   -  person Ryan    schedule 08.02.2017
comment
Преимущество BRIN заключается в его размере, а не в скорости - Поскольку индекс BRIN очень мал, сканирование индекса добавляет небольшие накладные расходы по сравнению с последовательным сканированием - я не думаю, что это когда-либо предназначалось для быть сопоставимым с BTREE (по производительности). Если возможен BTREE (его размер допустим для приложения), он превосходит BRIN по скорости. - По крайней мере, для тех запросов, где ожидается выбор только небольшого подмножества. Когда выбрана почти вся таблица, BRIN может работать лучше (в этих ситуациях - без BRIN - все равно будет выбрано последовательное сканирование).   -  person pozs    schedule 08.02.2017
comment
Спасибо, @Laurenz Albe, изменение pages_per_range на 64 значительно ускорило выполнение запроса с использованием индекса BRIN. У меня есть дополнительный вопрос, но я создам для него новый пост (относительно эффективного способа выбора размера pages_per_range).   -  person Ryan    schedule 09.02.2017

Ответы (1)


arrow_upward
11
arrow_downward

Кажется, что сканирование индекса BRIN не очень избирательно, оно возвращает 30 миллионов строк, все из которых необходимо перепроверить, на что и тратится время.

Это, вероятно, означает, что transaction_date_key плохо коррелирует с физическим расположением строк в таблице.

BRIN-индекс работает, объединяя диапазоны блоков таблиц (как многие из них можно настроить с помощью параметра хранения pages_per_range, значение которого по умолчанию равно 128). Сохраняется максимум и минимум индексированного значения для диапазона каждого блока.

Таким образом, многие диапазоны блоков в вашей таблице содержат transaction_date_key между 20170101 и 20170201, и все эти блоки необходимо сканировать, чтобы вычислить результат запроса.

Я вижу два варианта улучшения ситуации:

  • Уменьшите параметр хранения pages_per_range. Это увеличит индекс, но уменьшит количество ложноположительных блоков.

  • Сгруппируйте таблицу по атрибуту transaction_date_key. Как вы узнали, для этого требуется (по крайней мере временно) индекс B-дерева в столбце.

person Laurenz Albe    schedule 08.02.2017