Вопрос 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).

Весенний бигдатник от sql-ninja.ru
22 марта в 10:20 на ст.м. Таганская,
Марксистская в БЦ Таганка Атриум

Как и просили:
Персональные данные, Архитектура, Cloudberry, Postgresql, Clickhouse, Spark.
Конечно кейтеринг, кофе, нетворкин и да, онлайн будет 😉

купить билет

erid: CQH36pWzJqDgK9RNVKVdELsKuxSzntDejEYYuuqwJG1fTU
Реклама, ИП "Ким Лестат Альбертович", ИНН 183403475084

Translation of last published secret:
Secret 31 (Break for happiness!)
Friends, stock up on popcorn. There will be a longread, because there was not much time to shorten the material that I am rushing to share.

I have long wanted to get to know the GP partitions face to face,
especially against the background of rumors about them, often unflattering.

Moreover, my interest was fueled by 15 years of experience with Oracle, where I was absolutely convinced that the partitioning strategy plays a primary role
in the performance and maintenance of the product.
Here, the management sent R&D the task of testing the pros and cons of partitioned tables.

I set the task, and for how long does it make sense to fragment the data, in other words, find the maximum number of partitions,
when the table can still be worked with.

Because in the doc the limit on the number of partitions at each level = 32,767, I decided to go with the simplest options and try to get closer to it.

I took a representative sample of 7 million rows from a document table with 74 billion rows, 1 million for each data source, and
began to save this data in a partitioned table of the same structure with 92 fields.

The test plan was as dumb as a felt boot.
I took a single-level partitioning by date.
In each test, I created a partitioned table of the type from scratch, where I wrote the prepared 7 million rows, then read a small sample from the resulting table
with the filter document_date between :1 and :2.
The only thing is that in each record document_date was filled with a randomly distributed uniform value DATE in the range from 2000 to the end of 2025 year.
Having started with a table of 50 partitions (hereinafter N), I added another 50 each time (By the way, the film "Another Round" is ecstasy, I watched it several times)
I.e. in each iteration, only the EVERY parameter (the length of the partition slicing interval) changed from '190 days' to '1 days' for the animal:

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
          );

I put all the scripts into a separate config table (3 operators for each test: CREATE TABLE, INSERT, SELECT), which I ran through the ubiquitous explain analyze,
which will execute them and save the plan in JSON (using the technology described in https://t.iss.one/mpp_secrets/39)

After loading the script overnight, I discovered in the morning that the test was aborted on insert into a table with a partition size of 9 days. or with N=1055:
can't have more than 10000 different append-only tables open for writing data at the same time.
Looking into the DB parameters, I found the max_appendonly_tables parameter, which sets the very limit on the number of append-only tables
that can be written to simultaneously( i.e. 10 000 ).
At first I was confused, because I was writing 1055 tables within the INSERT (a partition is a table), which did not explain the crash!
Then it dawned on me that PG is not ORACLE and since DDL does not commit a transaction, and all my tests were performed in a loop in one transaction, there could have been a cumulative effect from previous tests.
In any case, I decided to make a knight's move and check the table with N = 9497 or with a partition size of 1 day in a separate session.
Insert operator result: again crap, but with a high VMEM usage error, which I can explain by the fact that we have an AOCO table (column-based) and files for such a table are written simultaneously 92 (columns) * 9497 (partitions), which, apparently, the DB did not digest.
to be continued

Translation of last published secret, part 2 of 2:
Well then I had to weaken the structure and instead of AOCO take AORO:
WITH (
appendonly=true,
orientation=row,
compresstype=zstd,
compresslevel=1
)
In such a table with a border N = 9497 insert eventually finished correctly.
You will say that all this is well, but is the game worth the candle?
I put 1 billion rows into foo using the same principle and put the same set into a flat foo_no_part (i.e. without parts) table.
I measured select ~1% of rows in 3 tests (via explain), the 1st test gave a small gain, but the average turned out to be less in the end for the flat one (in the photo tests D2:F6).
How is that possible? All in vain? I thought that because there are 348 segments in the DB, then on average there are 3 million rows per segment for a flat table, which are full-scanned faster
than the necessary partitions in foo. To smooth out the overhead of extracting data from partitions, I raised the stakes and dropped 10 billion rows into both.
I again ran 3 tests for select ~1% of rows (95 million to be exact) and justice prevailed:
On explain foo broke ahead in all 3 tests, and in one of them twice (tests A1:C6)
And to exclude the influence of sending rows to the coordinator, I ran 3 more CTAS tests, saving the results of selects from previous tests from foo and foo_no_part into separate tables with
randomly distribution (A9:C13). The result on partitions gives a 6-fold speedup.

The most pedantic people will ask and will be right - what about the time of inserting 10 bln rows into foo and foo_no_part.
Answer: 42 min 54 sec and 15 min 49 sec respectively, which is expected, since it is faster to insert data into a table of the simplest structure.
However, it is worth noting that foo_no_part simply had CTAS from foo, and foo inserted 1 billion, replicated via generate_series(1,10)
and for each row the partition key document_date was calculated via '2000-01-01'::date + trunc(random()*9496))::int.

