Проверим, а всегда ли нас ждет такой печальный конец для оконной функции?
Перераспределим foo по ключу invalid_id ( для примера - синтетика )
Let's check if such a sad end always awaits us for the window function?
Let's redistribute foo by the invalid_id key (for example - synthetics)

create table public.tst WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select generate_series(1, 1000) invalid_id, 1 version_id, random() hash_diff distributed by(invalid_id);

Видим, что Redistribute Motion исчез, потому что теперь на каждой ноде есть все данные для локального выполнения запроса ввиду выбранного хэша табл-ы:
We see that Redistribute Motion has disappeared because now each node has all the data for local query execution due to the selected table hash:

explain analyze
SELECT row_number()
                                       OVER (PARTITION BY rdv_src.invalid_id ORDER BY rdv_src.version_id DESC) AS rdv_wf$vsn_rank,
                                       rdv_src.hash_diff                                                       AS hash_diff,
                                       rdv_src.invalid_id                                                      AS invalid_id
                                FROM public.tst AS rdv_src
                                                                                                                      
Gather Motion 720:1  (slice1; segments: 720)  (cost=0.00..431.04 rows=1000 width=20) (actual time=0.094..381.777 rows=1000 loops=1)
  ->  Result  (cost=0.00..431.00 rows=2 width=20) (actual time=0.192..0.203 rows=6 loops=1)
        ->  WindowAgg  (cost=0.00..431.00 rows=2 width=20) (actual time=0.188..0.197 rows=6 loops=1)
              Partition By: invalid_id
              Order By: version_id
              ->  Sort  (cost=0.00..431.00 rows=2 width=16) (actual time=0.178..0.179 rows=6 loops=1)
"                    Sort Key: invalid_id, version_id"
                    Sort Method:  quicksort  Memory: 23760kB
                    ->  Seq Scan on tst  (cost=0.00..431.00 rows=2 width=16) (actual time=0.146..0.154 rows=6 loops=1)
Planning time: 13.253 ms
  (slice0)    Executor memory: 679K bytes.
"  (slice1)    Executor memory: 420K bytes avg x 720 workers, 428K bytes max (seg0).  Work_mem: 33K bytes max."
Memory used:  229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 400.504 ms

В итоге, исходный запрос лечению не подлежит, т.к. проблема должна решаться выше, на уровне бизнес-анализа,
ибо расчет ранга по данному техническому полю смысла не имеет - баг!
Но мораль истории в том, что в GP все в ваших руках.
As a result, the original request cannot be resolved, because the problem must be solved earlier, at the business analysis level,
because calculating the rank for this technical field does not make sense - a bug!
But the moral of the story is that in GP you're the master of your business.

Хорошего вечера, каста!

Секрет 15 ( 2 в 1 или Точность - вежливость королей )
ч.1
На днях прекрасная коллега, а также подписчик канала, попросила чекнуть запрос на удаление дублей в 2 млрд табл-е.
Я предупредил что, DELETE 99% записей не лучшая идея, но по регламенту TRUNCATE + INSERT уникальных был запрещен.
Однако, суть в том, что в уникальном ключе ее запроса, помимо ctid + gp_segment_id ( о чем гласит Секрет 11) бало заявлено 3 поле - tableoid.
В ее случае таблица была нефрагментировна, поэтому ctid + gp_segment_id достаточно, но в общем случае в рамках
таблицы tableoid у записей разных партиций действительно будет разным, ибо это физические таблицы, поэтому концептуальный запрос в GP на удаление
дублей на все случаи будет таким:
Secret 15 (2 in 1 or Punctuality is a kingly virtue)
Part 1
The other day, a wonderful colleague, and also a subscriber of the channel, asked to check a request to delete duplicates in a table of 2 billion rows/
I warned that DELETE 99% of records is not the best idea, but according to the regulations, TRUNCATE + INSERT of unique records was prohibited.
However, the point is that in the unique key of her request, in addition to ctid + gp_segment_id (as stated in Secret 11), a 3rd field was declared - tableoid.
In her case, the table was not fragmented, so ctid + gp_segment_id is enough, but in general, within the
table, the tableoid of records of different partitions will indeed be different, because these are physical tables, so the conceptual request in GP to delete duplicates for all cases will be like this:

