Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..431.00 rows=1 width=28) (actual time=5.109..5.150 rows=1 loops=1)
  ->  Hash Join  (cost=0.00..431.00 rows=1 width=28) (actual time=2.458..3.223 rows=1 loops=1)
        Hash Cond: (((column2)::text) = n_txt)
"        Extra Text: (seg26)  Hash chain length 1.0 avg, 1 max, using 8 of 524288 buckets."
        ->  Result  (cost=0.00..0.00 rows=1 width=28) (actual time=0.105..0.105 rows=1 loops=1)
              ->  Result  (cost=0.00..0.00 rows=1 width=28) (actual time=0.041..0.041 rows=1 loops=1)
                    ->  Result  (cost=0.00..0.00 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)
        ->  Hash  (cost=431.00..431.00 rows=1 width=8) (actual time=0.065..0.065 rows=8 loops=1)
              ->  Seq Scan on tst_1k_nk_dat_no_stat2  (cost=0.00..431.00 rows=1 width=8) (actual time=0.058..0.061 rows=8 loops=1)
Planning time: 12.860 ms
  (slice0)    Executor memory: 427K bytes.
"  (slice1)    Executor memory: 4314K bytes avg x 348 workers, 4316K bytes max (seg0).  Work_mem: 1K bytes max."
Memory used:  311296kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 11.493 ms

Вернем табл-у в исходное, но соберем стату:
Let's return the table to its original state, but collect the stats:

alter table tst_1k_nk_dat_no_stat2 set distributed randomly;
analyze tst_1k_nk_dat_no_stat2;

В результате исходного запроса попали на Redistribute:
The original query switched to Redistribute:

Gather Motion 348:1  (slice2; segments: 348)  (cost=0.00..431.05 rows=1000 width=23) (actual time=16.175..19.786 rows=1 loops=1)
  ->  Hash Join  (cost=0.00..431.00 rows=3 width=23) (actual time=12.145..13.017 rows=1 loops=1)
        Hash Cond: (((column2)::text) = n_txt)
"        Extra Text: (seg26)  Hash chain length 1.0 avg, 1 max, using 8 of 524288 buckets."
        ->  Result  (cost=0.00..0.00 rows=1 width=28) (actual time=0.182..0.183 rows=1 loops=1)
              ->  Result  (cost=0.00..0.00 rows=1 width=28) (actual time=0.091..0.091 rows=1 loops=1)
                    ->  Result  (cost=0.00..0.00 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=1)
        ->  Hash  (cost=431.00..431.00 rows=3 width=3) (actual time=11.607..11.607 rows=8 loops=1)
              ->  Redistribute Motion 348:348  (slice1; segments: 348)  (cost=0.00..431.00 rows=3 width=3) (actual time=5.425..11.602 rows=8 loops=1)
                    Hash Key: n_txt
                    ->  Seq Scan on tst_1k_nk_dat_no_stat2  (cost=0.00..431.00 rows=3 width=3) (actual time=0.081..0.086 rows=8 loops=1)
Planning time: 12.717 ms
  (slice0)    Executor memory: 447K bytes.
"  (slice1)    Executor memory: 172K bytes avg x 348 workers, 172K bytes max (seg0)."
"  (slice2)    Executor memory: 4226K bytes avg x 348 workers, 4235K bytes max (seg260).  Work_mem: 1K bytes max."
Memory used:  311296kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 63.870 ms

Почему в первом варианте есть One-Time Filter: (gp_execution_segment() = 152) для генерации параметров, а в последнем - нет, не понял, но лучше избегать такого подхода, когда кортеж констант базируется в СТЕ.
Why the first option has One-Time Filter: (gp_execution_segment() = 152) for generating parameters, but the last one doesn't, I don't understand, but it's better to avoid such approach when the constant tuple is based on CTE.

Секрет 30 (Девиз GUCCI) Secret 30 (GUCCI motto)

Сегодня на проме попалась PL/pgSQL ф-я на 1k строк, которая упорно давала осечку с ошибкой workfile per query size limit exceeded
, генеря каждый раз спилл более 30 TB.