Conclusion: Partitions can give a noticeable gain to a business (Time is Money!) depending on the ratio of the number of rows in the table and the number of cluster segments.
So, already with ~30 million table rows on each segment it is 6x!

slide for english version

Секрет 32 (Остерегайтесь функций!)
Юбилейный секрет( для акул разработки в GP ) решил посвятить функциям, куда же без них.
Кому-то он покажется лишним, как зуб мудрости, но тем не менее, поверьте, рано или поздно каждый третий вспомнит про него.
Действительно, далеко не все задачи решаются SQL запросом(ами), иногда приходится прибегать к циклам, ветвлениям и упаси боже рекурсиям.
Пример такой задачи есть у меня, плавали-знаем - корреляция телеком-трафика, когда надо смэтчить 2 плеча 1 звонка: входящее и исходящее, по гибким правилам, где фиксированный ключ -
это тел. номер, и то после его стандартизации, а длит-ть и время вызова - нечеткие ключи, т.к. ни разу не обязаны совпадать.
Его величество, задача из класса гибкого мэтча. Нетривальна, как может показаться на первый взгляд.

Так вот тем, кому придется взять (или уже взяли как мы) на вооружение функции, думаю, пригодится список ниже.
Суть в том, что в случаях нижеперечисленных типов запросов, при выполнении ф-ии используется не GPORCA планировщик, а Legacy(PostgreSQL).
Такие кейсы не поддерживаются оптимизатором GPORCA по умолчанию и при выполнении такого запроса( из ф-ии) в логе Greenplum будет запись
Falling back to Postgres-based planner because GPORCA does not support the following feature: UTILITY command.
Если нет доступа к логам БД, то проверить выбранный планировщик можно в плане запроса

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

♓️ - SP-GiST индексы. GPORCA поддерживает только B-tree, bitmap, GIN, and GiST индексы, игнорируя все другие типы индексов.
♒️ - Использование в SELECT выражениях TABLESAMPLE
♑️ - Использование WITH ORDINALITY в SELECT
♐️ - Multi-level partitioned tables
♏️ - Non-uniform partitioned tables
♎️ - SortMergeJoin (SMJ)
♍️ - Ordered aggregations
♌️ - Multi-argument DISTINCT qualified aggregates, e.g. SELECT corr(DISTINCT a, b) FROM foo
♋️ - Multiple grouping sets specified using a duplicate alias in a null-producing grouping set spec.
Такие запросы откатываются к Legacy планировщику, если только вы напрямую не присвоите псевдоним отдельной переменной, как в примере ниже:

CREATE TABLE foo AS SELECT i AS ai1, i AS ai2 FROM generate_series(1, 3)i;
SELECT ai1, ai2 FROM foo GROUP BY ai2, ROLLUP(ai1) ORDER BY ai1, ai2;

♊️ - Скалярные операторы:
🔸ROW
🔸ROWCOMPARE
🔸FIELDSELECT
♉️ - Aggregate functions that take set operators as input arguments // Не смог сход придумать пример в этом роде, поэтому без перевода - кто знает о чем речь, блесните в коментах!
♈️ - Множественные агрегаты над DISTINCT не поддерживаются GPORCA по дефолту, напр.

SELECT count(DISTINCT a), sum(DISTINCT b) FROM foo

