Планы запросов: Query plans
Канонический: Classic

Aggregate  (cost=0.00..8282.42 rows=1 width=8) (actual time=2135638.276..2135638.276 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..8282.42 rows=1 width=8) (actual time=1401663.534..2135637.326 rows=348 loops=1)
        ->  Aggregate  (cost=0.00..8282.42 rows=1 width=8) (actual time=1752231.829..1752231.830 rows=1 loops=1)
              ->  Seq Scan on tst  (cost=0.00..4198.52 rows=159303174 width=13) (actual time=1.873..29095.001 rows=159415592 loops=1)
Planning time: 7.709 ms
  (slice0)    Executor memory: 301K bytes.
"* (slice1)    Executor memory: 218876K bytes avg x 348 workers, 218976K bytes max (seg108).  Work_mem: 218743K bytes max, 8814887K bytes wanted."
Memory used:  524288kB
Memory wanted:  8815186kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 2135652.775 ms

Оптимальный: Boosted

Aggregate  (cost=0.00..77329.99 rows=1 width=8) (actual time=604673.360..604673.360 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice2; segments: 348)  (cost=0.00..77329.99 rows=1 width=8) (actual time=604618.549..604673.179 rows=348 loops=1)
        ->  Aggregate  (cost=0.00..77329.99 rows=1 width=8) (actual time=604592.376..604592.376 rows=1 loops=1)
              ->  HashAggregate  (cost=0.00..77329.99 rows=1 width=8) (actual time=604582.705..604582.721 rows=6 loops=1)
                    Group Key: gp_segment_id
"                    Extra Text: (seg0)   Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions."
""
"                    Extra Text: (seg2)   Hash chain length 1.0 avg, 1 max, using 1 of 32 buckets; total 0 expansions."
""
"                    Extra Text: (seg329) Hash chain length 1.0 avg, 1 max, using 6 of 32 buckets; total 0 expansions."
""
                    ->  Redistribute Motion 348:348  (slice1; segments: 348)  (cost=0.00..58070.87 rows=159303174 width=17) (actual time=149192.161..474592.851 rows=881806966 loops=1)
                          Hash Key: gp_segment_id
                          ->  HashAggregate  (cost=0.00..49594.35 rows=159303174 width=17) (actual time=173402.695..498347.846 rows=147062079 loops=1)
"                                Group Key: gp_segment_id, id"
                                Extra Text: (seg154) 147062079 groups total in 160 batches; 33 overflows; 295547324 spill groups.
"(seg154) Hash chain length 10.6 avg, 91 max, using 19521970 of 50855936 buckets; total 46 expansions."
""
                                ->  Seq Scan on tst  (cost=0.00..4198.52 rows=159303174 width=17) (actual time=1.400..27443.299 rows=159415592 loops=1)
Planning time: 15.710 ms
  (slice0)    Executor memory: 495K bytes.
"* (slice1)    Executor memory: 93879K bytes avg x 348 workers, 93923K bytes max (seg0).  Work_mem: 92514K bytes max, 6909525K bytes wanted."
"  (slice2)    Executor memory: 188K bytes avg x 348 workers, 235K bytes max (seg2)."
Memory used:  524288kB
Memory wanted:  13819549kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 604731.078 ms

Когда пожар слишком удобен: южнокорейский цифровой апокалипсис

Сегодня в СМИ разошлась новость из Южной Кореи. В одной из самых технологичных стран мира случился настоящий цифровой коллапс. Горел главный правительственный дата-центр, и после пожара полстраны оказалось без госуслуг. Полиция не работает, таможня не работает, аналог наших Госуслуг лежит.

Власти говорят про халатность рабочих и несчастный случай с литиевыми батареями. Звучит логично, да? Но когда начинаешь разбираться в деталях, появляются очень неудобные вопросы.

Что сгорело на самом деле?

Вот тут начинается самое интересное. Пожар уничтожил 647 сервисов, это факт. Но большинство из них можно восстановить или хотя бы частично вернуть к жизни. А вот одна система испарилась полностью и безвозвратно — G-Drive.

