на проде вместо hash join используется nestedloop и запрос очень сильно деградирует

Стоп. Я подробно не смотрел, но почему Вы вообще используете CTE?

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

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

> 1) удобнее писать большие запросы, А, тогда лучше вообще не пользуйтесь CTE. В PostgreSQL есть "маленькая" особенность — они являются optimization fences, т.е. оптимизируются отдельно от остального запроса. Т.е. в типичных случаях при их использовании об адекватном планировании можете забыть. :( > 2) получаем вместо 1й большой несколько мелких агрегаций, которые исполняются асинхронно Где Вы это видите? В плане ничего подобного нет, кстати.

2) это не тот план, согласен, в 11 в несколько воркеров, это с 9.5 там в принцыпе нет воркеров

по 1) не заметил деградации производительности с CTE

изменить можно (mount -o bind /tmp/pgconf /etc/postgresql), но как поведёт себя сервер при рестарте - неизвестно, поэтому не хотелось бы

Alter system поправит параметры в postgresql.auto.conf, лежащем рядом с данными

по 1) не заметил деградации производительности с CTE

Ну-ну. Ничего, ещё заметите. :( И, кстати, что?! Это не Вы ли тут что-то хинтуете, а? ;)

предварительно в cte выбираю нужные строки и их агрегирую, потому, что если делать всё сразу одной агрегацией, то это огромные join на 2-7 КК строк и агргация работает несколько секунд + join очень долгий, + огромная подребность в work_mem (60-120mb), а если мы выбираем 10-100к строк в отдельные CTE, предварительно агрегируем их и получаем join не между 1.5КК и 5кк строк а join между ~6к строк, которые отрабатывает на порядок быстрее и запрос работает не 22000ms а 200ms

Ну-ну. Ничего, ещё заметите. :( И, кстати, что?! Это не Вы ли тут что-то хинтуете, а? ;)

не понял, set_nestloop нужен был всего лишь для проверки работы hash join на проде.

предварительно в cte выбираю нужные строки и их агрегирую, потому, что если делать всё сразу одной агрегацией, то это огромные join на 2-7 КК строк и агргация работает несколько секунд + join очень долгий, + огромная подребность в work_mem (60-120mb), а если мы выбираем 10-100к строк в отдельные CTE, предварительно агрегируем их и получаем join не между 1.5КК и 5кк строк а join между ~6к строк, которые отрабатывает на порядок быстрее и запрос работает не 22000ms а 200ms

Подождите, Вы это на самом деле пробовали, или это всё Ваши предположения? Вы можете показать EXPLAIN ANALYZE запроса (на prod), где все CTE заменены на подзапросы?

да, это на самом деле пробовал, explain сейчас скинуть не могу

не понял, set_nestloop нужен был всего лишь для проверки работы hash join на проде.

Который сработал лучше, нет? Что значит, что либо планировщик построил не лучший план, либо у Вас что-то не так с cost-ами.

проблема с планировщиком только на одном серваке на проде, в остальном работает хорошо.

да, это на самом деле пробовал, explain сейчас скинуть не могу

Проверьте на досуге, это несложно. Хотя бы сравните оценки rows с реальностью (для оптимизации это самое важное). Тем не менее, общий приницип от этого не меняется — CTE выполняются "целиком" (почти всегда, кроме LIMIT снаружи.. может, ещё каких-то крайних случаев) — предикаты в них не "проталкиваются", flattening не производится, и т.д. Короче, Вам просто пока "везёт" (и то, как видно, уже не очень).

Ярослав, не буду с вами спорить про везёт и тд, разбиение запроса на несколько cte позволило быстро соптимизировать запрос с 22с до 0.2с, команду это устраивает, идём дальше) Мы пробовали много разных техних но остановились на этой. Можете кидать камнями и называть шарлатаном

за совет про set ... спасибо

Ярослав, не буду с вами спорить про везёт и тд, разбиение запроса на несколько cte позволило быстро соптимизировать запрос с 22с до 0.2с, команду это устраивает, идём дальше) Мы пробовали много разных техних но остановились на этой. Можете кидать камнями и называть шарлатаном