delete
from public.calls
where ctid || '#' || gp_segment_id || '#' || tableoid not in
      (select min(ctid || '#' || gp_segment_id || '#' || tableoid) from public.calls group by phone_num).

Благодарен @lyubov_medvedeva за наводку! Thanks to @lyubov_medvedeva for the tip!

ч.2
Мы на проекте часто расплачиваемся за ошибки проектирования. Дубли это еще не все.
Оказывается, что если в колоночную таблицу добавить поле, не указав в ALTER TABLE опцию хранения,
то оно не наследует свойства таблицы, в данном случае будет создано без сжатия!!

Проверим
Part 2
We often pay for design errors on the project. Duplicates are not everything.
It turns out that if you add a field to a column table without specifying the storage option in ALTER TABLE,
then it does not inherit the table properties, in this case it will be created without compression!!

Let's check

create table public.tst_aoco WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select 1 n;

alter table public.tst_aoco add k int;

SQL>
select a.*
from pg_catalog.pg_attribute_encoding a,
     pg_class c
where c.relname = 'tst_aoco'
  and a.attrelid = c.oid
order by 2;
attrelid | attnum | attoptions
-----------------------------------------------------------------------
283176480| 1    | {compresstype=zstd,compresslevel=1,blocksize=32768}
283176480| 2    | {compresstype=none,blocksize=32768,compresslevel=0}

Спасибо @tribuntu, подписчику канала, подсказавшему где GP хранит опции колонок.

p.s.
В разработке кода в GP любая неточность чревата, и так же как в шахматах, здесь полно ловушек, как то:
Thanks to @tribuntu, a channel subscriber, who suggested where GP stores column options.

p.s.
In GP code development, any inaccuracy is fraught, and just like in chess, there are plenty of traps, such as below:

Вышла полезная статья, к которой я оставил интересующий меня комментарий, который автор отклонил к публикации. Он был о том как в алгоритмическом способе ускорения count(distinct) (№2) можно сгладить перекос данных, добавив в ключ GROUP BY - день продажи.
Кмк при этом объем входных (для группировки) данных пропорционально увеличится на всех сегментах, а значит перекос не уйдет
А вам понятно ?

Секрет 16(И все таки она меняется от перестановки слагаемых )
Заинтересовался кейсом от поддержки, почему вакуум на проме и DR для одной и той же сжатой табл-ы дал сильно разный результат.
Оказалось, что на DR табл-а distributed randomly, на проме - по ключу.

Стало интересно проверить гипотезу, а как зависит размер zstd табл-ы от ее ключа.
В случае ее подтверждения, это бы все объяснило.

Взял множество 1 млрд почти уникальных GUID ( 95% уников ) - занес в колонку AOCO zstd таблицы распределив по GUID и RANDOMLY - никакой разницы.
Проверил, а как ведут себя A) последовательность натурального ряда и Б) она же с повтором.
A)
Возьмем первые 100 млн натуральных чисел и запишем в табл-ы по ключу и randomly
Secret 16 (And yet permutation changes the sum)
I got stucked from support why vacuum on prod and DR for the same compressed table gave very different results.
It turned out that on DR the table is distributed randomly, on prod - by key.

I decided to test the hypothesis of how the size of the zstd table depends on its key.
If it is confirmed, it would explain everything.

I took a set of 1 billion almost unique GUIDs (95% unique) - put them into the AOCO zstd column of the table, distributing them by GUID and RANDOMLY - no difference.
I checked also how do A) a sequence of natural numbers and B) the same with a repeat behave.
A)
Let's take the first 100 million natural numbers and write them into tables by key and randomly

create table public.tst_100m_uniq WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select generate_series(1, 100e6::int) n distributed by(n);