, но дока гласит, что это можно исправить параметром
optimizer_enable_multiple_distinct_aggs ( не проверял )
♟ - percentile_* window functions (ordered-set aggregate functions)
♟ - Inverse distribution functions.
♟ - ф-ии, содержащие символы UNICODE в названиях таблиц и пр. метаданных, либо символы, несовместимые с локалью ОС, где они исполняются
♟ - SELECT, UPDATE, и DELETE запросы, где в локации таблицы используется ONLY
♟ - Per-column collation. GPORCA supports collation only when all columns in the query use the same collation.
Те, кто кодил под Teradata, наверное знают, что такое collation и как неожиданно она может влиять на рез-т сортировки по полю.
♟ - DML and COPY ... FROM operations on foreign tables.
♟ - Unsupported index-related features include:
🔸Index scan on AO tables
🔸Partial dynamic index scan
🔸Partial indexes
🔸Forward and backward dynamic index and dynamic index-only scans on partitioned tables
🔸Indexed expressions (an index defined as an expression based on one or more columns of the table)
🔸Combined indexes
♟ - ф-ии созданные с опцией ON COORDINATOR, ON ALL SEGMENTS.
К слову, узнать режим выполнения ф-ии можно в pg_proc.proexeclocation: m - master only , s - all segments ( Спасибо @reshke за подсказку! Напомню, он
в списке участников в весеннем BigDat-нике, ссылку на который кидал 11 марта)

☯️Ну что, оказывается не все так просто с функциями в GP?
Мне остается добавить, что мы на проекте по требованиям Архитектора тоже базово предоставляем доступ к данным через функции, где идет отбор последних версий из SCD2 таблиц
запросом вида

select a.* from foo a join (  select account_rk,effective_date, max(version_id) version_id
from foo where version_id<=p_version_id
group by account_rk,effective_date) b 
using (account_rk,effective_date,version_id)

И хотя такой типовой SQL запрос не попадает ни в одну из перечисленных категорий трансформаций, видимо, именно опция создания ф-ии ON ALL SEGMENTS приводит к тому, что
такая ф-я выполняется Postgre оптимизатором, что крайне неэффективно, как по времени выполнения, так и по размеру спилла(10-20 TB).
Сам ее SQL запрос отрабатывает без него и на порядок быстрее.

Translation of last published secret:
Secret 32 ​​(Beware of functions!)
I decided to dedicate the anniversary secret (for GP development sharks) to functions, where would we be without them.
It may seem superfluous to some, like a wisdom tooth, but nevertheless, believe me, sooner or later every third person will remember about it.
Indeed, not all tasks are solved by SQL query(s), sometimes you have to resort to cycles, branches and, God forbid, recursion.
I have an example of such a task, we've been there before - correlation of telecom traffic, when it is necessary to match 2 legs of 1 call:
incoming and outgoing, according to flexible rules, where the fixed key is the phone number, and then after its standardization,
and the duration and time of the call are fuzzy keys, because they are never required to match.
His Majesty, a task from the flexible match class. Non-trivial, as it may seem at first glance.

So for those who will have to take (or have already taken, like us) the functions into service, I think the list below will be useful.

The point is that in cases of the following types of queries, when executing the function, not the GPORCA planner is used, but Legacy (PostgreSQL).
Such cases are not supported by the GPORCA optimizer by default and when executing such a query (from the function), the Greenplum log will contain the entry
Falling back to Postgres-based planner because GPORCA does not support the following feature: UTILITY command.
If there is no access to the DB logs, then you can check the selected planner in the query plan.

♓️ - SP-GiST indexing method. GPORCA supports only B-tree, bitmap, GIN, and GiST indexes. GPORCA ignores indexes created with unsupported methods.
♒️ - The SELECT command's TABLESAMPLE clause
♑️ - The optional WITH ORDINALITY clause of a function call inside the FROM clause of a SELECT command.
♐️ - Multi-level partitioned tables
♏️ - Non-uniform partitioned tables
♎️ - SortMergeJoin (SMJ)
♍️ - Ordered aggregations
♌️ - Multi-argument DISTINCT qualified aggregates, e.g. SELECT corr(DISTINCT a, b) FROM foo
♋️ - Multiple grouping sets specified using a duplicate alias in a null-producing grouping set spec.
Such queries fall back to the Postgres-based planner unless you directly coerce the alias to a separate variable as shown in the example below:

CREATE TABLE foo AS SELECT i AS ai1, i AS ai2 FROM generate_series(1, 3)i;
SELECT ai1, ai2 FROM foo GROUP BY ai2, ROLLUP(ai1) ORDER BY ai1, ai2;

