month(date) тоже не помогает

а где условие периода?

А где partition by?

А как добавить?

Так и добавить в over перед order by, а ещё лучше доку глянуть

все. спасибо!

Последний заряд телепатии на сегодня истратил.

Переходим к поиску проблем в схеме БД.
Первый документ, который стоит изучить: https://wiki.postgresql.org/wiki/Don't_Do_This
Там вы найдете небольшое количество правил, которые легко учесть при разработке, если вы не используете ORM.
Но на что еще нам стоит обратить внимание?
Покажу 10 из 1.5k правил.

Пара моментов, связанных с производительностью:
1) зеркальные индексы (a,b) и (b,a) или индексы с перестановкой
В общем случае невозможно определить оптимальный порядок колонок в индексе. При анализе стоит учесть все множество запросов в проекте, которые могли бы частично использовать многоколоночный индекс.
Но даже если таких запросов нет, они могут появиться.
В любом случае стоит избегать индексов одного типа, созданных по одним и тем же полям. Это замедлит обновление таблицы, увеличит нагрузку на диск, а значит заафектит производительность всей базы.

2) UNIQUE индекс, включающий поля неограниченного размера
Проверка уникальности сама по себе дорогая операция, которая будет тормозить изменение таблицы. Но кроме того, такой индекс таит в себе еще несколько опасных побочных эффектов.
Во-первых, полная уникальность длинной строки, скорее всего не имеет смысла с точки зрения архитектуры. Обычно нужны будут каки-то функции от текста - lowercase, tsvector, или что-то более сложное.
Во-вторых, в один прекрасный момент можно получить ошибку вот с таким хинтом:
Values larger than 1/3 of a buffer page cannot be indexed.

Поэтому рекомендуется строить такие индексы по какому-нибудь хешу

Несколько архитектурных issues:
3) заданное DEFAULT значение без ограничений NOT NULL
Самый распространенный кейс использования значений по умолчанию - отсутствие необходимости указывать значение при вставке данных.
Скорее всего это означает, что отсутствие значения нас не устроит.
Стоит обозначить это явно.

4) Константные DEFAULT значения для колонок, используемых в уникальных индексах
Может привести к возникновению ошибки при вставке строк без явного указания значений для таких колонок.
Скорее всего в тестах такой кейс не придет никому в голову, а значит найти причину будет сложно.

5) DEFAULT значение для колонок с внешним ключем
Кроме очевидного вопроса "зачем?" возникает 2 опасных кейса при вставке данных:
- значение DEFAULT есть во внешней таблице, но не соответствует бизнес логике. Это ломает связанность данных
- значения нет во внешней таблице, ошибка

6) Внешний ключ по SERIAL/IDENTITY колонкам
Мало того, что это не имеет физического смысла, это довольно вероятно со временем приведет к ошибкам при вставке, когда соответствующие значения во внешней таблице закончатся.

7) Уникальные TIMESTAMP колонки
Не имеет физического смысла в большинстве случаев, т.к. TIMESTAMP имеет точность в 1 микросекунду.
Кроме того, могут возникнуть проблемы при массовой вставке с использованием функций времени, фиксируемых на начало транзакции, например NOW()

8) Уникальный индекс включает другой уникальный индекс
Бессмысленно и имеет побочные эффекты, аналогичные (1)

9) Похожие имена
PostgreSQL приводит к нижнему регистру все имена, указанные без кавычек. Snowflake к верхнему регистру. Но имена в кавычках будут регистро-чувствительными.
"Field", "field" и field (превратится в FILED в Snowflake) будут считаться разными идентификаторами, что может привести к ошибке

10) Неявная генерация имен
Для ограничений, индексов или других объектов, которые не требуют обязательного указания имени при создании, база данных подбирает имена самостоятельно.
В PostgreSQL для этого есть специальный алгоритм. Но если сгенерированные имена объектов похожи, к ним будут добавлены числовые индексы.
А это значит, что важен порядок создания этих объектов.
Поэтому на разных экземплярах базы имена одних и тех же объектов могут быть перепутаны.
Это может привести к очень печальным последствиям, например, при удалении объектов.
Но Snowflake ведет себя иначе. Он генерирует неуказанные явно имена случайным образом. При изменениях схемы такое поведение потребует большого количества внимательности и ручного труда.

немного познавательного из nosingularity на сон грядущий

есть 2 связанные таблицы, citys и regions. поле citys.region ссылается на region.id
как добавить строку в таблицу citys преобразовав при этом название региона в id из таблицы region, если там такое название существует?

Спасибо, посмотрел.

Там ребята советуют scale factor в 5% вкуртить, но delay в 0 для ssd.

Не будет ли это большой проблемой для pg9.5, где нет оптимизации с freeze tuples/pages? Выглядит так, что pg может в диск уйти надолго. У нас nvme.

народ подскажите пожалуйста, нужно обновить postgresql с версии 9.6 до 13 версии, можно ли обновляться напрямую или надо 9.6 - 10 - 11 -12 - 13 ?

Это же классическая задача с подзапросом?)
INSERT INTO table
values ....
where exist (select ....)

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

надо крутить по чуть-чуть и смотреть на производительность... например уменьшили косты или scale factor , смотрим как ведет себя система, в течение 2-3-6 часов, потом еще крутим и так далее

Спасибо

Но имхо, если это не для универа, а для бизнеса, то что-то с ним не так, если у вас идет в базу названия, а не id)

если nvme, то должно быть все хорошо... у нас на конфигурациях с nvme приблизительно такие значения стоят:
scale_factor = от 0.02 до 0.05
cost'ы hit/miss/dirty = 0/1/5 (cost_limit дефолтный 200)
autovacuum_vacuum_cost_delay = 0.01ms

Да, но у вас не 9.5?

пытаюсь прикрутить бд к телеграм боту. просто ради интереса пока