Секрет 35 ( Еще одна проблема пустой таблицы )
Т.к. Москву и окрестности накрыло сырью и хмурью, попробую чуток разнообразить ваш субботний авечер!
Я много говорил о неожиданных спецэффектах join-а с пустыми таблицами как и о преимуществах определенных видов join-ов перед другими, но много не значит достаточно.
BFG-10K. долго скучающее на стене, выстрелило накануне 1 Мая, напомнив мне, ЧТО незаслуженно забыт FULL OUTER JOIN!!!.
Спешу закрыть этот вопрос, т.к. во-первых, наверняка некоторые используют эту трансформацию, а во-вторых,
вчера на проме пробегал образцово-показательный зверь -) : полный join с пустой таблой, который наследил спиллом в 28 TB:

with new_set as (select * from account_balance where to_version_id = 9223372036854775807),
old_set as (select * from account_balance where 1=0)
select
  sum(case when coalesce(t0.account_rk,t1.account_rk) is not null and
                coalesce(t0.effective_date,t1.effective_date) is not null and
                coalesce(t0.src_cd,t1.src_cd) is not null and
                t0.hash_diff != t1.hash_diff then 1 else 0 end) change_count
from new_set t0 full outer join old_set t1
on t0.account_rk = t1.account_rk and 
   t0.effective_date = t1.effective_date and 
   t0.src_cd = t1.src_cd
where t0.account_rk is null or t1.account_rk is null or t0.hash_diff != t1.hash_diff

Данный запрос возбудил дважды мой интерес:
1) Зачем он, выполняющий мартышкин труд, нужен в принципе ?
2) Как он смог ТАК нагнуть сервер ?

Ответ на первый вопрос меня не удовлетворил, т.к. не объясняет его сакральную суть: де это регресс-тест.
⛏️Ответ на второй я нашел в плане идентичного запроса, но на порядок меньшей выборке в new_set(100 млрд строк), вот он .

Заметим, что в плане есть сортировка по полям join-а, которая в зависимости от объема данных, выполняется либо на диске
( Sort Method: external merge Disk: 6 254 949 152kB в данном случае )
либо в памяти ( Sort Method: quicksort Memory: 12826236kB для 100 млн строк )

Таким образом, для GPORCA не имеет значения число строк в табл-е справа от FULL JOIN (пустой в моем случае) - Собаки лают, караван идёт, все идет по плану! - Занавес!
План:

Aggregate  (cost=0.00..66485002.13 rows=1 width=24) (actual time=1073504.012..1073504.012 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice2; segments: 348)  (cost=0.00..66485002.13 rows=1 width=24) (actual time=1073500.931..1073503.909 rows=348 loops=1)
        ->  Aggregate  (cost=0.00..66485002.13 rows=1 width=24) (actual time=1073502.299..1073502.300 rows=1 loops=1)
              ->  Redistribute Motion 348:348  (slice1; segments: 348)  (cost=0.00..66424753.86 rows=287356316 width=78) (actual time=625468.045..953332.916 rows=287403064 loops=1)
                    ->  Result  (cost=0.00..48232823.20 rows=287356316 width=78) (actual time=722544.174..913870.520 rows=288956000 loops=1)
                          Filter: ((account_rk IS NULL) OR (account_rk IS NULL) OR (hash_diff <> hash_diff))
                          ->  Merge Full Join  (cost=0.00..35072823.46 rows=287356316 width=78) (actual time=722544.146..866509.306 rows=288956000 loops=1)
                                Merge Cond: ((account_rk = account_rk) AND (effective_date = effective_date) AND (src_cd = src_cd))
                                ->  Sort  (cost=0.00..2349522.41 rows=287356316 width=50) (actual time=722543.481..831079.605 rows=288956000 loops=1)
"                                      Sort Key: account_rk, effective_date, src_cd"
                                      Sort Method:  external merge  Disk: 6 254 949 152kB
                                      ->  Seq Scan on account_balance  (cost=0.00..60482.72 rows=287356316 width=50) (actual time=0.549..51603.818 rows=288956000 loops=1)
                                            Filter: (to_version_id = '9223372036854775807'::bigint)
                                ->  Sort  (cost=0.00..0.00 rows=0 width=28) (actual time=0.000..1.051 rows=0 loops=1)