Я прогнал ее под своей учеткой в песочнице и вдруг она сразу отработала без спилла за 10 мин.
Я всегда явно выставляю на мою сессию принудительный сбор статы через set gp_autostats_mode_in_functions = on_no_stats;

Т.к. данный параметр в оригинальной ф-ии установлен не был, я решил проверить, берется ли он из GUC для тех учеток, которые используются в даге (DAG – Directed Acyclic Graph) где выполняется ф-я.
=========================================================================
Today I came across a PL/pgSQL function on 1k lines on PROD, which stubbornly misfired with the error workfile per query size limit exceeded, generating a spill of more than 30 TB each time.
I ran it under my account in the sandbox and suddenly it immediately worked without a spill in 10 minutes.
I always explicitly set forced stats collection for my session via set gp_autostats_mode_in_functions = on_no_stats;
Since this parameter was not set in the original function, I decided to check whether it is taken from GUC for those accounts that are used in the DAG (DAG – Directed Acyclic Graph) where the function is executed.
Запрос

select u.usename, * from pg_db_role_setting x, pg_database d, pg_user u
where x.setdatabase = d.oid
and x.setrole = u.usesysid
and u.usename in ('dtpl-cdm-etl-tec', 'dtpl-cdm-ddl-tec');

выявил, что параметр сбора статистики задан в профиле GUC только для для УЗ dtpl-cdm-etl-tec:
{gp_autostats_mode=on_change,gp_autostats_mode_in_functions=on_change,gp_autostats_on_change_threshold=100000}

А т.к. в этой ф-ии на 100% используются CTAS запросы, т.е. DDL по сути, то выполнялась она под dtpl-cdm-ddl-tec, а значит для нее действует глобальный параметр БД,
который выдает show all, а он запрещает сбор статы (gp_autostats_mode_in_functions=none).
Ну а что бывает с запросами, которые join-ят табл-ы без статы думаю я уже вам все уши прожужжал - с большой вероятностью как фишка ляжет.

Таковы последствия нарушения принципа GUC Continuous Integration - что то не доехало до конфигурации учетки, в итоге - ничего не поехало.

Если верить книге Во имя Гуччи, основатель легендарного дома моды сказал: "Качество помнят еще долго после того, как забывается цена".

Гениально. и ведь это про создание кода тоже. Само написание кода это лишь малая часть затрат, и на коде, написанном на скору руку,
без отрисовки блок-схемы если хотите, без проверки сход-развала, читай настроек профиля GUC в этом случае, далеко не уедешь.
=========================================================================
The query above revealed that the statistics collection parameter is set in the GUC profile only for the dtpl-cdm-etl-tec account:
{gp_autostats_mode=on_change,gp_autostats_mode_in_functions=on_change,gp_autostats_on_change_threshold=100000}

And since this function uses 100% CTAS queries, i.e. DDL in essence, it was executed under dtpl-cdm-ddl-tec, which means that the global DB parameter is in effect for it,
which gives show all, and it prohibits the collection of stats (gp_autostats_mode_in_functions=none).
Well, what happens with queries that join tables without stats, I think I've already told you all about it - most likely it will work out.

These are the consequences of violating the GUC Continuous Integration principle - something didn't make it to the account configuration, and as a result, nothing went.

According to the book In the Name of Gucci, the founder of the legendary fashion house said: "Quality is remembered long after the price is forgotten."

Brilliant. And this is also about creating code. Writing the code itself is only a small part of the costs, and you won't get far with code written in a hurry,
without drawing a block diagram if you like, without checking the wheel alignment, read the GUC profile settings in this case.

Сорри за оффтоп-)

🇷🇺#бизнес #россия
Gucci зарегистрировала одноименный бренд в РФ — данные Роспатента

Праздничный квест.
В табл-е 5 млр строк, в GP 1000 сегментов.
Какой ключ распределения для X в запросе лучше, чем текущий ключ 'b' :