create table public.tst_100m_uniq_rnd WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select *
from public.tst_100m_uniq distributed randomly;

Результат взвешивания уже показал статистическую разницу The weighing result already gives a statistical difference

sql>
select 1 tbl_num, pg_table_size('public.tst_100m_uniq')
union all
select 2, pg_table_size('public.tst_100m_uniq_rnd')
tbl_num | pg_table_size
--------+--------------
1       | 386 582 264
2       | 419 292 504

Ого, Выигрыш от того, что не поленились и задали ключ - 8%

Теперь проверим как покажет себя посл-ть с повторами ( 100 x 1 млн уников )
Б)
Wow, the gain from not being lazy and setting the key is 8%

Now let's check how the sequence with repetitions will perform (100 x 1 million uniques)
B)

create table public.tst_1m WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select generate_series(1, 1e6::int) n distributed by(n);

create table public.tst_100m WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select n
from public.tst_1m,
     (select generate_series(1, 100)) a distributed by(n);

create table public.tst_100m_rnd WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
select *
from public.tst_100m distributed randomly;

Невероятно, но разница выросла в ~7 раз Incredibly, the difference has increased by ~7 times

sql>
select 1, pg_table_size('public.tst_100m')
union all
select 2, pg_table_size('public.tst_100m_rnd')
tbl_num | pg_table_size
--------+--------------
1       | 51 163 888
2       | 342 405 928

Цифры говорят сами за себя. Мне хватило тестов на одной колонке (т.к. AOCO zstd у нас стандарт), чтобы подтвердить гипотезу,что ключ распределения сжатой таблицы влияет на ее размер. Изначальный вопрос решен, причина ясна.
p.s.
В хоккее есть поговорка "Порядок бьет класс". Не ленимся коллеги, не забываем задавать ключ при создании таблицы, чтобы
не краснеть перед руководством в ответе на вопрос: "Куда так быстро утекает место?"
The numbers speak for themselves. This test on one column is enough(since AOCO zstd is our standard) to confirm the hypothesis that the distribution key of a compressed table
affects its size. The initial issue is resolved, the reason is clear.
p.s.
There is a proverb in hockey: "Order beats class". Let's not be lazy and not forget to specify the

Секрет 17 (Однопроходные весы)

Однажды многим предстоит сравнить 2 табл-ы.
Скорее всего, 95% ( включая меня ) используют запрос вида:
Secret 17 (Single-Pass Comparator)

Many people will have to compare 2 tables at some point.
Most likely, 95% (including me) use a query like

select * 
from ((table t1 except table t2) 
  union all (table t2 except table t1)) foo;

Обнаружил аналог, предложенный Feng Tian из Vitessedata
который читает табл-ы 1 раз, избегая join-а:
I discovered another one suggested by Feng Tian from Vitessedata
which reads tables once, avoiding join:

select h, sum(cnt) from (
   select textin(record_out(t1)) as h, 1 as cnt from t1
   union all
   select textin(record_out(t2)) as h, -1 as cnt from t2) foo
group by h 
having sum(cnt) <> 0;

Как думаете какой способ лучше ?
Which method do you think is better?

Секрет 17 (продолжение - бенчмарк)
Т.к. мнения разделились, провел 3 теста, 2 на синтетике и 1 на пром данных.
Для краткости приведу только результаты забега explain analyze , где T1 - это вариант с join(1й в опросе - old), T2 - однопроходный(new).
В каждом тесте создано 2 идентичных табл-ы zstd AOCO(Append only column oriented), где для каждой выполнен 1 прогон запроса old и new
Тест 1
Синтетика 1 млрд строк с 99% дублей:
Secret 17 (continued - benchmark)
Since opinions were divided, I conducted 3 tests, 2 on synthetics and 1 on industrial data.
For brevity, I will only provide the results of the explain analyze run, where T1 is the option with join (1st in the survey - old), T2 is a single-pass (new).
In each test, 2 identical zstd AOCO tables (Append only column oriented) were created, where for each one, 1 run of the old and new queries was performed
Test 1
Synthetics 1 billion rows with 99% duplicates:

create table public.t1_syn   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as
select n, '1900-01-01'::date + mod(n,100) dt, lpad('0', mod(n,10), '0' ) txt
from (select generate_series(1,1e6::int) as n) a distributed by(n);

insert into public.t1_syn
select t.* from public.t1_syn t, (select generate_series(1,1e3::int)) a

create table public.t2_syn   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select * from public.t1_syn  distributed by(n);

T1: (old)
Execution time: 51321.937 ms
T2: (new)
Execution time: 67164.703 ms

Тест 2 - те же таблицы, но с разными хэшами ( 2я distributed by(n, dt), 1я - by n )
Test 2 - the same tables, but with different hashes (2nd distributed by(n, dt), 1st - by n)

T1:
Execution time: 86031.780 ms
T2:
Execution time: 42848.731 ms

Тест 3 - пром таблица в 1 млн строк из 100 колонок
Test 3 - production table of 1 million rows of 100 columns
T1:
Execution time: 3087.161 ms
T2:
Execution time: 2733.244 ms

Отмечу, что для old везде GP выбрал Optimizer: Pivotal Optimizer (GPORCA), для new - Optimizer: Postgres query optimizer
И если для Тест 3 включить legacy оптимизатор ( set optimizer = off ), то разрыв увеличивается почти в 3 раза, (Execution time: 7897 ms для old)

Вывод: лично я голосовал за метод T2 (от дядюшки Ляо) и остаюсь при своем мнении.
К тому же, в отличие от 1-го он ловит дубли, выводя их в разницу.

По хорошему, надо бы построить ф-ю зависимости разницы в методах от числа колонок в таблице, т.к. похоже 1й метод более чувствителен к возрастанию их числа.
Note that for old, GP chose Optimizer: Pivotal Optimizer (GPORCA) everywhere, for new - Optimizer: Postgres query optimizer
And if you enable the legacy optimizer for Test 3 (set optimizer = off), the gap increases almost 3 times, (Execution time: 7897 ms for old)

Conclusion: personally, I voted for the T2 method (from Uncle Liao) and I remain with my opinion.
In addition, unlike the 1st, it catches duplicates, displaying them in the difference.

In a good way, it would be necessary to build a function of the dependence of the difference in methods on the number of columns in the table, since it seems that the 1st method is more sensitive to an increase in their number.

С Днем Рождения, секретный ключ ЦБ - теперь тобой можно оплатить пиво в США -)

⚠️🇷🇺#инфляция #дкп #россия
ЦБ НЕ НАБЛЮДАЕТ ПРИЗНАКОВ ЗАМЕДЛЕНИЯ ИНФЛЯЦИИ

ДЛЯ УДЕРЖАНИЯ РОСТА ЦЕН ПОТРЕБУЕТСЯ БОЛЕЕ ЖЕСТКАЯ ДКП В 2025 Г

- НАБИУЛЛИНА - ТАСС

Секрет 18( IN или NOT IN )
Secret 18( To BE or NOT to BE! )
В продолжение сегодняшнего опроса, немного суровой правды.
Кто использует NOT IN - это вы зря,срочно меняйте код на NOT EXISTS.
Оказывается, IN и NOT IN ведут себя совершенно по разному, и если IN и EXISTS - дело вкуса, то NOT IN вызывает Broadcast Motion,
т.е. тираж всей фильтр таблицы на все узлы кластера и цена запроса (время) в обоих вариантах составляет почти 3 порядка.

Простой пример:
Создадим 2 табл-ы по 100 млн строк
Continuing with today's survey, a bit of bitter truth.
Those who use NOT IN - you are in danger.
It turns out that IN and NOT IN behave completely differently, and if IN and EXISTS are a matter of taste, then NOT IN causes Broadcast Motion,
i.e. the circulation of the entire filter table to all cluster nodes and the cost of the request (time) in both options is almost 3 orders of magnitude.
A simple example:
Let's create 2 tables of 100 million rows

