#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!

#hive, hive, как это мило ...
Секрет 45(О важности разметки холодных данных)
В DataLake обновили кодовую базу, и меня попросли принять активное участие в тестировании
интеграции GP и Hadoop.

Здачка простая - проверить загрузку таблицы Hive в GP.

Я что-то подобное делал год назад, ограничившись тогда таблицей в 1 млн строк.
В этот раз я решил повысить ставки, и получить бенчмарк на табле в 1 млрд строк.

Создав строку в таблице foo в Hue интерфейсе к Hive, я стал ее клонировать, рассчитывая, что через 30 итераций одного и того же запроса получу 1 млрд строк:

insert into foo
select * from foo

Тут все прошло гладко, как по маслу.
⚠️
Пробую загрузить полученный сет в pxf_foo в GP через CTAS по PXF и отлуп.

Проверяю, что если ограничить по LIMIT:

select * from pxf_foo limit 10

ERROR: PXF server error : Fetch EMA 0 bytes/sec dropped below threshold (1,048,576), aborting.

Проверяю заливку старой табл-ы 24 г из 1 млн - все ОК, foo_24 загружается в GP без проблем.
⁉️
Гипотеза: видимо GP не может заглотить всю таблицу из 1 млрд, при чем, т.к. LIMIT отсекает сет не до, а после, то и 2й запрос рвет.

И тут я понимаю, что гипотезу на созданной синтетике не проверить, ибо все записи в созданной на скору руку синтетике - дубли,
а нужно взять часть, чтобы убедиться, что она вычленяется
на удаленной базе через PDO ( проброс предиката в удаленную БД, aka Pushdown Optimization)

Тогда решил получить сет из 1 млрд строк, взяв старый миллионник от 24 г foo_24 и склонировал его так, чтобы полных дублей в нем было 1 млн.

Для этого я добавил в новый сет поле date2, которое пробегает диапазон в 1000 дн начиная с date1 ( существующее поле в foo_24 = '1900-01-01' )
и использовал аналог функции generate_series в Hive:

create table foo_25_1b as
select  t.*
       ,date_add (t.date1,pe.i) as date2

from    foo_24 t
        lateral view 
        posexplode(split(space(datediff(date_add (t.date1+100),t.date1)),' ')) pe as i,x

Таким образом, в hive я получил таблицу из 1 млрд строк, но теперь в нем есть поле селектор - date2 ( без которого строки опять же полные дубли ).

Теперь в GP запросы к pxf_foo_rng ( внешняя табла на foo_25_1b в Hive ) вида :

select * from pxf_foo_rng
where date2 = '1900-01-01'::date
limit 10

select * from pxf_foo_rng
where date2 = '1900-01-01' -- 1 млн строк

select * from pxf_foo_rng
where date2 between '1900-01-01'::date and  '1900-01-10'::date -- 10 млн строк

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


📌Мораль - если охлаждаете данные в Hive и прочие озера данных, откуда потребуется их поднять, обязательно предусмотрите возможность их выгрузки в GP
по диапазону, которым сами сможете управлять.
Маркером такого диапазона может быть дата, номер батча первичного охлаждения , просто кэшированный сиквенс, но что-то, что размечает ваши данные на потенциальные пачки для обратной выгрузки.

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

В мастерской скульпторов в Константинополе XII века создаются декоративные элементы для дворцов. Мастерская имеет богатую и давнюю историю: за многие поколения здесь накопились записи о многих произведениях. Мастера ведут подробный реестр работ, где фиксируются сведения о каждом изделии:

уникальный номер скульптуры id (float);

имя мастера, выполнившего работу master_id (string);

дата завершения creation_date (string);

дата продажи sell_date (string);

тип скульптуры sculpture_type (string).

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

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

Input format
Ваша задача - написать код на SQLite, который выводит имена строчными буквами упорядоченные по алфавиту. Обращаться к изначальной таблице можно через название Sheet1. Вы можете потестировать свой код на открытом датасете: скачать database_sample.xlsx, к примеру с помощью библиотеки sqlite3.

Проверка в контексте осуществляется на другом датасете, но гарантируется, что тестовый датасет содержит все возможные сценарии ошибок.

Notes
Скульптуры могут быть проданы по предзаказу сделанному сильно заранее до изготовления.

К задачке прилагается файлик

Секрет 26,ч2. (Я знаю, что ничего не знаю: наводим резкость на small data )
Случился прецедент за гранью моего понимания, и по делу проходит снова справочник без статы:
на проме запрос

select cap_.agreement_rk, vc4_.product_bp_nm
from mart$v as cap_
         join dict_product as vc4_
              on cap_.product_bp_cd = vc4_.product_bp_cd
where cast('2025-11-26' as date) >= cap_.effective_from_dttm
  and cast('2025-11-26' as date) < cap_.effective_to_dttm;

ежедневно отрабатывает в среднем за 1.5 мин, без проблем, как ни странно,
где mart$v - SCD2 вью для отбора последних версий, с идеей которой мы уже знакомы:

select *
from public.foo a
         join (
    select n, max(version_id) version_id from public.foo where version_id between :v_min and :v_max group by 1) c
              using (n, version_id);

dict_product - пересобираемый под запрос тривиальный справочник в 1500 строк ( коды которых уникальны ), по которому стата не собрана, согласно параметров GUC.