Holiday quest.
There are 5 billion rows in the table, 1000 segments in the GP.
Which distribution key for X in the query is better than the current 'b' :

select a, b, c , deleted_flg, dt_from, dt_to,
min(case when deleted_flg is true then null else dt_from end)
over(partition by a, b) as mn_dt_from
from X

С праздником, мужики!

Ответ на quest.
Еще вчера я бы поверил, что randomly - корректный ответ.
Вот мои доводы, запасайтесь поп корном!

Серия тестов на проде показала, что независимо от ключа, план запроса не меняется, который привожу в оригинале для точности ( cession_deal_rk, credit_deal_rk - это наши a и b в задаче )

Gather Motion 1056:1  (slice1; segments: 1056)  (cost=0.00..1231650.10 rows=5456054272 width=97) (actual time=25171.537..1101746.845 rows=5456054066 loops=1)
  ->  Result  (cost=0.00..65032.75 rows=5166719 width=97) (actual time=88416.959..108735.578 rows=8879058 loops=1)
        ->  WindowAgg  (cost=0.00..64531.58 rows=5166719 width=97) (actual time=88415.738..106450.656 rows=8879058 loops=1)
"              Partition By: cession_deal_rk, credit_deal_rk"
              ->  Sort  (cost=0.00..64531.58 rows=5166719 width=96) (actual time=88415.691..98580.905 rows=8879058 loops=1)
"                    Sort Key: cession_deal_rk, credit_deal_rk"
                    Sort Method:  external merge  Disk: 417165856kB
                    ->  Seq Scan on x  (cost=0.00..891.35 rows=5166719 width=96) (actual time=7.464..3897.447 rows=8879058 loops=1)
Planning time: 67.970 ms
  (slice0)    Executor memory: 2000K bytes.
"* (slice1)    Executor memory: 50717K bytes avg x 1056 workers, 67008K bytes max (seg240).  Work_mem: 48410K bytes max, 1595169K bytes wanted."
Memory used:  540672kB
Memory wanted:  4785705kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1346019.073 ms

Мой опорный вывод, что шаг Sort Method: external merge в плане выполняет сортировку слиянием отсортированных ранее частей табл-ы на сегментах,
а значит ровно рассыпаная по сегментам табл-а дает на этом шаге оптимальный результат.

В пользу этого говорит следующий факт.
Если выбрать ключом a, табл-а ( в нашем конкретном бизнес кейсе) станет сильно перекошена при максимуме 285 млн строк на сегменте, что
почти на 2 порядка выше среднего на сегмент ~5 млн строк (= 5 млрд сток в табл-е / 1056 сегментов),
В результате запрос валится по тайм-ауту после часа выполнения, тогда как при randomly выполняется меньше минуты.
Иными словами, такое поведение намекает, что сортировка происходит локально сначала именно на сегментах, и не все ее способны вынести.

В исходном коде проекта был обнаружен ключ b, который гораздо ровнее чем a, хотя и далек от равномерного.
Если в цифрах, для ключа по a,b skew составил 0.4, для randomly 0.001, т.е. данные почти без перекоса.
Считали skew как (max tuples(среди всех сегментов) - avg tuples(по всем сегментам)) / max tuples.

Таким образом, ключ оконки в partittion by не имеет значения, и подстраивать под него ключ дистрибуции для больших таблиц я бы не стал.
Конечно, если конкретный ключ дает равномерное распределение, то как вариант - можно оставить его, но универсальный способ - лучше раскидать таблицу по сегментам максимально равномерно.
Но в нашем квесте речь шла о решении конкретной узкой задачи.

Но не все так просто, ибо
я выявил баг с ALTER TABLE SET DISTRIBUTED BY.
Меня удивило, что данная операция смены ключа с a,b в randomly на табл-е в 5 млрд отработала мгновенно (0.2 с) и я решил проверить, все ли тут чисто.

