минус семь дней от текущей даты

в принципе уже сразу можно для этой выберки по последней недели применить btree_gist по времени

но дальше все равно нужно или представление или materialized view

Тут нужно поиграться с индексом по нескольким колонкам. У вас сколько записей в неделю (порядок?)

есть только расчетные значения. 100000 вставок в день. Строки не апдейтится, а только добавляется новая инфа

Ага, порядка миллиона. Я поэкспериментирую и отпишусь)

Да, спасибо, будет интересно увидеть твое решение.

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

Да, спасибо, будет интересно увидеть твое решение.

Я понимаю, что статистику ближайших надо будет строить часто и быстро. Поэтому нужен кеш и я предлагаю создать таблицу 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

Из недостатков - я не доверяю всяким конструкциям типа регистров оборотов, в них всегда может пойти несогласованность данных. Плюс, в момент создания у вас будет простой, наверное секунд на 15. И главное, когда будете первоначально заполнять таблицу weekly_deals, убедитесь, что используете покрывающий индекс - у меня на 2 млн в таблице за две недели агрегация за неделю строилась на покрывающем индексе 4 секунды.

А почему trans_date не участвует в поиске по индексу, а только в sequence scan? Может, ее стоит добавить в покрывающий индекс?

Вам нужен (trans_date, payer_edrpou) по двум колонкам

Вам нужен (trans_date, payer_edrpou) по двум колонкам

но там же условии payer OR recipt. или поиск будет сначала по двойному индексу, а потом только фильтрация по recipt?

Никакой разницы вроде

но там же условии payer OR recipt. или поиск будет сначала по двойному индексу, а потом только фильтрация по recipt?

А вот recipt я не заметил, это все портит)

А вот recipt я не заметил, это все портит)

этот OR не дает расслабиться уже долгое время))

но там же условии payer OR recipt. или поиск будет сначала по двойному индексу, а потом только фильтрация по recipt?

но этот вариант тоже протестирую сейчас

неа, этот индекс не используется, запрос строится так же само

неа, этот индекс не используется, запрос строится так же само

Не, с or btree индекс не работает. Яникогда не пользовался, но слышал по bloom,которому вроде безразлично на порядок колонок - возможно что-то на нем можно поймать. Но он вроде умеет только = и для интов с текстом

на самом деле сфинкс очень быстро делает такие запросы и если использовать базу толька для запросов по списку id'шек (и разбить pathman'ом таблицу по id'кам, а не датам) то будет сносно. правда мне это кажется не совсем полноценным, хотя если бы база была на SSD или хотя бы индексы, то было бы тоже нормально

Кстати, у меня мысль! Можно разбить ваш запрос на два и склеить их union. В первом будет покрывающий индекс trans_date + payer_edrpou, а во втором - trans_date + recipt_edrpou

это как раз лучшее, что удалось сделать с mysql и что в итоге работает за 5-7 секунд))

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

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

Давайте посмотрим план, мне интересно)

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

Нет, интересен план и оценки.

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

Так создайте тестовую базу с только родительской таблицей и партицией за 16 год. В ней 2.8 млн...По ней индекс пулей построится, а нам ничего другого для тестов и не надо

На худой конец просто нагенерируйте туда рандомных данных

Можно вообще просто одну дочернюю таблицу, нужно же понять, даёт ли два покрывающих индекса преимущество

да, логично, так и сделаю

И в индексах важен порядок колонок. Мне кажется, будет адекватно работать только порядок (payer_edrpou, trans_date) и (recipt_edrpou, trans_date)

хм, а я создал как раз наоборот, что же, тоже сравним. сам запрос explain analyze select * from ((select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and payer_edrpou='00013480' limit 300) union (select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and recipt_edrpou='00013480' limit 300)) t join transactions_16 on t.id=transactions_16.id limit 300; норм или перемудрил?

Запрос ок, а результат печальный. Давайте наоборот колонки в индексе проверим?

Хотя погодите, запрос не норм. Я не понял, зачем бы подзапросом выбираете id и присоединяете по нему таблицу

explain analyze (select * from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and payer_edrpou='00013480' limit 300) union select * from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and recipt_edrpou='00013480' limit 300; вот прямая версия

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