Это цифровое сердце всей южнокорейской бюрократии: 858 терабайт документов — контракты, служебки, переписка, внутренние расследования. Документы 190 тысяч государственных служащих из 74 министерств и ведомств. За восемь лет, с 2017 по 2025 год.

Теперь внимание. G-Drive создавался с 2017 года специально как единое хранилище для всех чиновников. Власти запретили им пользоваться Google Drive (типа импортозамещение) и заставили держать всё в государственной системе.

И вот что удивительно: эту систему намеренно сделали без нормального резервного копирования. Официальное опровдание — "технические сложности из-за большого объема". Серьезно? В 2025 году, когда любой школьник умеет настроить бэкап, правительство самой технологичной страны не может скопировать 858 терабайт в другой регион?

Это всё равно что построить главный банк страны и не поставить на хранилище дверь, потому что "технически сложно".

Резервные копии были... в соседней серверной. Того же здания. Которое тоже сгорело. Естественно.

Дальше больше. У Южной Кореи был план — построить настоящий центр аварийного восстановления в городе Конджу. Защищенный даже от электромагнитного импульса, на случай войны с Северной Кореей.

Проект начали еще в 2012 году. И знаете, сколько его финансировали? Ноль. Тринадцать лет ноль финансирования. При трех разных президентах — и левых, и правых.

Центр достроили к 2025 году, его планировали запустить в октябре. Но на момент пожара 26 сентября он просто стоял пустой.

Кому это выгодно?

А теперь главный детективный вопрос. Посмотрите на хронологию уничтоженных данных: 2017-2025 годы.
2017-2022 — президент Мун Чжэин (левые)
2022-2025 — президент Юн Сок Ёль (правые)
2025 — президент Ли Чжэмён (левые)

Сгорели документы трех администраций. И левых, и правых.

858 терабайт документов — это компромат на всех. На обе партии. На всех министров. На весь бизнес, который работал с властью. На судей, прокуроров, силовиков.

Мы считаем, что это не была операция одной группы. Это был встроенный механизм самоуничтожения, созданный по молчаливому консенсусу враждующих элит.

Система была спроектирована хрупкой намеренно. Как кнопка "стереть всё" для всего госаппарата.

Центр в Конджу не финансировался 13 лет при РАЗНЫХ правительствах. G-Drive делали без нормального бэкапа. Батареи не меняли. Всё это — не ошибки, а функции системы.

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

Официальное следствие, скорее всего, накажет нескольких рабочих и мелкого чиновника. Козлы отпущения найдутся. А те, кто 13 лет создавал эту систему, кто блокировал финансирование защиты, кто проектировал "кнопку обнуления" — они останутся в тени.

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

@darpaandcia

Я вот только не пойму, G-Drive это тот самый сервис Google Drive, которым весь мир пользуется, включая нас с вами?

В итоге, снова командный интеллект рулит, спасибо за подсказу одному из самых активных гуру @novanto , который предложил самый простой вариант как продолжение секрета 42 :

select count(*)
from (
         select distinct id from tst) a;

который улучшил мой результат, а для таблицы с randomly распределением ускорение в среднем 4 раза (3 прогона )

Также благодарю @andre_rumyanec,@GaRin_1979 за снайперские разъяснения того, что я упустил из виду.
План запроса оставлю на память:

Aggregate  (cost=0.00..27274.06 rows=1 width=8) (actual time=472431.983..472431.983 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..27274.06 rows=1 width=8) (actual time=289343.460..472431.051 rows=348 loops=1)
        ->  Aggregate  (cost=0.00..27274.06 rows=1 width=8) (actual time=320763.249..320763.249 rows=1 loops=1)
              ->  HashAggregate  (cost=0.00..27274.06 rows=159303174 width=1) (actual time=178221.741..424697.199 rows=147062079 loops=1)
                    Group Key: id
                    Extra Text: (seg154) 147062079 groups total in 96 batches; 33 overflows; 295092493 spill groups.
"(seg154) Hash chain length 12.3 avg, 99 max, using 21653742 of 68157440 buckets; total 15 expansions."
""
                    ->  Seq Scan on tst  (cost=0.00..4198.52 rows=159303174 width=13) (actual time=0.318..26652.122 rows=159415592 loops=1)