♊️ - These scalar operators:
🔸ROW
🔸ROWCOMPARE
🔸FIELDSELECT
♉️ - Aggregate functions that take set operators as input arguments // Indeed any examples of that case are welcome in comments
♈️ - Multiple Distinct Qualified Aggregates, such as below are not supported by default

Translation of last published secret, part 2 of 3:

SELECT count(DISTINCT a), sum(DISTINCT b) FROM foo

They can be enabled with the optimizer_enable_multiple_distinct_aggs
♟ - percentile_* window functions (ordered-set aggregate functions)
♟ - Inverse distribution functions.
♟ - Queries that contain UNICODE characters in metadata names, such as table names, and the characters are not compatible with the host system locale.
♟ - SELECT, UPDATE, and DELETE commands where a table name is qualified by the ONLY keyword.
♟ - Per-column collation. GPORCA supports collation only when all columns in the query use the same collation.
Those who've coded for Teradata probably know what collation is and how unexpectedly it can affect the result of sorting by a field.
♟ - DML and COPY ... FROM operations on foreign tables.
♟ - Unsupported index-related features include:
🔸Index scan on AO tables
🔸Partial dynamic index scan
🔸Partial indexes
🔸Forward and backward dynamic index and dynamic index-only scans on partitioned tables
🔸Indexed expressions (an index defined as an expression based on one or more columns of the table)
🔸Combined indexes
♟ - Queries that run functions that are defined with the ON COORDINATOR or ON ALL SEGMENTS attribute.
By the way, you can find out the function execution mode in pg_proc.proexeclocation: m - master only, s - all segments
(Thanks to @reshke for the tip! Let me remind you, he is on the list of participants in the spring BigData meetup, the link to which I posted on March 11)

Translation of last published secret, part 3 of 3:
☯️Well, it turns out that not everything is so simple with functions in GP?
I have to add that on the project, according to the Architect's requirements, we also provide basic access to data through functions, where the latest versions are selected from SCD2 tables
with a request like

select a.* from foo a join (  select account_rk,effective_date, max(version_id) version_id
from foo where version_id<=p_version_id
group by account_rk,effective_date) b 
using (account_rk,effective_date,version_id)

And although such a typical SQL query does not fall into any of the listed categories of transformations, apparently, it is the option of creating the ON ALL SEGMENTS function that leads to the fact that such a function is executed by the Postgre optimizer, which is extremely inefficient, both in terms of execution time and the size of the spill (10-20 TB). Its SQL query itself is processed without it and an order of magnitude faster.

Секрет 33 ( UNION + функция = полная деструкция )

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

Засветился вчера запрос вида

 SELECT response_id
 FROM foo$v r, stg.foo a  WHERE r.contact_id = a.contact_id
 UNION
 SELECT response_id  FROM foo_fn(0, :p_version_max).
 

Спилл 38 TB. Отличились! В этом году - это топ!

foo$v и foo_fn - это вью и ф-я, которые смотрят на одну и ту же партицированную помесячно табл-у из 62 секций,
так называемые аксессоры(в наших термиах), полного и интервального среза.
foo - временная табл-а.

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

 SELECT response_id FROM t1
 UNION
 SELECT response_id FROM t2
 

выполняется без спилла.
При этом, запросы создания t1 и t2 спилл не создают ( суммарно 0.1 TB для t1 из 1.9 млрд строк, 0.05 TB для t2 из 1.2 млрд строк на кластер из primary 1056 сегментов).

Диагноз проблемы: Запрос исполнил легаси оптмизатор (PostgreSQL), а не GPORCA, ибо в запросе есть функция из класса Legacy т.к. создана ON ALL SEGMENS.
explain analyze зверушки выполнить не смог под моей РГ, но explain ( тут не привожу, 480 строк - ту мач ) выявил Broascast Motion в первом подазпросе( тот, что без ф-ии ),
что видимо и объясняет наличие исходного спилла. При чем, что интересно, Broascast появляется при обращении к вью, и вот этот датасает
уже претерпевает Redistribute при join-е с stg.foo, т.к. ключи обоих датасетов не согласованы.

Если же выполнить

 explain analyze
 SELECT response_id
 FROM foo$v r, stg.foo a
 WHERE r.contact_id = a.contact_id
 

то в плане Broascast-а нет, только Redistribute.