create table public.t1   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select generate_series(1,1e8::int) n distributed by(n);

create table public.t2   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as select * from public.t1  distributed by(n);

План запроса для NOT IN: Query plan for NOT IN:

explain analyze
select t1.* from public.t1 where t1.n not in(select n from public.t2)

Gather Motion 720:1  (slice2; segments: 720)  (cost=0.00..23275.28 rows=100000000 width=4) (actual time=1078136.740..1078136.740 rows=0 loops=1)
  ->  Hash Left Anti Semi (Not-In) Join  (cost=0.00..22392.73 rows=138889 width=4) (actual time=0.000..1078112.753 rows=0 loops=1)
        Hash Cond: (t1.n = t2.n)
        Extra Text: (seg0)   Initial batch 0:
(seg0)     Wrote 1153562K bytes to inner workfile.
(seg0)     Wrote 1604K bytes to outer workfile.
(seg0)   Initial batches 1..63:
"(seg0)     Read 1153562K bytes from inner workfile: 18311K avg x 63 nonempty batches, 18343K max."
"(seg0)     Read 1604K bytes from outer workfile: 26K avg x 63 nonempty batches, 27K max."
"(seg0)   Hash chain length 3.1 avg, 17 max, using 31848259 of 33554432 buckets."
        ->  Seq Scan on t1  (cost=0.00..432.38 rows=138889 width=4) (actual time=0.616..85.887 rows=139894 loops=1)
        ->  Hash  (cost=1000.99..1000.99 rows=100000000 width=4) (actual time=494428.395..494428.395 rows=100000000 loops=1)
              ->  Broadcast Motion 720:720  (slice1; segments: 720)  (cost=0.00..1000.99 rows=100000000 width=4) (actual time=4.791..247886.342 rows=100000000 loops=1)
                    ->  Seq Scan on t2  (cost=0.00..432.38 rows=138889 width=4) (actual time=0.398..242.821 rows=139894 loops=1)
Planning time: 38.989 ms
  (slice0)    Executor memory: 891K bytes.
"  (slice1)    Executor memory: 220K bytes avg x 720 workers, 220K bytes max (seg0)."
"* (slice2)    Executor memory: 82285K bytes avg x 720 workers, 82285K bytes max (seg0).  Work_mem: 36686K bytes max, 2343750K bytes wanted."
Memory used:  229376kB
Memory wanted:  2344250kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1 078 696.860 ms

Не буду грузить вас тут планом и для exists в вечер Пт просто рез-т:
I won't burden you with a plan for EXISTS on Friday evening, just the result:

explain analyze
select t1.* from public.t1 where t1.n not in(select n from public.t2)

Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1752.198 ms

Также посмотрим на вариант с LEFT JOIN,который не вошел в опрос, но любит @andre_rumyanec
Let's also look at the option with LEFT JOIN (suggested by @andre_rumyanec)

explain analyze
select t1.* from public.t1 t1 left join public.t2 t2 on t1.n = t2.n
where t2.n is null

Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1502.517 ms

И таким образом, LEFT JOIN выбился в лидеры.
Единственное, если вы соблазнитесь им и для IN -фильтра, не забудьте обернуть рез-т DISTINCT-ом, т.к. если фильтр табл-а содержит дубли предикатного ключа, то возможно размножение строк.

Ни фото ниже для полного раскрытия темы, сравним планы золотого и серебряного призера, отставшего на 0.25 с.

NOT EXISTS vs LEFT JOIN

Секрет 19( Хочешь сделать хорошо, сделай сам )

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

В общем случае этот подход неоптимален. Иаче говоря, сет из CTE, где используется GROUP BY, не обязан быть распределен по полям,
по которым идет его join с самой таблицей.

Пример - синтезируем 3 млн строк, по 3 версии на каждый бизнес-ключ n:
Secret 19 (If you want something done right, do it yourself)