Ладно, а можно только первую часть? Я не понимаю, почему не использует планировщик индекс по обоим колоннам

Хотя погодите, запрос не норм. Я не понял, зачем бы подзапросом выбираете id и присоединяете по нему таблицу

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

А можно посмотреть созданный индекс? Я в замешательстве)

Вообще, с таблицы убрать все индексы, кроме двойного?

а как его посмотреть?) хотя что-то тут и правда неладное явно

сейчас порядок поменяю

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

пересоздал, кстати создались они вообще влет

в другом порядке это занимало минуту +-

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

да, сейчас

новые все равно не использует, кстати

а, это я на родительской пустой таблице создал, сори

новые все равно не использует, кстати

Да ладно, он издевается. Давайте сделаем vacuum analyze и freeze. А если и тут не будет использовать, то выставьте set enable_seqscan = false

а, это я на родительской пустой таблице создал, сори

Аааа)))

создал нормально, но итог тот же) CLUSTER index trans_date дропнул, дропну сейчас другие и еще раз запущу

Сделайте vacuum freeze на партицию

запустил. это 5 сек заняло считать 300 строк с диска, выходит или вот это Recheck Condition просто seq scan делает, не пойму

Он продолжает делать перепроверку в таблице после обновления карты видимости? Странно. Точно vacuum freeze на патрицию отработал?

Ну это норма, он карту видимости вам делал)

ERROR: S client not available

так он норм сработал же?

А, я понял. Уберите в запросе * и замените на truns_date

Чтобы он только в индекс лез. Если сработает, добавим в индекс ещё id

а хотя не лучше, после вакуум то же время

А, я понял. Уберите в запросе * и замените на truns_date

сейчас это сделаю

Ненене, там в таблицу лезли, так как мы запрашивали все поля из таблицы, а их не было в индексе. Нужно создать индекс по трём колонкам - времени, recip и id. И в селекте выбирать id

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

Индекс - это отдельный файл. Если постгрес может все данные вытащить из него, он берет только оттуда и не лезет за каждой строкой в файл таблицы. Потом, так как в индексе нет версий строк, он просматривает карту видимости. И те строки из индекса , которые видят не все транзакции перепроверяются в таблице

а с доступов по id разве не то же самое, я так понимаю, это такой же индекс?

а с доступов по id разве не то же самое, я так понимаю, это такой же индекс?

Не понял)

ну т.е. запрос c where id=N использует такой же индекс, как и обычные индексированные колонки?

Да, абсолютно. Лежит себе отдельный файл с индексом id и оттуда вытаскивать можно только значения id

Ну хорошая новость, мы не вылезти из индекса) Плохая, что ищем медленно)) Давайте поменяем колонки местами

Первые две

давайте, почему бы, собственно, и нет

Первые две

создал еще два таких индекса, но планировщик их не использует, думаете, стоит удалить те, что использует?

Да, определенно

Грустно... покрывающие индексы нас не спасли.

Ну тут для чистоты эксперимента нужен ещё просто индекс по id для join, наверное

Но в целом все ясно с результатами

не понимаю, почему это не работает быстро, дается около 300 готовых id'шек, но планировщик циклы в цыкле делает. проверю просто по списку id как будет

Ну тут для чистоты эксперимента нужен ещё просто индекс по id для join, наверное

explain analyze select * from ((select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and payer_edrpou='00013480' limit 300) union (select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and recipt_edrpou='00013480' limit 300)) t join transactions_16 on t.id=transactions_16.id limit 300; такой запрос? тоже тестил, ~9 сек на payer,date,id

explain analyze select * from ((select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and payer_edrpou='00013480' limit 300) union (select id from transactions_16 where trans_date>='2016-12-15' and trans_date<='2017-01-14' and recipt_edrpou='00013480' limit 300)) t join transactions_16 on t.id=transactions_16.id limit 300; такой запрос? тоже тестил, ~9 сек на payer,date,id

Давайте добавим индекс по id и ещё раз план посмотрим?

Давайте добавим индекс по id и ещё раз план посмотрим?

это payer,date,id всмысле?

Нет, ещё отдельный один

Просто id

а, понял