Ну все, грустим, btree нам не подошёл. Можно ещё попробовать bloom

и правда грустим, почитаю о bloom. спасибо, за помощь, в любом случае)

Незачем что. Когда будете экспериментировать, попробуйте построить индекс по date_trunc('year', date), чтобы можно было использовать условие =. Ну и отпишитесь, что получится)

тут правда говорили, что where column in (...) это очень плохо, но в тот раз на таком же запросе разницы между ними особой не было

а почему все-таки выбрать 300 записей, по id'ам с 2.8 млн таблицы так долго или это нормально?

А у вас toast полей нет?

а как вы удостоверяетесь, что это страницы с диска, а не с кеша?

Вообще если вы хотите именно 100% с диска, сейчас устрою проверку

Важный уточняющий вопрос. Пользователи, которые не получали никаких новых баллов в amount в отборе ближайших участвуют?

https://blog.2ndquadrant.com/tables-and-indexes-vs-hdd-and-ssd/ вот сравнение, на небольшой таблице разница в разы, но не в сотни раз. а какая у вас структура таблицы? хочу такую же развернуть и потестить то же самое

Я уже ушёл от компьютера, часа через три проведу ещё полноценный тест, сброшу вам результаты, таблицу и запросы.

Я понимаю, что статистику ближайших надо будет строить часто и быстро. Поэтому нужен кеш и я предлагаю создать таблицу weekly_deals и использовать ее как регистр оборотов. План примерно такой: Таблица deals: 1. Покрывающий btree индекс date_trunc('day', date), user_id, amount. Опционально партицирование через pg_pathman Таблица weekly_deals (userid, amount): 2. Аудит в таблицу weekly_deals_audit через триггер на строку 3. Транзакция старт 4. Блокировка на запись deals 5. Вставить в weekly_deals агрегацию из deals за 6 дней (без сегодня) 6. Построить gist индекс по amount (create extension) 7. На deals добавить построчный триггер обновления amount для каждой новой вставки 8. Транзакция коммит 9. Регламентное ежедневное задание, удаляющее из deals_weekly данные за самый старый день Ну и ищите ближайших в weekly_deals через gist

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

То есть неделя назад - это не начало дня, а ровно 24*7 часов назад, секунда в секунду

Может в сторону хранимых процедур посмотреть, сделать логику работы с курсором например

Ну тут все равно нужно отделять холодные данные, которых наверняка десятки/сотни гигабайт в сторону и работать только с тёплыми.

Курсоры плохо по производительности обычно, а хранимки - да, хорошо. Но куда их пристроить?

Пока не знаю :)

Denis А все так, почему materialized view плохое решение? Ведь это та же самая таблица, проблема только в том, что её нужно полность сбрасывать. Вот я и буду раз в минуту её сбрасывать

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

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

там сложнее, придеться все таблицы смещать на один день