"                                      Sort Key: account_rk, effective_date, src_cd"
                                      Sort Method:  quicksort  Memory: 11484kB
                                      ->  Result  (cost=0.00..0.00 rows=0 width=28) (actual time=0.000..0.146 rows=0 loops=1)
                                            ->  Result  (cost=0.00..0.00 rows=0 width=28) (actual time=0.000..0.086 rows=0 loops=1)
                                                  ->  Result  (cost=0.00..0.00 rows=0 width=28) (actual time=0.000..0.041 rows=0 loops=1)
                                                        One-Time Filter: false
Planning time: 82.315 ms
  (slice0)    Executor memory: 1092K bytes.
"* (slice1)    Executor memory: 101556K bytes avg x 348 workers, 122489K bytes max (seg19).  Work_mem: 121678K bytes max, 30902646K bytes wanted."
"  (slice2)    Executor memory: 147K bytes avg x 348 workers, 147K bytes max (seg0)."
Memory used:  524288kB
Memory wanted:  61806290kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1073549.572 ms

Интересно отметить, что
🔸 спилл непропорционален числу строк в new_set, при увеличении которого с 10 до 100 млрд клонированием спилл вырос с 482 до 5965 ГБ
🔸 если в ON предикате join-а оставить только ключ дистрибцуии табл-ы account_rk, то спилл для new_set из 10 млрд строк сокращается до 153 ГБ, а Redistribute в плане исчез.

📌 Вывод: Если FULL JOIN в вашем коде неизбежен, проверьте табл-у справа на пустоту, и сделайте отдельную ветку кода на этот случай.

Translation of last published secret
Secret 35 (Another problem of empty table)
Since Moscow and its environs are covered with drizzle and gloom, I'll try to diversify your evening a little!
I've talked a lot about the unexpected special effects of joins with empty tables as well as about the advantages of certain types of joins over others, but much does not mean enough.
BFG-10K. long bored on the wall, shot up on the eve of May 1, reminding me THAT FULL OUTER JOIN has been undeservedly forgotten!!!.
I hurry to close this question, because firstly, some people probably use this transformation, and secondly,
yesterday a model beast ran through the prom -) : full join with an empty table, which left a trace of 28 TB of spill:

with new_set as (select * from account_balance where to_version_id = 9223372036854775807),
old_set as (select * from account_balance where 1=0)
select
  sum(case when coalesce(t0.account_rk,t1.account_rk) is not null and
                coalesce(t0.effective_date,t1.effective_date) is not null and
                coalesce(t0.src_cd,t1.src_cd) is not null and
                t0.hash_diff != t1.hash_diff then 1 else 0 end) change_count
from new_set t0 full outer join old_set t1
on t0.account_rk = t1.account_rk and 
   t0.effective_date = t1.effective_date and 
   t0.src_cd = t1.src_cd
where t0.account_rk is null or t1.account_rk is null or t0.hash_diff != t1.hash_diff

This query piqued my interest twice:
1) Why is it needed in principle, doing monkey work?
2) How could it bend the server THAT way?

The answer to the first question did not satisfy me, because it does not explain its sacred essence: where is this regression test.
⛏️I found the answer to the second in terms of an identical query, but an order of magnitude smaller sample in new_set (100 billion rows), here it is.

Note that the plan includes sorting by join fields, which, depending on the data volume, is performed either on disk
( Sort Method: external merge Disk: 6,254,949,152kB in this case )
or in memory ( Sort Method: quicksort Memory: 12,826,236kB for 100 million rows )

Thus, for GPORCA the number of rows in the table to the right of FULL JOIN (empty in my case) does not matter - The dogs bark, the caravan moves on, everything is going according to plan! - Curtain!

Query plan:

It is interesting to note that
🔸 the spill is not proportional to the number of rows in new_set, when increasing it from 10 to 100 billion by cloning, the spill grew from 482 to 5965 GB
🔸 if in the ON predicate of the join we leave only the distribution key of the account_rk table, then the spill for new_set from 10 billion rows is reduced to 153 GB, and Redistribute disappeared in the plan.

