можете подсказать не могу найти в документации ,как в pgbouncer пропускать все бд (чтобы логин и пароль не прописывать в конфиге)

Индексы есть, к сожалению. order by profile_id, create_ts desc сверху добавил - все то же самое :(

ладно, так или иначе, спасибо за помощь! Еще поковыряюсь, появилось пару идей

может их ребилднуть нужно ?

реиндекс таблицы сделал - не помогло. Я подумал, что может 30к строк тестовых данных мало для индекса, и постгрес и так быстро все считает (по analyze 77ms) 🤔 он еще древний, 9.5...

мда, на проде за 13 минут я так и не дождался результата explain...) буду думать

если where profile_id внутрь подзапроса в from пихнуть, сразу после over, то там будет внутренний скан по индексу. Видимо, буду батчами делать просто, с where

Я таки к вам с тем же вопросом по билду и установке расширений.

Не могу понять, как выставить версию постгреса под которую я хочу скомпилироваться? Что-то не гуглится ответ(

То:
> Нужно для каждого profile_id оставить по 10 последних записей по create_ts, одним запросом.

То:

SELECT id -- это запись?
FROM (select id, row_number() over (partition by profile_id order by create_ts desc) as pos from payment) AS partition
WHERE partition.pos <= 500; -- а это десять, да? ;)

Нет бы показать \d payment и настоящий оптимизируемый запрос, с EXPLAIN (ANALYZE, BUFFERS), рассказать про распределение данных и про то, что получилось с другими методами...

> он еще древний, 9.5...

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

для примера я указал 10, по факту там может быть любое число n. Не очень понимаю, как от этого сам запрос поменяется, ну да ладно)

select id - да, запрос настоящий, это нужно для дальнейшего delete, чтобы у каждого профиля осталось только N последних payment.

в \d payment много ненужных для запроса колонок, основные я перечислил - create_ts и profile_id. Оба с индексом. Других индексов нет.

Indexes:
    "payment_pkey" PRIMARY KEY, btree (id), tablespace "secondspace"
    "payment_create_ts_idx" brin (create_ts), tablespace "secondspace"
    "payment_profile_id_idx" btree (profile_id), tablespace "secondspace"

(кстати, только сейчас заметил, что у create_ts brin индекс)

другими способами пока ничего не получилось, запрос без partition by я придумать не смог, упираясь в условие "последние Н для каждого профиля"

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

> Не очень понимаю, как от этого сам запрос поменяется

От этого зависит то, какой метод написания такого запроса будет эффективнее.

> это нужно для дальнейшего delete, чтобы у каждого профиля осталось только N последних payments

А Вам не кажется, что это совершенно другая задача? Т.е. Вы пытаетесь оптимизировать "обратный" запрос.
Т.е. на самом деле Вам нужно удалить (DELETE FROM payment ...) все записи, кроме N последних?

> в \d payments много ненужных для запроса колонок

Тем не менее, там есть другая полезная информация, которой здесь никто так и не увидел. ;)
Например — есть ли у Вас таблица profile_id (туда был бы FK, в норме)?

> Оба с индексом. Других индексов нет.

И оба эти индекса бесполезны для этого запроса (впрочем, какая разница — запрос-то, видимо, не тот).

> запрос без partition by я придумать не смог, упираясь в условие

Запросы с LATERAL проще писать, если есть таблица того, по чём в Вашем запросе partition by (профилей), а если её нет — посложнее. Тем не менее, нужна настоящая задача.

> 9.5 не от хорошей жизни, к сожалению :( в ближайшее время с этим ничего не поделать

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

Индэкс должэн быть, конечно, (profile_id, create_ts) -- и не brin, понятное дело.

Но да, 9 вообще была довольно молодой версией для window functions.

> А Вам не кажется, что это совершенно другая задача? Т.е. Вы пытаетесь оптимизировать "обратный" запрос. Т.е. на самом деле Вам нужно удалить (DELETE FROM payment ...) все записи, кроме N последних?

Мне нужно удалить все записи, кроме последних N для каждого профиля. Если вы сможете рассказать, как это сделать, кроме как DELETE FROM payment WHERE id IN (select id ...), где все упирается в написание именно внутреннего запроса, который я и привел в пример, я буду рад, ибо может я чего не понимаю

> Тем не менее, там есть другая полезная информация

да, есть таблица profile, и profile_id = FK - в принципе, ожидаемо. Что это как-то поможет написанию запроса - я не знал 🤷‍♂️ в следующий раз обязательно укажу.

> А зря, зря... этим стоило бы в первую очередь заняться

видели бы вы состояние бэкэнда в целом... 😅 студия маленькая - DBA понятное дело нет, два бэкэндера, девопс только вышел и занимается в первую очередь деплоем и адекватной автоматизацией (баш скриптики переделывает), поэтому пока работаем с тем, что есть, инкрементально улучшая. Но обновиться надо, с этим никто не спорит. Пока вот хотелось бы табличку разгрузить от старых данных, а то там 510 млн записей, похоже...

>а то там 510 млн записей, похоже...

https://www.youtube.com/watch?v=Oa33Zq7Bn-c

> Мне нужно удалить все записи, кроме последних N для каждого профиля.

А выбираете-то Вы как раз не то, что нужно удалить (а наоборот, то, что нужно сохранить)! ;)

> как это сделать, кроме как DELETE FROM payment WHERE id IN (select id ...)

NOT IN (...), в таком случае.

> Пока вот хотелось бы табличку разгрузить от старых данных, а то там 510 млн записей, похоже...

А удалится-то сколько, примерно?
А, и сколько всего профилей?

> А выбираете-то Вы как раз не то, что нужно удалить

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

> А удалится-то сколько, примерно?

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

> А, и сколько всего профилей?
уникальных профилей 6 миллионов

> А удалится-то сколько, примерно?
а хрен его знает, но поидее дофига. если 510 млн записей на 6 миллионов...

Ещё совет такой: если это однократная операцыя, а вот жэлезо совсем слабое и downtime является проблемой (да, в общем, дажэ если не является на самом деле) -- то есть прямой смысл не выдумывать полный запрос, а перебрать profile на питоне каком-нибудь.
Заодно с progressbarом проблем не будет, плюс можно прерывать в середине это.

Если 510 записей на 6 миллионов, то это в среднем по 100 записей на профиль. Если вы удаляете все старшэ 500-й -- то у вас можэт и ничего не удалиться...

я пока к этому и склоняюсь, но решил спросить - вдруг чего не понимаю. Зато много узнал!

как я и сказал, даже с window function, если делать запрос по N профилей, которые указывать в WHERE, то там индекс и вроде как все хорошо. Т.е в крайнем случае через offset limit по профилям пойду.

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

игре 6 лет, там поидее чем старше игрок, тем больше он будет страдать. 500 - это я чисто игрался, смотрел поменяется ли explain. Пока с конкретным числом не определились, вполне возможно, что это будет нечто маленькое, ближе к 10.

я б выдал топ по`select profile_id, count(1) from payment group by profile_id order by count(1) desc limit 50`, но он уже 10 минут чего-то там вычисляет 🤷‍♂️