Начиная с конца недели запрос стал падать по спиллу > 30 TB, при том, что особо ничего не поменялось.
В моей реальности, запрос тогда уж должен бы падать всегда, согласно Секрет 26, и объяснить нормальное поведение ДО начала деградации я не в состоянии.

Мне захотелось что-то выжать из этой аномалии, и вот что я узнал.
Если материализовать вью

 
select 
from mart$v
where '2025-11-26'::date >= effective_from_dttm
  and '2025-11-26'::date) < effective_to_dttm;

в таблицу tst_mart из 1 млрд строк, то запрос

explain analyze
select cap_.agreement_rk, vc4_.product_bp_nm
from tst_mart as cap_
         join dict_product as vc4_
              on cap_.product_bp_cd = vc4_.product_bp_cd

отбегает за 2м 18с, но при этом на материализацию tst_mart ушло 6 мин.

Если заглянуть в план, то ожидаемо увидим Broadcast дикта, ибо он без статы, что не критично :

Gather Motion 384:1  (slice2; segments: 384)  (cost=0.00..1507.73 rows=1 width=264) (actual time=9.319..108483.528 rows=990063918 loops=1)
  ->  Hash Join  (cost=0.00..1507.73 rows=1 width=264) (actual time=9.950..1315.095 rows=2583044 loops=1)
        Hash Cond: (tst_mart.product_bp_cd = (tst1500.product_bp_cd)::text)
"        Extra Text: (seg40)  Hash chain length 1.0 avg, 2 max, using 1498 of 524288 buckets."
        ->  Seq Scan on tst_mart  (cost=0.00..498.18 rows=2775858 width=14) (actual time=0.290..450.133 rows=2781093 loops=1)
        ->  Hash  (cost=431.15..431.15 rows=1 width=264) (actual time=6.116..6.116 rows=1500 loops=1)
              ->  Broadcast Motion 384:384  (slice1; segments: 384)  (cost=0.00..431.15 rows=1 width=264) (actual time=0.023..5.667 rows=1500 loops=1)
                    ->  Seq Scan on tst1500  (cost=0.00..431.00 rows=1 width=264) (actual time=0.537..0.544 rows=12 loops=1)
Planning time: 13.347 ms
  (slice0)    Executor memory: 788K bytes.
"  (slice1)    Executor memory: 268K bytes avg x 384 workers, 268K bytes max (seg0)."
"  (slice2)    Executor memory: 4805K bytes avg x 384 workers, 4805K bytes max (seg0).  Work_mem: 145K bytes max."
Memory used:  2129920kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 138661.777 ms

А теперь к сути поста, которая меня крайне заинтересовала.

Если в исходном запросе в справочнике удалить все строки, или оставить, скажем одну из 1500, запрос отрабатывает на раз ( за 2 и 4 с соотв-но )
Это было неожиданно на фоне предыдущих секретов про пустые таблицы (Секрет 1,6).
Проверил снова запрос на полном справочнике - через 9 мин рухнул с workfile per query size limit exceeded.

Возникла шаткая гипотеза, что Broadcast сета из вью mart$v идет только на те узлы, где лежат строки справочника, что хоть как-то объяснило бы, почему запросы отработали.

Т.к. фактический план падающего запроса не проверить, я решил сравнить
теоретический план EXPLAIN с планом EXPLAIN ANALYZE, полученным из запроса на справочнике из 1 строки без статы.

Оказалось, планы совпадают, а моя гипотеза несостоятельна.
А дело вот в чем.
Анализ плана (ниже фрагмент его критичных слайсов) дал следующий инсайд :
На первом шаге идет также Broadcast справочника, который Hash Join-им с большой табл-ой под вью, и вот этот результат Oops.. снова Broadcast-им.
Соотв-но, последний Broadcast для случая 1 строки в справочнике пересылает в 1500 строк меньше, чем для полного.
Для 0 строк в справочнике последний Broadcast соотв-но просто номинальный шаг.

Остается добавить, если выполнить исходный запрос на справочнике с собранной статой, запрос выполнился без спилла за 8.5 мин с ожидаемым Redistribute, но у меня и РГ другая,
не такая крутая, как у регламентного потока на проме.
Остается загадкой, а зачем нужен 2й Broadcast, кроме первого, который дал нам возможность сделать Hash-Join

К секрету 26,ч.2

В Лас-Вегасе стартовал финал чемпионата мира по Microsoft Excel. 24 финалиста в течение трёх дней решают сложные кейсы на вылет в легендарной HyperX Arena, где обычно соревнуются киберспортсмены. На кону рекордные $60 000 (4,7 млн рублей), а победитель получит чемпионский пояс в стиле WWE.
The Microsoft Excel World Championship finals have kicked off in Las Vegas. Over three days, 24 finalists will solve challenging knockout cases at the legendary HyperX Arena, where esports athletes typically compete. A record-breaking $60,000 (4.7 million rubles) is at stake, and the winner will receive a WWE-style championship belt.

Правильный ответ на опрос выше - 1(Да). Если в запросе есть сортировка ORDER BY, возможен спилл.
Напомню, что в ITAS(CTAS - не суть) это может давать хороший выигрыш по месту, о чем писал неоднократно, напр.

The correct answer to the poll above is 1.If the query contains an ORDER BY clause, a spill is possible.
As a reminder, in ITAS(CTAS - no matter), this can yield significant space savings, as I've written about repeatedly, for example:

А вы знали, что если в ватс апп сменить язык на болгарский то звонки работают без впн( хак от коллеги, не проверял пока)