Often there is a task to get the latest versions of records in the SCD2 table and we'd like to do it with one query, using CTE to select the latest versions of each business key, filtering the table with the resulting set in order to get the current records at the output.
In general, this approach is not optimal. In other words, a set from a CTE, where GROUP BY is used, does not have to be distributed by the fields by which it is joined with the table itself.

Example - let's get 3 million rows,3 versions for each business key n:

create table public.tst_1m   WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1)
as
    with a as (
    select generate_series(1,1000000) n),
         b as (select generate_series(1,3) version_id)
select a.n, b.version_id, random() val   from a,b
    distributed by(n,version_id );

Отберем актуальные (на последнюю версию) записи
Let's select the latest records

 
explain analyze
select * from public.tst_1m join (
select n,max(version_id) version_id from public.tst_1m group by 1) c
using (n,version_id )

Несмотря на то, что join таблицы с ее аналитической выборкой идет по полям, выбранным в CTE, и являющимися хэшом табл-ы, план неоптимален - требуется Redistribute Motion:
Even though the join of the table with its analytic rollup is performed on the fields selected in the CTE and being the table hash, the plan is not optimal - a Redistribute Motion is required:

Gather Motion 864:1  (slice3; segments: 864)  (cost=0.00..898.40 rows=955579 width=16) (actual time=104.067..263.010 rows=1000000 loops=1)
  ->  Hash Join  (cost=0.00..864.68 rows=1106 width=16) (actual time=91.642..96.042 rows=1255 loops=1)
        Hash Cond: ((tst_1m.n = tst_1m_1.n) AND (tst_1m.version_id = (max(tst_1m_1.version_id))))
"        Extra Text: (seg300) Hash chain length 1.0 avg, 2 max, using 1247 of 131072 buckets.Hash chain length 4.7 avg, 11 max, using 253 of 256 buckets; total 3 expansions."
""
        ->  Redistribute Motion 864:864  (slice1; segments: 864)  (cost=0.00..431.33 rows=3473 width=16) (actual time=0.021..2.053 rows=3765 loops=1)
              Hash Key: tst_1m.n
              ->  Seq Scan on tst_1m  (cost=0.00..431.06 rows=3473 width=16) (actual time=0.470..1.123 rows=3678 loops=1)
        ->  Hash  (cost=431.62..431.62 rows=1106 width=8) (actual time=90.543..90.543 rows=1255 loops=1)
              ->  HashAggregate  (cost=0.00..431.62 rows=1106 width=8) (actual time=89.799..90.033 rows=1255 loops=1)
                    Group Key: tst_1m_1.n
"                    Extra Text: (seg0)   Hash chain length 4.7 avg, 11 max, using 253 of 256 buckets; total 3 expansions."
""
"                    Extra Text: (seg300) Hash chain length 4.9 avg, 12 max, using 255 of 256 buckets; total 3 expansions."
""
                    ->  Redistribute Motion 864:864  (slice2; segments: 864)  (cost=0.00..431.20 rows=3473 width=8) (actual time=15.055..87.630 rows=3765 loops=1)
                          Hash Key: tst_1m_1.n
                          ->  Seq Scan on tst_1m tst_1m_1  (cost=0.00..431.06 rows=3473 width=8) (actual time=0.326..1.114 rows=3678 loops=1)
Planning time: 39.895 ms
...
Work_mem: 40K bytes max."
Memory used:  229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1040.474 ms

Перераспределим таблицу по хэшу от n
Let's redistribute the table by n

ALTER TABLE public.tst_1m SET DISTRIBUTED BY(n)

Выполним тот же запрос. Видим, что из плана исчез Redistribute и ожидаемо запрос ускорился :
Let's execute the same query. We see that Redistribute has disappeared from the plan and, as expected, the query has accelerated:

 
Gather Motion 864:1  (slice1; segments: 864)  (cost=0.00..899.41 rows=991220 width=16) (actual time=0.103..438.499 rows=1000000 loops=1)
  ->  Hash Join  (cost=0.00..864.44 rows=1148 width=16) (actual time=4.625..7.169 rows=1255 loops=1)
        Hash Cond: ((tst_1m_2.n = tst_1m_2_1.n) AND (tst_1m_2.version_id = (max(tst_1m_2_1.version_id))))