Planning time: 7.094 ms
  (slice0)    Executor memory: 301K bytes.
"* (slice1)    Executor memory: 186409K bytes avg x 348 workers, 186527K bytes max (seg108).  Work_mem: 185027K bytes max, 6914497K bytes wanted."
Memory used:  524288kB
Memory wanted:  6914896kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 472454.119 ms

#никогда не было и вот опять.
Пытаюсь в динамике читать таблу в рамках моей ф-ии
select get_max_value('stg.foo')

Рвется код
query := format('SELECT MAX(version_id) FROM %I', p_table_name);
EXECUTE query INTO max_value;
с ошибкой ERROR: relation "stg.foo" does not exist
В статике
SELECT MAX(version_id) FROM stg.foo
выполняется.
Есть идеи куда копать ?
с SECURITY DEFINER И без проверял.

На заметку
Друзья, если ваш Data Governance созвучен с русским нелитературным словом, или иными словами вы запроектировали вашу ИС так не проактивно, что
понятия не имеете какие из тысяч таблиц вам нужны , а руководство в мыле сообщило, что в ХД дефицит места
и надо срочно что-то удалить, есть идея!
Метрика SOS, которую можно брать на вооружение : pg_stat_all_tables.seq_scan.
Каждый select, delete, update по любой таблице инкрементирует SOS до seq_scan+1,
тогда как служебные analyze, vacuum не аффектят.
Таким образом, сравнив снимок pg_stat_all_tables за период, можно узнать, что поменялось.
Благодарю @AndyDira за находку.
Век живи, век учись!

Note
Friends, if your Data Governance sounds like a Russian slang term, or in other words, you've designed your IS so inactively that
you have no idea which of the thousands of tables you need, and management has emailed you that there's a space shortage in the data warehouse
and something needs to be deleted urgently, I have an idea!
An SOS metric you can use: pg_stat_all_tables.seq_scan.
Every select, delete, or update for any table increments the SOS to seq_scan+1,
while the service functions analyze and vacuum have no effect.

Therefore, by comparing a snapshot of pg_stat_all_tables over a period, you can see what has changed.
Thanks to @AndyDira for the gem.
Live and learn!

Секрет 43 (Аннушка не опять а снова разлила масло или не все то золото, что CTE)
📌
Вероятно, самый известный закон старины Мерфи -
Если что-нибудь может пойти не так, оно пойдёт не так!

Вот еще пример, на этот раз, в противовес секрету, обратная сторона CTE, так сказать.

Я не устаю трубить, что join > 2 таблиц, особенно в MPP - это с большой вероятностью чревато..
Намедни принесли запрос, где идет join 4 таблиц, в.т.ч. 2 - CTE выражения, т.е. подзапросы.

ДНК запроса выглядит так

select * -- набор полей без преобразований
from t1
left join t2 on t2.parent_deal_num = t2.deal_num
left join cte_1 on t1.acc_num = cte_1.main_acc_num and coalesce( t1.x, t2.y) is null
left join cte_2 on t1.deal_num = cte_2.deal_num

где t1,t2 - табл-ы без ключа,
cte_1..2 - подзапросы вида

select DISTINCT id, first_value( ... ) OVER (PARTITION BY id ORDER BY version_id) from t

📌
Отметим размеры выборок:
t1 - 20млн строк
t2 - <100k
cte_1 - 7 млн, полученная из 3 млрд строк
cte_2 - <50k, полученные из небольшого сета
Размер целевого сета: 20 млн строк.
Рез-т плачевный : 360 TB спиллов в 10 запусках такого запроса за 2 дн, каждый из которых шуршал 2ч.
⁉️
А все почему ?
Кодер не озадачился материализовать cte в отдельные таблицы, переключив запрос на которые
он выполнится за 1 мин без спилла.

При этом, уже не имеет значения, что t1,t2 остались RANDOMLY и в плане появилось 2 Broadcast-а

Translation
Secret 43 (How Annushka spilled the oil again or not all that is gold is CTE)
📌
Probably the most famous of old Murphy's Laws:
If anything can go wrong, it will!