📌 Conclusion: If FULL JOIN is unavoidable in your code, check the table on the right for emptiness, and make a separate code branch for this case.

Друзья, есть 2 новости, и обе не радуют.
1)
Я захотел проверить коментарий очень уважаемого мной читателя, который использует legacy оптимизатор в случае с FULL OUTER JOIN.
Действительно, проще было бы уйти от лишней проверки табл-ы на пустоту.
Но дело в том, что несмотря на то, что после set optimizer = off;
в плане действительно появился Hash Full Join и даже ушел спилл, Execution time (более 1.5ч) сильно просел на табле в 10 млрд строк
по сравнению с GPORCA, который отработал за 92 с.

Aggregate  (cost=712450000.06..712450000.07 rows=1 width=24) (actual time=5842721.436..5842721.436 rows=1 loops=1)
  ->  Hash Full Join  (cost=0.04..612475000.06 rows=9997500001 width=352) (actual time=14.220..3411361.808 rows=10000000000 loops=1)
        Hash Cond: ((t0.account_rk = t1.account_rk) AND (t0.effective_date = t1.effective_date) AND (t0.src_cd = t1.src_cd))
        Filter: ((t0.account_rk IS NULL) OR (t1.account_rk IS NULL) OR (t0.hash_diff <> t1.hash_diff))
"        Extra Text: Hash chain length 0.0 avg, 0 max, using 0 of 4194304 buckets."
        ->  Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..400000000.00 rows=10000000000 width=176) (actual time=0.047..1199763.273 rows=10000000000 loops=1)
              ->  Subquery Scan on t0  (cost=0.00..225275415.00 rows=28735633 width=110) (actual time=3.728..13822.989 rows=28890400 loops=1)
                    ->  Seq Scan on bbridge_account_balance  (cost=0.00..125275415.00 rows=28735633 width=110) (actual time=3.707..11014.937 rows=28890400 loops=1)
                          Filter: (to_version_id = '9223372036854775807'::bigint)
        ->  Hash  (cost=0.02..0.02 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=1)
              Buckets: 4194304  Batches: 1  Memory Usage: 0kB
              ->  Subquery Scan on t1  (cost=0.00..0.02 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1)
                          One-Time Filter: false
Planning time: 0.374 ms
  (slice0)    Executor memory: 35146K bytes.
"  (slice1)    Executor memory: 2449K bytes avg x 348 workers, 2463K bytes max (seg216)."
Memory used:  524288kB
Optimizer: Postgres query optimizer
Execution time: 5 842 726.112 ms

Хотелось бы верить, у коллеги другой сценарий, для которого включение legacy имеет смысл.
2) Не менее уважаемый читатель @GaRin_1979 сообщил, что при UPDATE партицированной табл-ы блокируются все секции, цитирую:
"
Дано две таблицы с кол-вом партиций > 5000
Открываем транзакцию. Делаем update даже с условием where false или с условием партицирования, аля, operday = ххх - не важно.
В другой сессии (или в этой же) пытаемся так же обновить другую партицированную таблицу.
Здравствуй ошибка про 10тыс одновременно открытых на вставку таблиц.
Без global deadlock detector лочятся ВСЕ партиции при попытке update или delete.
"
Проверил, это верно и увы, для INSERT по-видимому тоже имеет место та же ошибка
can't have more than 10000 different append-only tables open for writing data at the same time.,
если вставка данных идет в таблицы с числом секций > 1/2 параметра max_appendonly_tables
Таким образом, мой прошлый вывод тут оказался неверный
и я в растерянности, потому что судя по таймингу двух параллельных транзакций, точнее вложенности их интервалов у меня пазл не складывается.
Да, в моем тесте число осекций было < 1/2 max_appendonly_tables, но на механизм блокировки число секций влиять не должно, это лишнее усложнение.

Иными словами, если блокируется вся таблица и при INSERT, то как 2я сессия могла начаться позже и закончиться раньше первой сессии.
Определенно, я чего то не знаю про механизм блокировок.
Возможно, что 2я сессия успела заблокировать табл-у ранее первой, или имеет место отложенная запись, но мне обе версии кажутся очень зыбкими.

С ДНЕМ ВЕЛИКОЙ ПОБЕДЫ!!!