"        Extra Text: (seg300) Hash chain length 1.0 avg, 2 max, using 1247 of 131072 buckets.Hash chain length 4.7 avg, 11 max, using 253 of 256 buckets; total 3 expansions."
""
        ->  Seq Scan on tst_1m_2  (cost=0.00..431.06 rows=3473 width=16) (actual time=0.490..1.202 rows=3765 loops=1)
        ->  Hash  (cost=431.53..431.53 rows=1148 width=8) (actual time=3.191..3.191 rows=1255 loops=1)
              ->  HashAggregate  (cost=0.00..431.53 rows=1148 width=8) (actual time=2.486..2.721 rows=1255 loops=1)
                    Group Key: tst_1m_2_1.n
"                    Extra Text: (seg0)   Hash chain length 4.7 avg, 11 max, using 253 of 256 buckets; total 3 expansions."
""
"                    Extra Text: (seg300) Hash chain length 4.9 avg, 12 max, using 255 of 256 buckets; total 3 expansions."
""
                    ->  Seq Scan on tst_1m_2 tst_1m_2_1  (cost=0.00..431.06 rows=3473 width=8) (actual time=0.277..1.122 rows=3765 loops=1)
Planning time: 38.725 ms
  (slice0)    Executor memory: 964K bytes.
"  (slice1)    Executor memory: 1834K bytes avg x 864 workers, 1921K bytes max (seg300).  Work_mem: 40K bytes max."
Memory used:  229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 506.149 ms

Напрашивается простой вывод
Если хэш табл-ы поменять нельзя и квота в схеме позволяет, то материализация CTE подзапроса по ключу = хэшу табл-ы, сопряженной с CTE
в отдельную таблицу рулит:
A simple conclusion suggests itself.
If the hash of the table cannot be changed and the quota in the scheme allows it, then materialization of the CTE subquery by key = hash of the table, coupled with the CTE
into a separate table is the way to go:

create unlogged table public.tst_1m_slice as
select n,max(version_id) version_id from public.tst_1m group by 1 distributed by (n,version_id)

Чекнем план: Let's check the query plan

explain analyze
select * from public.tst_1m join public.tst_1m_slice c
using (n,version_id )
 
 
Gather Motion 864:1  (slice1; segments: 864)  (cost=0.00..899.23 rows=1000000 width=16) (actual time=0.089..206.448 rows=1000000 loops=1)
  ->  Hash Join  (cost=0.00..863.95 rows=1158 width=16) (actual time=2.786..5.658 rows=1268 loops=1)
        Hash Cond: ((tst_1m.n = tst_1m_slice.n) AND (tst_1m.version_id = tst_1m_slice.version_id))
"        Extra Text: (seg570) Hash chain length 1.0 avg, 2 max, using 1266 of 262144 buckets."
        ->  Seq Scan on tst_1m  (cost=0.00..431.06 rows=3473 width=16) (actual time=0.566..1.181 rows=3678 loops=1)
        ->  Hash  (cost=431.02..431.02 rows=1158 width=8) (actual time=0.619..0.619 rows=1268 loops=1)
              ->  Seq Scan on tst_1m_slice  (cost=0.00..431.02 rows=1158 width=8) (actual time=0.033..0.170 rows=1268 loops=1)
Planning time: 84.317 ms
 (slice0)    Executor memory: 756K bytes.
"  (slice1)    Executor memory: 2573K bytes avg x 864 workers, 2630K bytes max (seg485).  Work_mem: 40K bytes max."
Memory used:  229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 316.943 ms

Кстати, результат получился даже быстрее, чем в запросе без Redistribute