Here's another example, this time, as opposed to the secret, the downside of CTEs, so to speak.

I never tire of trumpeting that a join of > 2 tables, especially in MPP, is highly likely to be dangerous.

The other day, someone brought me a query that joins 4 tables, including 2 CTE expressions, i.e., subqueries.

The query's DNA looks like this:

select * -- a set of fields without transformations
from t1
left join t2 on t2.parent_deal_num = t2.deal_num
left join cte_1 on t1.acc_num = cte_1.main_acc_num and coalesce(t1.x, t2.y) is null
left join cte_2 on t1.deal_num = cte_2.deal_num

where t1,t2 are tables without a key,
cte_1..2 are subqueries akind of

select DISTINCT id, first_value( ... ) OVER (PARTITION BY id ORDER BY version_id) from t

📌
Note the sample sizes:
t1 - 20 million rows
t2 - <100k
cte_1 - 7 million, obtained from 3 billion rows
cte_2 - <50k, obtained from a small set
Target set size: 20 million rows.
The result is frustrating: 360 TB of spills in 10 runs of this query over 2 days, each running for 2 hours.
⁉️
And how come?
The coder didn't bother to materialize the cte into separate tables, switching the query to which
it will execute in 1 minute without a spill.

In this case, it no longer matters that t1 and t2 remain RANDOMLY and two Broadcasts appeared in the plan.

Вопрос 6
Пытаюсь выполнить SQL скрипт размером 1 МБ, рвет с ERROR: stack depth limit exceeded/
БД рекомендует Increase the configuration parameter "max_stack_depth" (currently 2048kB)

Я конечно попилю на части, но почему рвется, когда стакан только наполовину полон ?

Кстати, интересно отметить, что
By the way, it is interesting to note that

select seq_scan from pg_stat_all_tables where relname = 'foo' => 5

select sum(seq_scan) from gp_dist_random('pg_stat_all_table')s where relname = 'foo' => 2415

Есть идеи, почему разница ?
Any ideas why there is a difference?

#table2json
На заметку
Note

Если надо выгрузить таблицу в единый JSON, напр. чтобы сложить его по расписанию в Elastic для пост анализа была ли собрана статистика по
объекту( когда его уже нет ),
то пригодится запрос:
If you need to export a table to a single JSON file, for example, to store it in Elastic on a scheduled basis for post-analysis to determine whether statistics were collected for an object (even though it no longer exists), then the following query will be useful:

select json_agg(row_to_json( (s.nspname || '.' || c.relname, c.reltuples) ) )  from pg_class c, pg_namespace s
where s.nspname = 'stg'
and s.oid = c.relnamespace;

Секрет III(Корабли лавировали лавировали, да не вылавировали..или о гибкости кода)
Сказ не столько про MPP, сколько про ♻️SDLC♻️ всея IT Руси (Software Development Life Cycle), поэтому пронумерован отдельной веткой.
Риски того, что цепочка Архитекторы запроэктировали, Программеры закодировали, Тестировщики не заметили нюансы нового алгоритма выльется в деливери говна на пром, иногда реализуются.
📌
Простой пример, основан на реальных событиях.
⚠️в ODS вместо фактического удаления на запись повесили флаг deleted_flg.
И вот после одной из доработок в ХД, ETL посчитал, что если deleted_flg = True, то такие записи просто можно игнорировать при выгрузке, удалены же..
В итоге - 500 потоков отработали некорректно, т.к. соответствующие строки не были удалены в ХД.
Пока заметили, пока оценили масштаб бедствия - прошла неделя.
Дальше хотфикс и перезагрузка данных по PXF за неделю, а это от млн до млрд записей в рамках каждого потока.
Наверное,у многих параметр выгрузки в ETL - интервал времени - это A MUST.
⁉️
Интересно, а у вас поддержаны другие параметры, более ad-hoc чем вездесущее время, напр. категория товаров или список SKU ?