После смены ключа с randomly на a,b ключ честно поменялся.
После отката к randomly (через ALTER TABLE SET x DISTRIBUTED RANDOMLY) ключ поменялся только в метаданных, т.е. pg_get_table_distributedby вернул RANDOMLY,
но по факту skew остался 0.4, вместо ожидаемого отсутствия перекоса.

И что хуже, эта фейковая смена ключа в RANDOMLY не отразилась на реальном плане, где при DISTRIBUTED RANDOMLY должен появиться Redistribute:

Gather Motion 1056:1  (slice2; segments: 1056)  (cost=0.00..1233203.18 rows=5456056832 width=97) (actual time=69988.544..1143226.306 rows=5456056682 loops=1)
  ->  Result  (cost=0.00..66585.28 rows=5166721 width=97) (actual time=70230.562..99398.590 rows=9020522 loops=1)
        ->  WindowAgg  (cost=0.00..66084.11 rows=5166721 width=97) (actual time=70229.410..94669.119 rows=9020522 loops=1)
"              Partition By: cession_deal_rk, credit_deal_rk"
              ->  Sort  (cost=0.00..66084.11 rows=5166721 width=96) (actual time=70229.304..76381.228 rows=9020522 loops=1)
"                    Sort Key: cession_deal_rk, credit_deal_rk"
                    Sort Method:  external merge  Disk: 417165728kB
                    ->  Redistribute Motion 1056:1056  (slice1; segments: 1056)  (cost=0.00..3366.42 rows=5166721 width=96) (actual time=173.046..30055.350 rows=9020522 loops=1)
"                          Hash Key: cession_deal_rk, credit_deal_rk"
                          ->  Seq Scan on x  (cost=0.00..891.35 rows=5166721 width=96) (actual time=23.282..11751.681 rows=5173811 loops=1)
Planning time: 55.041 ms
  (slice0)    Executor memory: 2044K bytes.
"  (slice1)    Executor memory: 1839K bytes avg x 1056 workers, 1839K bytes max (seg0)."
"* (slice2)    Executor memory: 77215K bytes avg x 1056 workers, 100128K bytes max (seg9).  Work_mem: 48410K bytes max, 1620584K bytes wanted."
Memory used:  540672kB
Memory wanted:  4862049kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1392286.244 ms

Соотв-но, randomly в итоге д.б. не самый эффективный согласно плану.

А теперь сухие цифры рез-тов тестов, 3 прогона CTAS исходного запроса для каждого ключа:
ab:
35 s 877 ms
30 s 125 ms
31 s 277 ms
среднее: 32.426 s

randomly:
35 s 682 ms
34 s 57 ms
27 s 515 ms
среднее: 32.418 s

b:
39 s 936 ms
34 s 546 ms
33 s 234 ms
среднее: 35.905 s

Т.е. ключ ab и randomly показали почти одинаковый рез-т
Ну и чтобы вас совсем добить, скажу , что на DR с большим отрывом победил исходный ключ b, дав по скорости 2.5 мин против 4 мин для ab и 5 5 мин у randomly в среднем на 2х прогонах.
Почему такая аномалия, теряюсь в догадках, но число сегментов на DR втрое меньше.

Итоговый вывод по тесту я бы озвучил так: хорош тот ключ, который входлит в набор полей оконной ф-ии и дает несильный перекос, чтобы сегменты
смогли выполнить локальную сортировку своих данных
Что интересно, для ключа b и ab план одинаков, т.е. для b GPORCA не счел нужным сделать Redistribute как для RANDOMLY.

В заключение, у меня только один вопрос, почему материализация данных запросов через CTAS работает менее 1 мин, а explain analyze исходного селекта висит более 20 мин.

Вопрос 1
Друзья, призываю в помощь!
Идентичный код прикладной PL/pgSQL ф-ии в 500 строк ( набор SQL запросов ) на одних и тех же данных и одном и том же железе в одной РГ дает сильно разный спилл при повторных запусках , то 20 TB(вчера), то <1(сегодня).
Число строк на выходе ф-ии отличается на 0.01%
Спилл появляется эпизодами, за последниий месяц всплеск спилла был в 4 из 27 запусков ф-ии.
Есть идеи, чем вызван такой разброс ?