Работа над ошибками (Секрет 19)
Очень приятно, что среди вас есть те, кто критически подходят к контенту.
Благодарю @GaRin_1979, который совершенно верно отметил, что мой последний вывод не корректен, т.к. при сравнении не было учтено время на создание unlogged таблицы из CTE.
Проверил - он прав.Вытекает 2 интересных вопроса.
1) А Почему в плохом плане 2 Redistribute Motion когда казалось бы достаточно перераспределить рез-т CTE ?
2) Куда сохраняется результат каждого из них ? В какое табличное пр-во, схему ? Не в своп ли ?
Correcting errors (Secret 19)

Thanks to @GaRin_1979, who quite correctly noted that my last conclusion is incorrect, because the time to create an unlogged table from CTE was not taken into account when comparing.
I checked - he is right. Two interesting questions follow.
1) Why are there 2 Redistribute Motions in a bad plan when it would seem sufficient to redistribute the CTE result?
2) Where is the result of each of them saved? In what table space, scheme? Not in swap?

Секрет 19( Заключение - бенчмарк )
В заключение, решил проверить свою интуицию и ответить на вопрос - стоит ли игра свеч ?

Сравним исходный запрос с Redistribute Motion в плане и предложенную альтернативу, но
CTE будем сохранять НЕ в unlogging, т.к.
GP6 не умеет explain analyze create unlogging table ...,
а нас интересует метрика Execution Time из плана запроса, т.е. время его выполнения.

Возьмем табл-у побольше, из 300 млн строк,
или 30 версий на каждый бизнес-ключ n чтобы снизить погрешность при сравнении результатов :
Secret 19 (Benchmark)
In conclusion, I decided to test my intuition and answer the question - is the game worth the candle?

Let's compare the original query with Redistribute Motion in the plan and the proposed alternative, but
we will NOT save the CTE in unlogging, because GP6 does not know how to do "explain analyze create unlogging table ...",
and we are interested in the Execution Time metric from the query plan.

Let's take a larger table, from 300 million rows,
or 30 versions for each business key n to reduce the error when comparing results:

create table public.tst_300m WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
with a as (
    select generate_series(1, 10e6::int) n),
     b as (select generate_series(1, 30) version_id)
select a.n, b.version_id, random() val
from a,
     b distributed by(n,version_id );

В рамках нового подхода будем в каждом прогоне ( чтобы исключить возможность кэширования, которой нет, но все же) создавать новую промежуточную табл-у из CTE с нужным нам хэшом
As part of the new approach, we will create a new intermediate table from the CTE with the hash we need in each run (to eliminate the possibility of caching, which does not exist, but still)

explain analyze
create table public.tst_300m_slice_aoco WITH (appendonly=true,orientation=column) as
select n,max(version_id) version_id from public.tst_300m group by 1 distributed by (n,version_id)

используя ее для отбора актуальных версий уже без Redistribute
using it to select current versions without Redistribute

explain analyze
select * from public.tst_300m join public.tst_300m_slice_aoco c
using (n,version_id )

Затем выполним исходный запрос(old), требующий Redistribute, т.к. хэш табл-ы = (n,version_id ) не согласован с
ключом GROUP BY в CTE:
Then we execute the original query (old), which requires Redistribute, because the table hash = (n,version_id ) is not consistent with the GROUP BY key in the CTE:

explain analyze
select * from public.tst_300m join (
select n,max(version_id) version_id from public.tst_300m group by 1) c
using (n,version_id )

Ниже таблица с результатом, где
2я колонка - время выполнения канонического запроса (old), мс
сумма 3 и 4 колонок - время выполнения альтернативы, включая материализацию CTE (3я) и сам запрос результата(4я), мс
Below is a table with the result in ms, where
column 2 is the execution time of the canonical query (old),
the sum of columns 3 and 4 is the execution time of the alternative, including the materialization of the CTE (3rd) and the query for the result itself (4th)

As a result, in my opinion, we can say that the game is worth the candle, since we observe a statistically significant gain in
the last column, which is the ratio of the results (new vs old).

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