Какой тип индекса лучше всего выбрать для следующих данных?

Question

[beduin01]

У меня есть столбец со статусами: success, failed, pending.
Вопрос -- какой тип индекса лучше выбрать?

Answer 1

[ky0]

Вы представляете себе обход бинарного дерева, в котором нужно выбрать треть строчек таблицы? Он, конечно, будет быстрее полного сканирования - но гораздо больше пользы будет от индекса по какому-нибудь более различающемуся полю или сочетанию полей.

Comments

[beduin01]

А как лучше выбрать сочетание полей правильное для составного индекса?

[Melkij]

Он, конечно, будет быстрее полного сканирования

Медленнее в общем случае. Треть таблицы быстрее будет прочитать seqscan'ом.
index scan'ом читать треть таблицы - это много скакать (по памяти или random i/o) сперва для индекса, потом для каждой найденной в индексе версии строки лезть в таблицу. Много беготни. Обычно база не будет использовать такой индекс.

[beduin01]

Melkij, и как быть?
Вот у меня данные поля (50 млн записей):
id date file_name section_name status

Запросы на выборку используют все столбцы. Как быть? Какие индексы ставить?

[Melkij]

какие именно запросы? Каково распределение данных?

[beduin01]

Melkij, запросы просто просто на выборку file_name по разным условиям. Иногда нужно делать COUNT(*).
Даты пишутся более менее последовательно по возрастанию. Про распределение данных не совсем понял.

[Melkij]

какие именно запросы? Что такое "разные условия"?
"where status = 'pending' order by date limit 20" и всегда только pending это совсем не то же самое, что поиск по любому статусу.
под "where file_name = ?" - полностью другая история и полностью другой индекс

По распределению данных: id уникален? А file_name? уникален? Сколько из ваших 50млн строк в каком статусе типично числятся?

Индексы делаются всегда под конкретные задачи.

[beduin01]

Обычно запросы сводятся к выбору файлов для обработки по условию:
WHERE date>= '2018' AND data <= '2020' and status = NULL LIMIT 1000

Поиск по имени файла не производится. file_name не уникален

ID НЕ уникальный.

В процессе обработки у данных статус меняется т..е. понемногу status будет из NULL превращаться в succcess. Но возможны случаи когда он будет выставлен в pending или failed

[Melkij]

по условию:
WHERE date>= '2018' AND data <= '2020' and status = NULL LIMIT 1000

Ну это просто, пустой результат всегда без обращения к таблице за некорректное сравнение с NULL.

Если поправить на is null, то

btree(date) where status is null

будет разумной точкой для этого запроса.

[beduin01]

Melkij, да я опечатался, разумеется IS NULL.
А составные индексы тут как-то помочь могли бы?

[Melkij]

да, могли бы. Если бы не существовало частичных индексов - то сюда бы подошёл btree(status, date). Но такой индекс будет существенно больше частичного.

[beduin01]

Melkij, частичные в данном случае те где есть NULL?

[Melkij]

частичные - это вот эти: https://www.postgresql.org/docs/current/indexes-pa...