В данном случае, если бы при проектировании кода выгрузки, а точнее фрейма, на либах которого основана каждая,
был поддержан фильтр на deleted_flg как параметр, то пришлось бы перегрузить
не весь объем данных, а лишь 0.01-3% тех самых удаленных, сэкономив тем самым OPEX на ресурсы пересчета и нервы руководства, а банально время. А время - Деньги!
Если развить идею, то хорошо бы предусмотреть на этот случай мастер-поток, который запустит по списку некорректные потоки с одним и тем же параметром, избавляя DevOps от ненужной работы
по запуску вручную каждого из них.
Да, если есть зависимости между потоками, это уже сложнее, но тоже решаемо,особенно если замотивировать команду на старте проекта, а не как мы любим - хуяк и в пром -)

Повторюсь, за ошибки фундамента приходится дорого платить, и жизнь нас постоянно этому учит.

Translation
Secret III (The ships tacked and tacked, but couldn't catch... or about code flexibility)
This story isn't so much about MPP as it is about the ♻️SDLC♻️ of all IT Rus' (Software Development Life Cycle), which is why it's numbered in a separate branch.
The risks of the chain of "Architects designed, Programmers coded, Testers overlooking the nuances of a new algorithm" sometimes materializing.
📌
A simple example based on real events.
⚠️In ODS, instead of actually deleting a record, they set the deleted_flg flag.
And after one of the DWT modifications, the ETL decided that if deleted_flg = True, such records could simply be ignored during dumping, since they were deleted...
As a result, 500 streams failed to execute correctly because the corresponding rows weren't deleted in the DWT.
By the time they noticed and assessed the scale of the disaster, a week had passed.

Next, a hotfix and a reload of the PXF data for the week, which amounts to millions to billions of records within each stream.

For many, the time interval dump parameter in ETL is probably a MUST.
⁉️
I wonder if you support other parameters, more ad-hoc than the ubiquitous time, for example. Product category or SKU list?

In this case, if the deleted_flg filter had been supported as a parameter when designing the upload code, or more precisely the frame on which each library is based, then it would have been necessary to reload
not the entire volume of data, but only 0.01-3% of that deleted data, thereby saving OPEX on recalculation resources and management stress, and simply time. And time is money!
If we take this idea further, it would be a good idea to provide a master flow for this case that would launch incorrect flows with the same parameter from the list, saving DevOps from the unnecessary work of manually launching each of them.
Yes, if there are dependencies between flows, this is more complicated, but also solvable, especially if you motivate the team at the start of the project, rather than our usual approach of "bam, off to production" -)

I repeat, mistakes in the foundation come at a high price, and life constantly teaches us this.

Вопрос 7
#никогда не было и вот опять
Есть ф-я foo, возвращает table, в декларации
RETURNS TABLE ( ... )
LANGUAGE sql
STABLE

Запрос рвет select * from dm.foo('2025-12-31') с ошибкой превышения квоты на спилл файл:
ERROR: workfile per query size limit exceeded
Если склонировать функцию и ее обвязку, т.е. таблицы для SQL запроса, что она выполняет ( там он один - набор 3х UNION ALL с тривиальными GROUP BY на каждый) в public,
то клон выполняется за 15 сек.
В плане select * from public.foo('2025-12-31') GPORCA, статистика собрана
Ваши версии ?

Секрет 44 (Трудности перевода)
Продолжая последний вопрос https://t.iss.one/mpp_secrets/305, бдительный коллега ткнул меня носом в тот факт, что в функции клоне я допустил одну мутацию,
а именно параметр функции (Дата, зашитая в строку) хардкодом пробил в тело функции, тогда как в исходом варианте в теле функции явно используется именно параметр.
Сам себе в ногу выстрелил, что тут сказать.
Что ж, ответ на вопрос тем самым закрыт, что радует.
Но тут, не благодаря, а вопреки, случилось и открытие, которое было мне неочевидно до этого, и оно категорически не радует.

Получается, можно сделать вывод, что функция в GP не всегда, и может и никогда, умеет делать подстановку параметра в SQL запрос оптимальным образом.

В данном случае, в первом варианте, оптимизатор используется Legacy, а не GPORCA.
В итоге, в плане

select * from dm.foo('2025-12-31')