Да причем тут шарлатанство, я ничего такого не имел в виду... Я Вам просто рассказал, как работает планировщик, т.е. про то, что использовать CTE стоит или тогда, когда производительность не имеет значения, или как hint (как у Вас, видимо, и вышло). Но вместо этого лучше найти и, по возможности, решить настоящую проблему (т.к. hints имеют нехорошее свойство "протухать", т.е. то, что хорошо работает сегодня, через месяц (после изменения данных) запросто станет выполняться несколько часов).

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

Не знаю, на сколько интересно, но я вчера походу запускал запрос на новых таблицах с пустой статистикой, сейчас решил заново прогнать тесты, всё работает как положено https://explain.depesz.com/s/RFuv

Который сработал лучше, нет? Что значит, что либо планировщик построил не лучший план, либо у Вас что-то не так с cost-ами.

Не знаю, на сколько интересно, но я вчера походу запускал запрос на новых таблицах с пустой статистикой, сейчас решил заново прогнать тесты, всё работает как положено https://explain.depesz.com/s/RFuv

А, так это вообще тогда было планирование "наугад". ;) Для начала лучше всегда делать [VACUUM] ANALYZE, а иначе дело может быть просто в том, что нет статистики...

Кто-нибудь смотрел "Логическая репликация и уровни изоляции транзакций PostgreSQL" Михаила Тюрина? Там были какие-нибудь слайды, или краткое резюме?

в субботу?

там были цитаты из документации по большей части

Краткое резюме: болт по всей морде, а не SERIALIZABLE на реплике.

да, основной рассказ был про SSI и то, что блокировки сериализации не передаются ни при бинарной ни при логической реплике

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

там были цитаты из документации по большей части

Ясно...

сначала - в смысле перед обсуждением SSI

Краткое резюме: болт по всей морде, а не SERIALIZABLE на реплике.

Ага, спасибо (это я и так знал)!

Кто-нибудь смотрел "Логическая репликация и уровни изоляции транзакций PostgreSQL" Михаила Тюрина? Там были какие-нибудь слайды, или краткое резюме?

https://www.youtube.com/watch?v=P5mkbjJ2Rks

да, основной рассказ был про SSI и то, что блокировки сериализации не передаются ни при бинарной ни при логической реплике

Понятно, спасибо!

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

А вот это как раз то, что мне было бы интересно! ;) И что говорил, что-то из них ACID?

кстати, про лондист у Михаила и Константина есть более подробное выступление на хайлоаде, там было больше деталей (если заинтересовало)

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

https://www.youtube.com/watch?v=vCYGOVa3w1g

Может кто в курсе? Есть кластер PostgreSQL 9.5.12 на Windows Server (все БД в кластере такого вида): Name | postgres Owner | postgres Encoding | UTF8 Collate | Russian_Russia.1251 Ctype | Russian_Russia.1251 Пытаюсь создать такую же БД на Ubuntu 16.04: initdb_options = '--encoding=UTF8 --locale=ru_RU.cp1251' # locale -a C C.UTF-8 en_US.utf8 POSIX ru_RU.cp1251 Получаю ошибку: Creating new PostgreSQL cluster 10/main ... /usr/lib/postgresql/10/bin/initdb --encoding=UTF8 --data-checksums --locale=ru_RU.cp1251 -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "ru_RU.cp1251". initdb: encoding mismatch The encoding you selected (UTF8) and the encoding that the selected locale uses (WIN1251) do not match. This would lead to misbehavior in various character string processing functions. Rerun initdb and either do not specify an encoding explicitly, or choose a matching combination. Error: initdb failed Error: could not create default cluster. Please create it manually with Как-то же она была создана?

А вот это как раз то, что мне было бы интересно! ;) И что говорил, что-то из них ACID?

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

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

Почему? Принципы для реализации есть (как для WAL-based логической репликации, так и для триггерной). Я вообще думал, что слоны full-ACID... разве нет?

Почему? Принципы для реализации есть (как для WAL-based логической репликации, так и для триггерной). Я вообще думал, что слоны full-ACID... разве нет?

Для физической обеспечить ACID - более-менее понятно, как. Для логической, с учётом того, что транзакция может быть длинной и завершиться существенно позднее с более меньшим TXID, нежели более поздние, которые завершатся весьма раньше - не очень. Особенно на READ COMMITTED

