Те из вас, чей ответ > 10 в опросе, просьба проголосовать вовторно в том же опросе ниже
Those of you who answered ">10" in the poll, please vote again in the same poll below

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

Результат ожидаем. Zstd на отсортированной колонке даёт большее сжатие. Совершенно бесполезная вещь для таблиц с большим количеством колонок.

Мой комментарий:
По идее - так оно и есть, коллега, НО есть нюанс, который продемонстрирую в этот раз на реальных данных крупного банка
🔸 Возьмем 6.5 TB таблицу account_balance, состоящую из 18 полей, ключ дистрибуции у которой - account_rk ( по сути л.с. клиента ) . Возьмем одну из ее топовых ( по размеру) партиций. Просто отсортируем по ключу - получим в результате 74 ГБ вместо 173 ГБ . А теперь отсортируем ее же по паре ключ + дата баланса - результат 41 ГБ. Цифры говорят сами за себя. Эффект кластеризации.
🔸 Возьмем таблицу проводок, состоящую из 92 полей, ключ дистрибуции у которой -doc_rk уникален в отличие от 1-го кейса После сортировки одной из ее секций по ключу получим 244 ГБ вместо 270. И вот тут самое интересное, экономия в 10% обусловлена главным образом не сжатием ключа при сортировке . Проверим, создав таблицу только из отсортированного doc_rk - размер 14.5 ГБ, в исходной табл-е вес этой колонки - 23.5 ГБ. Иными словами, при сортировке в структуре экономии в 10% ( 270 => 244 ГБ) на ужатый ключ приходится лишь 1/3 общего высвобожденного места ( 9 ГБ из 26 ) Феномен, о котором мне сложно судить, т.к. в отличие от 1-го кейса, тут строки в отсортированном наборе не имеют какой-либо связи кроме принадлежности одному сегменту. И даже, если предположить, что соседние doc_rk : N, N+1 определяют одну проводку, такие строки лягут на разные сегменты, ничего не зная друг о друге, тем самым исключая возможность дополнительной выгоды в виде наличия одинаковых атрибутов в паре.

Друзья, начинаем эстафету, делимся в комментах своими Пасхальными изделиями!
Friends, let's start the relay race, share your Easter creations in the comments!

В точку!

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

Сегодня группа Arenadata проведет День инвестора. IPO компании на Московской бирже состоялось в октябре 2024 года.

Спасибо @lyubov_medvedeva за ссылку на конференцию:
https://arenaday.io/live/

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

Если таблица public.foo2 распределена по полю tbl_nm ( содержащая список таблиц, размер котрых хотим получить), а не RANDОMLY,
то рез-ты действительно разные, причем разница в размерах, вычисленная двумя способами кратна числу сегментов.

Почему так, за гранью моего понимания.

One of the readers reported that the recipe above does not work for him.
I checked it on different circuits - it works for me, but there is a nuance.

If the public.foo2 table is distributed by the tbl_nm field (containing the list of tables whose size we want to get), and not RANDОMLY,
then the results are really different, and the difference in sizes calculated in two ways is a multiple of the number of segments.

Why this is so is beyond my understanding.

Секрет II (Владимирский транзит, ветер северный)
Долго думал, как и стоит ли шарить данный секрет ввиду его неочевидной ценности и витиеватости сюжетной линии.
Поэтому занесу его в раздел ништяков
К тому же, на первый взгляд, он не имеет отношения ни к MPP, ни к Postgre, а по сути относится к любой БД.
Думаю, я бы никогда не спросил себя, а значит не создал опрос выше, если бы не
случайное знакомство с 252 "пациентом ", спиллы которых лечу регулярно.
⌛️Сразу скажу, если non-ANSI join вида

a.id in (b.id, c.id) или a.id = coalesce(b.id,c.id)

для вас табу, не тратьте время на инфо ниже.

♻️︎Довелось на неделе тюнить запрос, спилл 21 ТБ
Те, кто используют рез-т A LEFT JOIN B, вероятно используют поля B, если запись B не найдена, через ф-ии
вида decode, coalesce, case when наконец.
И вы же, скорее всего, если в запросе есть JOIN C полученного на предыдщем шаге сета, вместо прямого использования
отсутствующих полей B обернете их в coalesce( b.id, c.<col> ) или что-то пооптимальнее.

Оставим на совести ленивых разрабов использование coalesce в join, который заставил меня переписать запрос ниже тройным UNION ALL, чтобы избавиться от спилла, суть не в этом.

А вот что действительно интересно, это то, что если скажем в иерархической таблице CL, хранящую связь client_rk => parent_clent_rk
у предков самого высокого уровня( интересно как это сказать одним словом ), вместо NULL в parent_client_rk записать терминальную ссылку в никуда,
скажем -1, полагая, что все client_rk > 0 определены на домене положительных чисел, есть шанс облажаться.

Вот и автор реального запроса в нашем ХД выше предполагает, что если LEFT JOIN не найдет строк в CL, то следующий INNER JOIN возьмет данные из TR, и это сработает корректно.

Скелет самого запроса ( убрал лишнее ):

 
select coalesce(cl.parent_client_rk, tr.payee_client_rk)         as client_rk,
       tr.document_date,
       sbp.service_start_date,
       sbp.agreement_num,
       sum(tr.payer_rur_sum)                                     as payer_rur_sum
from agent tr
         left join client_hierarchy cl
                   on tr.payee_client_rk = cl.client_rk
         inner join service sbp
                    on sbp.client_rk = coalesce(cl.parent_client_rk, tr.payee_client_rk) and
                       sbp.service_start_date <= tr.document_date::timestamp
group by coalesce(cl.parent_client_rk, tr.payee_client_rk), 
         tr.document_date, 
     sbp.service_start_date, 
     sbp.agreement_num

Но что, если LEFT JOIN взял строки CL, которые являются корнем иерархии(выше которых ничего нет), и у которых
БА решил, что в parent_clent_rk будет спец код (Don't ask me why.. хотя если по полю нужен индекс, то NULL не есть хорошо) ?

Запрос вообще говоря просто не вернет данные, т.к. на выходе coalesce(cl.parent_client_rk, ... ) будет константа = спец коду.

📌Закон Мерфи - если что-то может пойти не так, оно пойдет не так, тут вполне может напомнить о себе в 100500-й раз,
если SQL разработчик не остановит инерцию мышления, и не проверит, а что хранится в ссылке parent_id у праотцов.

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

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

 
A LEFT JOIN B on a.<col1> = b.<col1> + coalesce (b.<col2>, C.<col>)

p.s.
Не ожидал, что вариант ответа: NULL наберет < 100%

Секрет 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 Мая самым главным праздником. 🔥,кто согласен.

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