По опросу ВЦИОМ, 70% наших граждан считают 9 Мая самым главным праздником. 🔥,кто согласен.

Друзья, Отпуск - прекрасное время душой отдохнуть, да и руками поработать. Медвежий угол (угадаете о каком городе речь?) как всегда даёт массу впечатлений и силы, которые нам всем нужны. Вернулся и собрал шведскую стенку. Российская сборка, ребята по красоте сделали. Лёгким движением брусья превращаются в турник. А как у вас дела со спортом ? Опрос ниже

Секрет 36 (Инвариантность сжатия данных)
Secret 36 (Invariance of data compression)
И снова здравствуйте, продолжим тему кто, как и сколько жмет -)
Вернувшиссь из отпуска, обнаружил что новый прод подвезли, и т.к. старый пока остался, мой первый шаг был проверить -
как конфигурация кластера влияет на степень сжатия таблицы.
Hello again, let's continue the topic of who, how and how much compresses -)
Having returned from vacation, I discovered that a new product had been delivered, and since the old one was still there, my first step was to check -
how the cluster configuration affects the compression ratio of the table.


📌Число сегментов на хост сократилось вдвое.
+ на сегмент стало RAM x 2.5
Версия ADB осталась та же.

📌The number of segments per host was halved.
+ per segment there is RAM x 2.5
The ADB version remains the same.

Я взял уже знакомую нам табл-у account_balance и пересоздал ее на old и new кластере с опцией AO/CO zstd1, отсортировав по 5 полям -
account_rk, effective_date, currency_rk, department_rk, account_sum, где department_rk - филиал банка:
I took the already familiar account_balance table and recreated it on the old and new clusters with the AO/CO zstd1 option, sorting it by 5 fields -
account_rk, effective_date, currency_rk, department_rk, account_sum, where department_rk is the bank branch:

create table account_balance_ordered   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select * from idl.account_balance
    order by 1,2,3,4,5
    DISTRIBUTED BY (account_rk)

Результат:
Размер account_balance_ordered в обоих случаях составил 37% от исходной, или 77 ГБ.
Result:
The size of account_balance_ordered in both cases was 37% of the original, or 77 GB.

📌Таким образом, конфигурация кластера не влияет на размер отсортированного сета, упакованного в zstd

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

📌Thus, the cluster configuration does not affect the size of the sorted set packed in zstd
I hoped that the compression result would be better for new, because each segment now has almost three times more lines for one-time compression, but apparently
it compresses data exclusively for each group with the same hp number (account_rk - the first field in the sorting key), hence the lack of effect.

p.s.
Ожидаемо, на new спиллов > 10 TB стало меньше в разы, что очень радует.
As expected, on new,the number of spill > 10 TB queries has decreased significantly > 10 TB, which makes me very happy.

Друзья, продолжим тему KYC, т.к. мне очень интересно узнать,кто тут тусуется. Ниже пара опросов.
Guys, let's go further with KYC coz I really wonder who are you. Below are two votes

Oops, опрос с телефона не создать. Ладно, пишем в комментах, как давно у вас случился первый "секс" с Greenplum.
Damn, there is no chance to create vote from mobile app. Well, just share in comments how long have you known Greenplum?

Вопрос 4
А кто-то знает, почему GP запрос возвращает местами полные дубли ? Это же нарушение 12 правил Кодда.
Anybody knows why the query below returns surprisingly complete duplicates? Isn't this a violation of Codd's 12 rules?

select * from pg_partitition_columns

На заметку
Пример интервала по стандарту ISO 8601
P0DT5H33M39.000000S = 5 ч 33 м 39 с
- P — обозначает начало периода (Period).
- 0D — 0 дней.
- T — разделитель даты и времени.
- 5H — 5 часов.
- 33M — 33 минуты.
- 39.000000S — 39 секунд с шестью знаками после запятой.
Note:
Example of time interval according to ISO 8601 standard
P0DT5H33M39.000000S = 5 h 33 m 39 s
p.s.
Убил час, чтобы вкурить, что это за строка, а ИИ убил меня, ответив за 3 сек, что это за строка
I spent an hour trying to figure out what this line was, and the AI ​​killed me by answering in 3 seconds what this line was