Есть ли возможность в postgres сделать индекс для count запросов?

Question

[devalone]

Цель - сделать быстрыми запросы вида

`SELECT COUNT(*) FROM table WHERE column > value AND column < value2`

в частности - строить графики распределения записей по дате создания и другим полям, сейчас такие запросы на таблице размеров в 60 млн записей выполняются невероятно долго(до часа, т.к. постгрес перебирает все записи в индексе, что удовлетворяют условию).

Теоретически, нет проблем хранить количество в индексе, например в бинарном дереве и тогда выбор `WHERE timestamp > N AND timestamp < M` будет занимать log(N), что очень хорошо.

Есть ли возможность как-то сделать такие вопросы быстрыми, пускай и не максимально точными?

Answer 1

[SanSYS]

Как написали выше - желательно такое аггрегировать заранее - в фоне

Но триггеры с записью для этого не оч подходят, имхо, т.к. каждый раз придётся дописывать код записи, а не новые привычные всем запросы
Для PostgreSQL существует расширение PipelineDB, как им пользоваться можете почитать тут https://habr.com/ru/post/432512/, это похоже именно на то, что вам нужно

А вообще, если данные растут инкрементально, то может стоит рассмотреть индекс BRIN https://habr.com/ru/company/postgrespro/blog/346460/ ?
Весить он будет мало, а работать - вполне достойно, но доку читать внимательно
И.. 60 лямов записей ни о чём не говорит, без информации о размере таблицы и её структуре. Связано с особенностями хранения/чтения

Или ещё вариант - пробовать столбцовые БД ;)

Comments

[devalone]

Спасибо, почитаю.

> И.. 60 лямов записей ни о чём не говорит, без информации о размере таблицы и её структуре. Связано с особенностями хранения/чтения

Уже больше 70 и каждый день добавляется 50 - 100 тысяч новых записей, при этом старые обновляются редко. Данные - посты(5.7 ляма) и комментарии(73 ляма) с одного сайта и вся таблица комментариев сейчас занимает 38Gb.

> Или ещё вариант - пробовать столбцовые БД ;)

не уверен, что подойдёт для моей задачи)

Answer 2

[rPman]

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

p.s. индексы там и так используются, единственное, попробуйте вместо count(*) использовать count(индексируемое поле, используемое в where)

Comments

[devalone]

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

Такое себе решение.. А если запросы могут быть постоянно разными и всё, что их объединяет - это то, что они используют count по какому-то проиндексированному полю.

> p.s. индексы там и так используются, единственное, попробуйте вместо count(*) использовать count(индексируемое поле, используемое в where)

Да он и без явного указания понимает, в explain есть "Bitmap Index Scan", но это не делает запрос сильно быстрее, он всё равно не хранит в своём индексе количество записей, иначе запрос бы не занимал так много времени.

[rPman]

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

sql базы данных не волшебная пилюля, везде приходится изворачиваться и ничего лучше кеша не придумали.

p.s. вы можете хранить как промежуточные значения так и конечные результаты всех ваших запросов, это зависит от вас и характера этих запросов и их использования

p.p.s. есть еще table partitioning (в т.ч. и автоматический) когда вы делите вашу базу на части по условию (например у вас в where условие по времени и к примеру 99% запросов падают на последний год, а записи за предыдущие года очень редко появляются в запросе). Понятно, если вы делаете это вручную (в именах таблиц например), вам придется учитывать это и в запросах, а так же создавать новые таблицы (для новых значений) но это может позволить снизить нагрузку на порядок, так как основной поток запросов пойдет в маленькую таблицу (за текущий период) и не будет затрагивать все записи.

[devalone]

rPman,
> он по определению должен будет пройтись по записям (умноженное на логарифм от их количества или от общего количества, в зависимости от типа индекса), количество которых будет считать, так что нет.

Почему должен? Если в индексе хранится значение, то нет.

> p.p.s. есть еще table partitioning (в т.ч. и автоматический) когда вы делите вашу базу на части по условию (например у вас в where условие по времени и к примеру 99% запросов падают на последний год, а записи за предыдущие года очень редко появляются в запросе). Понятно, если вы делаете это вручную (в именах таблиц например), вам придется учитывать это и в запросах, а так же создавать новые таблицы (для новых значений) но это может позволить снизить нагрузку на порядок, так как основной поток запросов пойдет в маленькую таблицу (за текущий период) и не будет затрагивать все записи.

Как я понимаю, делить можно только по одному полю? Мне нужно делать запросы по куче разных полей.

[rPman]

devalone, делите как угодно, партицирование это как индексирование хешем, которое вы вручную реализуете.

например у вас есть поле A и поле B, оба позволяют разделить выборку на пополам, соответственно у вас 4 таблицы table_AL_BL, table_AG_BL, table AL_BG и table_AG_BG, где L и G соответственно группа меньше и больше порогового значения.

соответственно у вас запросы должны либо генерироваться на лету (подставляться имена таблиц в соответствии со значениями интервалов) либо вы должны заранее нагенерировать всевозможные комбинации и инициализировать запросы в драйвере базы данных (prepare для sql запросов иногда дает небольшой прирост к скорости)

p.s. например понятие архивная база данных, в которую записи физически переносятся, когда необходимость в оперативном доступе к ним отпадает, и есть частный случай партицирования

Answer 3

[TheRonCronix]

1. Выше верно указали, что нужны агрегаты. Агрегаты могут быть частичными. Если count у вас без distinct, то показатель будет аддитивным и проблем с суммированием по агрегатам быть не должно.
2. Раз у вас таблица так растет нужно ее секционировать. Скорее всего по дате.