появляется Broadcast сета в 300 млн строк из таблицы, по которой считается актуальны срез на версию.
Иными словами, идет join таблицы в 1 млрд строк на аналитическую выборку из нее же, которая тиражируется на все сегменты со всеми вытекающими (спиллами и абортом транзакции) :

select * from dm.t
join (select a,b,c max(version_id) as version_id from dm.t
group by a,b,c)
using (a,b,c version_id)

Данная трансформация просто для наглядности И не имеет никакого отношения к сделанному открытию, т.к. в проблемной ф-ии используется уже вью вида выше для генерации актуального среза,
по которой считается трижды GROUP BY, каждый со своими метриками.

Для внимательного читателя эта новость уже не нова ( как в том сальном анекдоте про Шварцнеггера) , т.к. уже ранее подсвечивал, что PL/pgSQL функции часто выполняются не в GPORCA, но в контексте находки, это определенно
стоило бы усовершенствовать.
Ну т.е. выходит, если в функции используется статический запрос без параметров, он выполнится в GPORCA, а с параметром - в Legacy ?
Ну есть же replace, в конце концов, чтобы подсказать оптимизатору, что он в одном простом шаге от того, чтобы выполнить запрос внутри функции оптимально, пробив параметр сразу, до компиляции запроса.
Реально, такая доработка компилятора GP бы решила множество проблем, которые проходят через меня

Кто в курсе, SQLLite 3.31 умеет CTE из CTE, а-ля

WITH
off_set as
(select distinct master_id from Sheet1 as a
where not exists (select 1 from Sheet1 where sculpture_type = a.sculpture_type and master_id = 'Benjamin'))
select lower(master_id) from Sheet1 where master_id not in (select master_id from off_set) 
and master_id != 'Benjamin'
order by 1

?

#Yandex Cup 2025
По фану, заскочил в последний вагон и зарегался на глобальный ивент от Яндекса (финал пройдет в Стамбуле) в последние минуты несколько дней назад.
Сегодня был контест, о котором я с опозданием узнал случайно из рассылки (my bad), в итоге из регламентных 5 ч на решение 6 задач у меня было 3 с небольшим.
150 место из 537 неплохой результат, с учетом, что уйму времени убил на простую с виду задачку по SQL, которая дает макс. число баллов (8) из представленных,
что на фоне зубодробительных задач по теорверу даже многовато, хотите попробовать ее решить ? 30 лайков и она ваша
https://new.contest.yandex.com/contests/83967/leaderboard?pageSize=100&page=5

Кстати, пока плавал на мелководье sqlite3 ( решение задачки требовалось для этой БД) обнаружил, что в ней
1. Имеется Vacuum, правда таблу не умеет, как м приатаченные базы, только main db
2, Distinct в фильтре not in ( или в not exists ) приводит к деградации запроса, в отличие от GP ( был лимит на run-time итогового SQL скрипта)
3. Помимо LIKE есть более продвинутая GLOB(Unix file globbing syntax), которая может использовать спец символы:
*: соотве-ет любому количеству символов
?: - одному символу
.: - любому одиночному символу
[символы]: любому одиночному символу из списка внутри скобок ([abc])
[начальный_символ-конечный_символ]: любому одиночному символу из диапазона ([a-zA-Z0-9])
^: этот символ используется в начале списка символов и соответствует любому символу, который НЕ входит в список ([^0-9])
Из минусов, функция case-sensitive

На заметку
Оказывается, несмотря на то, что функция EXCEPT очищает результат от дублей,

select id from A
except
select id from B

работает заметно хуже

select id from A
except
select distinct id from B

особенно, когда B густозаселена дублями (1 трлн ключей vs 1.8 млрд уникальных в моем случае)
В частности, 2й запрос поможет избежать ошибки workfile per query size limit exceeded, вызванной 1-м.
Век живи - два учись!
Note
It turns out that although EXCEPT function deduplicates the result,

select id from A
except
select id from B

performs significantly worse

select id from A
except
select distinct id from B

especially when B is heavily populated with duplicates (1 trillion keys vs. 1.8 billion unique ones in my case).
In particular, the second query will help avoid the "workfile per query size limit exceeded" error caused by the first one.
Live once, learn twice!