Question 1
Friends, I need help!
An identical PL/pgSQL function code of 500 lines (a set of SQL queries) on the same data and the same hardware
in one Resource Group gives a very different spill upon repeated launches, sometimes 20 TB, sometimes less than 1TB.
The number of lines in the output of the function differs by 0.01%
Spill appears sporadically, in the last month it happened 4 times of 27 launches
Any ideas what caused this spread?

Друзья, 5 минут до начала!!!

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

https://rshb.sk.jugru.org/

Ставьте + кто смотрит онлайн и узнал о митапе из этого канала

Люди ждут всегда 3 вещи - конца рабочего дня, Пятницу и Весну! Сегодня флэш-рояль! Всех с праздником!

Секрет 31 (Разбивай на счастье!)
Друзья, запасаемся попкорном. Будет лонгрид, т.к. было не так много времени, чтобы сократить матерьял, которым спешу поделиться.

Давно хотел познакомиться тет-а-тет с партишками в GP,
особенно на фоне слухов про них, зачастую нелицеприятных.

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

Тут как раз рук-во спустило R&D задачку прощупать плюсы и минусы партицированных таблиц.

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

Т.к. в доке предел на число партиций на каждом уровне = 32 767, я решил пойти с самых простых вариантов и попытаться к нему приблизиться.

Я взял репрезентативную выборку в 7 млн строк из табл-ы документов на 74 млрд строк, по 1 млн для каждой ИС и
стал сохранять эти данные в партицированную таблицу той же структуры из 92 полей.

План теста был прост как валенок.
Я взял одноуровневое партицирование по дате.
В каждом тесте я создавал с нуля партицировнную табл-у вида, куда писал подготовленные 7 млн строк, потом читал небольшую выборку из полученной таблицы
с фильтром document_date between :1 and :2.
Единственное, в каждой строке document_date заполнялся случайно распределенной равномерной величиной DATE в диапазоне от 2000 до конца 25 г.
Начав с табл-ы в 50 партиций( далее N ), я накидывал каждый раз еще по 50 (Кстати, фильм "Еще по одной" - экстаз, смотрел несколько раз)
Т.е. в каждой итерации менялся только параметр EVERY (длина интервала нарезки парт-ий) от '190 days' до '1 days' для зверушки:

CREATE TABLE foo ( <columns> )
WITH (
   appendonly=true,
   orientation=column,
   compresstype=zstd,
   compresslevel=1
)
DISTRIBUTED BY (document_rk)
PARTITION BY RANGE(document_date)
          (
          START ('2000-01-01'::date) END ('2025-12-31'::date) INCLUSIVE EVERY ('190 days'::interval),
          DEFAULT PARTITION foo_prt_other
          );