> Для логической, с учётом того, что транзакция может быть длинной и завершиться существенно позднее с более меньшим TXID, нежели более поздние, которые завершатся весьма раньше - не очень. С физической всё может быть абсолютно так же. Поэтому, по идее, метод тот же. > Особенно на READ COMMITTED А тут уж full-ACID вообще ни при чём. Этот уровень, и даже RR обеспечиваются всеми методами на реплике уже сейчас. А если я использовал RC на источнике, то все гарантии я потерял ещё тогда. ;)

Ярослав, я так глубоко не забирался, обсуждать эту безусловно интересную и животрепещущую тему не вот уж готов. Надо думать, открывать чакры для принятия Космоса, принимать оттедова тесты, погонять эти тесты. У меня, к сожалению, времени нет и неизвестно. :(

Ярослав, я так глубоко не забирался, обсуждать эту безусловно интересную и животрепещущую тему не вот уж готов. Надо думать, открывать чакры для принятия Космоса, принимать оттедова тесты, погонять эти тесты. У меня, к сожалению, времени нет и неизвестно. :(

Дело в том, что слонов я немного тестировал. И, похоже, что "всё работает" (т.е. full-ACID (SERIALIZABLE) на slave обеспечивается). Только тестирование, конечно, ничего не доказывает. :( Потому-то мне и интересно, какие из систем триггерной репликации дают эту гарантию, а какие — нет...

Я имел несчастье немного погрузиться в тестирование. Поэтому с некоторой долей уверенности могу сказать, что надо не "немного", надо основательно. И хорошо так подумать над самими тестами.

Я имел несчастье немного погрузиться в тестирование. Поэтому с некоторой долей уверенности могу сказать, что надо не "немного", надо основательно. И хорошо так подумать над самими тестами.

А толку-то? Как говорится, "тестирование может доказать только наличие ошибок, но не их отсутствие." ;) Поэтому, даже если тесты ничего не найдут, нужны и какие-то теоретические основания. Как именно, например, работает у слонов механизм формирования изменений, я точно не знаю (там как раз в плане работы с транзакциями с виду что-то нетривиальное). А чтобы убедиться, достигается ли ACID, нужно либо это знать, либо спросить у того, кто писал, к чему они стремились (я к тому, что если действительно стремились, то даже если тесты что-то выявят, это всего лишь bugs).

Может кто в курсе? Есть кластер PostgreSQL 9.5.12 на Windows Server (все БД в кластере такого вида): Name | postgres Owner | postgres Encoding | UTF8 Collate | Russian_Russia.1251 Ctype | Russian_Russia.1251 Пытаюсь создать такую же БД на Ubuntu 16.04: initdb_options = '--encoding=UTF8 --locale=ru_RU.cp1251' # locale -a C C.UTF-8 en_US.utf8 POSIX ru_RU.cp1251 Получаю ошибку: Creating new PostgreSQL cluster 10/main ... /usr/lib/postgresql/10/bin/initdb --encoding=UTF8 --data-checksums --locale=ru_RU.cp1251 -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "ru_RU.cp1251". initdb: encoding mismatch The encoding you selected (UTF8) and the encoding that the selected locale uses (WIN1251) do not match. This would lead to misbehavior in various character string processing functions. Rerun initdb and either do not specify an encoding explicitly, or choose a matching combination. Error: initdb failed Error: could not create default cluster. Please create it manually with Как-то же она была создана?

Впрочем, с такими параметрами: --encoding=WIN1251 --locale=ru_RU.cp1251 кластер БД инициализировался без ошибок. Остаётся загадкой, как его смогли инициализировать с кодировкой UTF8 и локалью Russian_Russia.1251.

Впрочем, с такими параметрами: --encoding=WIN1251 --locale=ru_RU.cp1251 кластер БД инициализировался без ошибок. Остаётся загадкой, как его смогли инициализировать с кодировкой UTF8 и локалью Russian_Russia.1251.

Мне кажется это одна и та же локаль (Russian_Russia.1251 и ru_RU.cp1251) только в линуксячем и виндовом варианте

Всё верно. Но кодировка на Винде UTF8 с этой локалью нормально соседствует, а на Ubuntu - ругается на несоответствие локали и кодировки.

Всё верно. Но кодировка на Винде UTF8 с этой локалью нормально соседствует, а на Ubuntu - ругается на несоответствие локали и кодировки.

Postgres в WIN-версии не так работает с locale/encoding как в UNIX-версии.

Всё верно. Но кодировка на Винде UTF8 с этой локалью нормально соседствует, а на Ubuntu - ругается на несоответствие локали и кодировки.

Это всё (locale/encoding/collations) в каждой ОС работает по-своему, к сожалению. :(

Это всё (locale/encoding/collations) в каждой ОС работает по-своему, к сожалению. :(

То есть готовиться к чудесам?(((

То есть готовиться к чудесам?(((

В общем, да. Даже бывает, что upgrades OS "ломают" индексы в PostgreSQL (потому что кто-то "наживую" переделал сортировку в какой-то locale, например, а о последствиях для использующих её программ (вроде PostgreSQL) не подумал). :(

В общем, да. Даже бывает, что upgrades OS "ломают" индексы в PostgreSQL (потому что кто-то "наживую" переделал сортировку в какой-то locale, например, а о последствиях для использующих её программ (вроде PostgreSQL) не подумал). :(

Спасибо, учту)

Подскажите, плиз, кто-то использует Barman и упирался при этом в процесс архивирования WAL файлов?

Подскажите, плиз, кто-то использует Barman и упирался при этом в процесс архивирования WAL файлов?

Что такое «упирался»?

есть поток WAL файлов на сервер бэкапа они спера записываются на раздел, затем барман их умеет жать в архив для экономии места

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

Ну как-бы тут не очень много вариантов - либо отключить компрессию, либо дать цпу помощнее. Ну либо посмотреть фичу многоптоковости в последних релизах - не знаю, есть ли она

@nyctophobe будет жить. Поприветствуем!

Всем привет!

Скажите, а синтаксис INSERT INTO ... SET ... вообще не поддерживается?

Скажите, а синтаксис INSERT INTO ... SET ... вообще не поддерживается?

А что бы он мог значить, вообще?

Скажите, а синтаксис INSERT INTO ... SET ... вообще не поддерживается?

Нет. Это удобно, но нестандартно.

как удалить (win 10 64) 10.5 ?

Что если дат перетащить и бросить на ехе?

A+ будет жить. Поприветствуем!

@kondr1 будет жить. Поприветствуем!

@z0s0O будет жить. Поприветствуем!

друзья, rum не ставится не на Enterprise ?

друзья, rum не ставится не на Enterprise ?

ставится. Я использую его для ванили (10-я версия)

Ребят, всем привет. У нас случилось падение сервера постгре, побились базы, все дела. В логе стали появляться подобные сообщения с пустыми параметрами. Кто может подсказать, почему постгре не логирует ни имя базы с ошибкой, ни ip с которого обратились? < 2018-09-18 13:03:34.125 MSK:{IP}:{client}:{database name} >LOG: could not receive data from client: Connection reset by peer < 2018-09-18 13:05:18.506 MSK::: >ERROR: index "pg_attribute_relid_attnum_index" contains corrupted page at block 0 < 2018-09-18 13:05:18.506 MSK::: >HINT: Please REINDEX it.

меня бы это меньше всего интересовало в данной ситуации )

Ну, восстановлением баз занимаюсь не я, процесс идёт, остальные проблемы понятны. А вот это поведение меня смущает. Я понять не могу в чём тут дело.

Григорий заблокируйте доступ к БД извне, пока идёт восстановление. Иначе вы получите что-нибудь "весёлое", а не базы.

Григорий заблокируйте доступ к БД извне, пока идёт восстановление. Иначе вы получите что-нибудь "весёлое", а не базы.

Такие решения я не принимаю ) Начальство было поставлено в известность обо всём, было выбрано решение. Последствия этого решения они сами, надеюсь, осознают )

Ну, восстановлением баз занимаюсь не я, процесс идёт, остальные проблемы понятны. А вот это поведение меня смущает. Я понять не могу в чём тут дело.

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

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

Я понял, спасибо. Просто интересно, чем вызвано такое поведение. Битой системной базой или базой клиента )

системной