Все скрипты я загнал в отдельную конфиг таблу( по 3 оператора для каждого теста: CREATE TABLE, INSERT, SELECT ), по которой прошелся вездесущим explain analyze,
который их и выполнит, и план в JSON сохранит ( по технологии, описанной в https://t.iss.one/mpp_secrets/39 )

Зарядив скрипт на ночь, я обнаружил утром, что тест абортнулся на insert-е в табл-у с размером партиции 9 дн. или с N=1055:
can't have more than 10000 different append-only tables open for writing data at the same time

Заглянув в параметры БД, я обнаружил параметр max_appendonly_tables, который задает то самое ограничение на число append-only таблиц,
в которое можно писать одновременно.
Я сначала растерялся, ведь я писал в рамках INSERT-а 1055 таблиц (партиция - это таблица), что никак не объясняло крэш!
Потом меня осенило, что PG не ORACLE и т.к. DDL не фиксирует транзакцию комитом, а все мои тесты выполнялись в цикле в одной транзакции, мог иметь
место накопительный эффект от предыдущих тестов.
Как бы там ни было, я решил сделать ход конем и проверить в отдельной сессии табл-у сразу с N = 9497 или с размером партиции 1 день.
Результат insert-а: опять шляпа, но уже с ошибкой high VMEM usage, что я могу объяснить тем, что у нас табл-а AOCO ( колоночная ) и файлов для такой табл-ы пишется одновременно
92(колонок) * 9497(партиций), что,видимо, БД не переварила.

Ну тогда пришлось ослабить структуру, и вместо AOCO взять AORO:
WITH (
appendonly=true,
orientation=row,
compresstype=zstd,
compresslevel=1
)
В такую табл-у с пограничным N = 9497 insert уже отработал корректно.
Это все хорошо скажете вы, а стоит ли игра свеч ?
Я по тому же принципу загнал в foo 1 млрд строк и тот же сет положил в плоскую foo_no_part ( т.е. без партов ) табл-у.
Замерил select ~1% строк в 3х тестах( через explain ), 1й тест дал небольшой выигрыш, но среднее оказалось меньше в итоге для плоской ( на фото тесты D2:F6 ).
Как же так ? Все зря ? Я подумал, что т.к. в БД 348 сегментов, то в среднем на сегменте по 3 млн строк для плоской таблы, которые фуллсканятся быстрее,
чем нужные партиции в foo. Чтобы сгладить накладные расходы на извлечение данных из партиций, я поднял ставки и загнал в обе по 10 млрд строк.
Снова выполнил по 3 теста на select ~1% строк ( 95 млн если точнее ) и справедливость восторжествовала:
На explain foo вырвалась вперед во всех 3х тестах, причем в одном из них двукратно ( тесты A1:C6 )
И чтобы исключить влияние пересылки строк на мастер, я провел еще 3 теста CTAS, сохранив рез-т select-ов предыдущих тестов из foo и foo_no_part в отдельные табл-ы c
randomly дистрибуцией(A9:C13). Рез-т на партициях дает 6-ти кратное ускорение.

Самые педанты спросят и будут правы - а что со временем вставки-то 10 млр строк в foo и foo_no_part.
Ответ: 42 мин 54 с и 15 мин 49 с соот-но, что ожидаемо, т.к. на вставку данных в табл-у самой простой структуры - это быстрее.
Правда стоит отметить, что в foo_no_part просто был CTAS из foo, а в foo вставлялся 1 млрд, растиражированный через generate_series(1,10)
и для каждой строчки ключ партиции document_date считался через '2000-01-01'::date + trunc(random()*9496))::int.

Вывод: Партиции могут давать заметный выигрыш бизнесу (Время-Деньги!) в зависимости от соотношения числа строк в табл-е и числа сегментов кластера.
Так, уже при ~30 млн строк табл-ы на каждом сегменте он составляет 6x!

Рез-ты тестов

Дорогие Леди, Мисс и Миссис!
Под звон весенней капели буду краток как тот парень.
Природа дала вам уникальную комбинацию повышенной отказоустойчивости через парную хромосому,
сопряженной с случайными перекосами сознания в сторону вашей безупречности.
За это вас и любим, скучать не даете!
С праздником, дорогие!❤️

Невероятно, но факт.
Если в той самой партицированной foo создать 110 млрд строк, сделать ее AOCO zstd1 (колоночной) с длиной секции 8 дн, или 1188 парт-ий,
то
select count(*) from foo виснет наглухо без возможности выполнения
select count(*) from foo where document_date between ('2000-01-01'::date) and ('2025-12-31'::date) отрабатывает за 4 мин.
Просто добавили фильтр, который покрывает все данные ( в дефолтной партиции 0 строк ).
Unbelievable, but true.
If in that same partitioned foo we create 110 billion rows, make it AOCO zstd1 (column) with a section length of 8 days, or 1188 partitions,
then
select count(*) from foo hangs completely without the possibility of execution
select count(*) from foo where document_date between ('2000-01-01'::date) and ('2025-12-31'::date) works in 4 minutes.
We simply added a filter that covers all data (there are 0 rows in the default partition).