На заметку
#table #create #time
Сегодня потребовалось узнать дату создания табл-ы. Сходу не нагуглил, так что сохраню здесь.
Замечу, что 1) фиксируется момент начала транзакции CREATE, а не конца; 2) после DROP это инфо уже недоступно
Today I needed to know the date of table creation. I couldn't find it right away on Google, so I'll save it here.
Note that the moment of the beginning of the CREATE transaction is recorded, not the end. Since DROP table the info is no longer available.

select statime, *
from pg_stat_last_operation o,
     pg_class c
where c.relname = 'foo'
  and o.objid = c.oid

Вопрос 3
В таблице foo (AOCO zstd1) 109.2 млрд строк, равномерно размазанных на 1188 одноранговых секций ( ключ секции - document_date( DATE ), отличен от ключа дистрибуции ).
select count(*) from foo висит вечно = не работает.
После analyze foo тот же запрос вернет рез-т за 1.5 мин.
Что изменилось после analyze, что запрос подсчета строк смог отработать.
Question 3
The foo table (AOCO zstd1) has 109.2 billion rows, evenly spread across 1188 partitions (the partition key is document_date( DATE ), differs from distribution key).
select count(*) from foo hangs forever = does not work.
After analyze foo the same query will return the result in 1.5 minutes.
What changed after analyze that the row count query was able to complete.

Ответ: Вот он - принцип MapReduce в чистом виде. На планах ниже видно, что без analyze агрегация, грубо говоря суммирование маркеров каждой строки,
выполняется на мастере,
тогда как после analyze на мастер поступают готовые агрегаты с каждого сегмента.
В первом случае, мастер видимо не может выполнить эту работу (для 100 ГБ интерконнекта прокачать 212 ГБ данных таблицы с сегментов - не проблема),
т.к. агрегация идет на одном ядре, куда стекаются потоки от всех 348 сегментов кластера.

Answer: Here it is - the MapReduce principle in its purest form. The plans below show that without analyze, aggregation, roughly speaking,
summing up the markers of each row, is performed exclusively on the master,
while after analyze, the master receives ready aggregates from each segment.
In the first case, the master apparently can't do this work (for 100 GB of interconnect, pumping 212 GB of table data from segments is not a problem),
since aggregation occurs on one core, where flows from all 348 cluster segments flow.

План A: Запрос без статы: A:No statistics on table

Aggregate  (cost=0.00..431.00 rows=1 width=8) (actual time=1072.009..1072.009 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..431.00 rows=1 width=1) (actual time=0.997..695.980 rows=7000000 loops=1)
        ->  Seq Scan on foo  (cost=0.00..431.00 rows=1 width=1) (actual time=0.980..4.703 rows=21999 loops=1)
Planning time: 11.125 ms
  (slice0)    Executor memory: 909K bytes.
"  (slice1)    Executor memory: 298K bytes avg x 348 workers, 298K bytes max (seg0)."
Memory used:  524288kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 1081.252 ms

План Б: Оптимальный запрос: B:There is statistics

Aggregate  (cost=0.00..442.05 rows=1 width=8) (actual time=266.551..266.551 rows=1 loops=1)
  ->  Gather Motion 348:1  (slice1; segments: 348)  (cost=0.00..442.05 rows=1 width=8) (actual time=5.237..266.416 rows=348 loops=1)
        ->  Aggregate  (cost=0.00..442.05 rows=1 width=8) (actual time=6.519..6.519 rows=1 loops=1)
              ->  Seq Scan on foo  (cost=0.00..442.01 rows=20115 width=1) (actual time=0.498..5.115 rows=21999 loops=1)
Planning time: 14.683 ms
  (slice0)    Executor memory: 429K bytes.
"  (slice1)    Executor memory: 298K bytes avg x 348 workers, 298K bytes max (seg0)."
Memory used:  524288kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 289.056 ms

Для иллюстрации планы даны для гораздо меньшей таблицы, но к сути вопроса это не относится.
For the sake of simplicity, the plans are given for a much smaller table, but it doesn't matter.

p.s.
Для Legacy оптимизатора такой проблемы не существует -)
После set optimizer = off запрос отрабатывает по плану Б.
For the Legacy optimizer there is no such problem -)
After set optimizer = off the query is processed according to plan B.

꧁꧂Не успели отшипеть пузырики в новогодних бокалах шампанского, под звон которых я думал о том, будет ли год 2025 так же щедр на секреты
нашей любимой зеленой сливы, как позади уже 1й квартал.
На удивление, Грин-плюмбум ( отсылка к фильму "Плюмбум, или опасная игра" ) не перестает удивлять своей архитекторой, интригуя
каждую новую неделю чем-то новым и неожиданным.
Все таки дополнительное измерение сложности в виде соединения считалок в Сеть - это интереснейшая тема,
которая наделяет проект Аренадаты потенциалом органического роста, заставля держать нас в уме главный девиз синергии 1+1 > 2

Надеюсь, даэже если такие именитые вендоры как Oracle, Teradata вернутся на наш рынок, они не дождутся смерти
Greenplum, который составит им на тот момент еще более серьезную конкуренцию
и те, кто едут на нем, на нем же и продолжать свой путь в Big Data.

Друзья, Всем лучи добра и хорошей комфортной езды без слива, тьфу без спилла -)☮️

Неожиданно, но корректный ответ - 1. Надеюсь, что если тут есть Архитекторы или Продакты Арены, они инициируют доработку, т.к.
Redistribute тут не нужен, т.к. служебное поле gp_segment_id - уже готовый ключ для локальной агрегации на каждом сегменте.
Я бы сказал, это нужная доработка, т.е. ускорит расчет перекоса в таблицах, что является важной рутинной операцией.

Surprisingly, but the correct answer is 1. I hope that if there are Arena Architects or Product owners here, they will initiate the revision, because
Redistribute is nonsense here, because the hidden field gp_segment_id is a ready-made key for local aggregation on each segment.
I would say that this is a necessary revision, i.e. it will speed up the calculation of the skew in the tables, which is an important routine operation.

Думаю, что вариант с select count(*) лучше.
Как мы знаем из плана запроса, о чем писал выше, в случае собранной по табл-е статистике, что как правило имеет место,
count(*) делает агрегацию пред агрегатов, посчитанных на сегментах,
а как работает LIMIT для меня до сих пор загадка.

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

I think that the option with select count(*) is better.
As we know from the query plan, which I wrote about above, in the case of statistics collected from the table, which usually takes place,
count(*) makes an aggregation of pre-aggregates calculated on segments,
and how LIMIT works is still a mystery to me.

The conclusion is also confirmed by the statistics I collected from running 5000 different AirFlow flows - since we had both options in production, there was an opportunity to compare.

The difference is noticeable, if we talk about the average run time of both versions of the code in the interval of 2 weeks.

Опровержение!
Оказывается, не все так просто.
Поставил эксперимент.
Взял топ 98 самых больших таблиц на проме: 38 партицированных, 60 - нет. Общий вес 78 TB.
Проверил наличие данных для каждой в цикле,
результат - безоговорочная победа метода LIMIT 1, который показал суммарное время = 2.8 ceк, тогда как COUNT(*) дал 1455 сек.

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

А теперь самое интересное.
Я решил проверить, а можно ли ускорить получение первой произвольной строки через LIMIT 1, добавив отсечку по gp_segment_id.

Запилил такую ф-ю:

 CREATE OR REPLACE FUNCTION is_tbl_empty(p_tbl_nm text, p_segments_cnt int, p_start_segment_id int)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
declare
    v_ int;
    begin
    for c in 0 .. p_segments_cnt - 1
        loop
    execute 'select 1 from ' || p_tbl_nm || ' where gp_segment_id = $1 limit 1' into v_ using mod( coalesce(p_start_segment_id,0)+c, p_segments_cnt) ;
    if v_ = 1 then
        return false;
    end if;
        end loop;
return true;
end;
$function$

которая возвращает true для пустой табл-ы ( иначе false ), читая первый сегмент, заданный параметром p_start_segment_id
Если табл-а пустая, придется прочитать все p_segments_cnt сегментов.
Но как правило, в реальности имеем дело с новыми даныыми каждый день, поэтому вероятность, что в каждом сегменте будут данные почти 1.

Читать по порядку от 0 до p_segments_cnt - 1 не стоит, т.к. повышаем вероятность нагрузки на сегменты с наименьшими номерами, поэтому функции в
p_start_segment_id лучше передавать random(0, p_segments_cnt).

Неожиданно, данный метод показал лучший результат лишь для 91 из 98 таблиц списка - суммарно затратив 0.9 сек против 4.8 с на все табл-ы .
Для остальных 7 таблиц ( из которых 1 оказалась партицированной) ф-я дала рез-т сильно хуже, в итоге суммарно ф-я затратила 18.7 с против 5 сек у LIMIT 1.

Я проверил для отдельной табли-ы без партиций из этих 7, нет ли в ней перекоса - особо не выявлено.
Вместо p_start_segment_id = 0 я задал для функции номер сегмента с наименьшим числом запиесей ( 87 ) рез-т почти не изменился.
Рез-т воспроизводится. Эти 7 таблиц раскиданы случайно в общем наборе, упорядоченном по убыванию размера таблиц.
Обяснения у меня нет. Но, надеюсь, это не деградация дисков.

Секрет 34 ( Порядок бьет класс, v/2.0 или кэшбэк от 11 до 45% на ровном месте )

Как мы знаем из https://t.iss.one/mpp_secrets/52 размер таблицы зависит от выбранного ключа дистрибуции.
Оказывается, размер таблицы зависит и от порядка строк в табл-е
и это можно использовать в нашу пользу, если отсортировать данные по ключу дистрибуции.

Проведем тест.
Для краткости отмечу, что все таблицы ниже создаются колоночными со сжатием zstd1, т.е.
WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1),

🔸Тест 1: 101 млн записей, низкая энтропия данных
Синтетика:

create table t1 as
select a.n, current_date + a.n dt
from (select generate_series(1, 1000000) n) a distributed by(n);

insert into t1
select a.n + trunc(random() * 1000), a.dt
from t1 a
         join (select generate_series(1, 100) n) b on 1 = 1;

Сортировка:

create table t1_ordered as
select *
from t1
order by 1 distributed by(n);

select count(*) from t1 -- 101 000 000

Рез-т:
select pg_relation_size('t1') -- 468 795 552
select pg_relation_size('t1_ordered') -- 258 197 512
Экономия места: 45%

🔸Тест 2: 101 млн записей, высокая энтропия данных
Синтетика:

create table t2 as
select a.n, current_date + a.n dt, random() rnd
from (select generate_series(1, 1000000) n) a distributed by(n);

insert into t2 
select a.n + trunc(random() * 100000000), a.dt, random() rnd
from t3 a
         join (select generate_series(1, 100) n) b on 1 = 1;

Сортировка:

create table t2_ordered as
select *
from t2
order by 1 distributed by(n);

Чек идентичности данных:
select count(*) from t2 -- 101 000 000

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

=> 0 строк ( сеты совпадают )

Рез-т:
select pg_relation_size('t2') -- 1 206 135 536
select pg_relation_size('t2_ordered') -- 1 075 361 608
Экономия места: 11%

Напомню, энтропия R - мера хаоса системы, а в данном контексте - % уникальных значений в столбце, т.е.
t2.rnd имеет экстремальную кардинальность ввиду его определения ( 101 млн случайных чисел ),
у dt - R в 100 раз ниже,
R ключа дистрибуции почти такая же как у rnd( ~100 млн случайных чисел) в тесте №2 и в 100 раз меньше - в 1-м тесте ( не более 1.001 млн случайных чисел )

🔸Кого заинтересовал рецепт, делитесь своими результатами - спасем планету от лишнего мусора!
Рассмотрены крайние случаи.

📌Корректный ответ - N, т.е. вся P не блокируется
Не скрою, сам не поверил, но факты - вещь упрямая

✅Обоснование:
Сразу скажу, что тест проводился на табл-е P (AOCO zstd1) с двухуровневым партицированием,
где по src_cd(text) было 8 парт--й по document_date(date) - 194. Всего 1552 партиции.
В S было 6 млн строк, по 1 млн для каждого src_cd, где document_date - случайная дата в интервале 2000 - 2025 г
2 сессии запущены почти одновременнно вручную в порядке 1,2

Рез-т: секции не блокируются для записи в параллельной сессии, что видно по вложенности интервалов выполнения операции INSERT - интервал 2й сессии вложен в интервал первой сессии

📌The correct answer is N, i.e. the whole P is not blocked
It's incredible, but facts are stubborn things
✅Justification:
I will say right away that the test was conducted on the P table(AOCO zstd1) with two-level partitioning,
where there were 8 partitions with src_cd(text) key and 194 with document_date(date). Total is 1552 partitions
In S there were 6 million rows, 1 million for each src_cd, where document_date is a random date in the range of 2000 - 2025
2 sessions were started almost simultaneously manually in the order 1,2

Result: sections are not blocked for writing in a parallel session, which is evident from the nesting of the intervals for executing the INSERT operation - the interval of the 2nd session is nested in the interval of the first session

🔸Тест с разным src_cd:
сессия 1:
Test with different src_cd:
session 1:

DO $$
BEGIN
raise notice 'bef ins:%', clock_timestamp();
insert into P
select * from S
    where src_cd = 'S0';
raise notice 'aft ins:%', clock_timestamp();
perform pg_sleep(15);
END$$;
bef ins:2025-04-15 20:19:36.138579+00
aft ins:2025-04-15 20:19:41.516063+00
completed in 20 s 438 ms

сессия 2:
session 2:

DO $$
BEGIN
raise notice 'bef ins:%', clock_timestamp();
insert into P
select * from S
    where src_cd = 'S1'
limit 10000;
raise notice 'aft ins:%', clock_timestamp();
END$$;
bef ins:2025-04-15 20:19:37.226968+00
aft ins:2025-04-15 20:19:41.202767+00
completed in 4 s 17 ms

🔸Тест с одинаковым src_cd:
сессия 1:
Test with same src_cd
session 1:

DO $$
BEGIN
raise notice 'bef ins:%', clock_timestamp();
insert into P
select * from S
    where src_cd = 'S0';
raise notice 'aft ins:%', clock_timestamp();
perform pg_sleep(15);
END$$;
bef ins:2025-04-15 20:27:57.471849+00
aft ins:2025-04-15 20:28:02.630457+00
completed in 20 s 218 ms

сессия 2:
session 1:

DO $$
BEGIN
raise notice 'bef ins:%', clock_timestamp();
insert into P
select * from S
    where src_cd = 'S0'
limit 10000;
raise notice 'aft ins:%', clock_timestamp();
END$$;
bef ins:2025-04-15 20:27:59.406034+00
aft ins:2025-04-15 20:28:02.326422+00
completed in 2 s 970 ms

📌Вывод: В обоих случаях insert в 1й сессии не блокирует insert в 2й сессии.
Задержка 15 сек в 1й сессии дает необходимую паузу для начала теста в рамках 2й сессии, а
limit 10000 в сессии 2 сокращает время выполнения insert дабы исключить потенциальную возможность реализации сценария,
когда временные интервалы insert обеих сессий пересекаются так, что 1-я закончилась пока работает 2-я.

👆Интересно отметить:
Если в качестве приемника P использовать таблицу без партиций, она блокируется на запись сессией 1, и сессия 2 ждет завершения операции insert в сессии 1, но не окончания транзакции.
📌Conclusion: In both cases, insert in session 1 does not block insert in session 2
The 15 sec delay in the 1st session gives the necessary pause to start the test within the 2nd session, and the 10000 limit in session 2 reduces the insert execution time in order to eliminate the potential possibility of a scenario where the insert time intervals of both sessions overlap so that the 1st session ends while the 2nd is running.

👆It is interesting to note:
If you use a table without partitions as a receiver, it is locked for writing by session 1, and session 2 waits for the insert operation in session 1 to complete, but not for the transaction to end.

Вопрос из зала: **Нужна помощь. Начали упираться в лимиты по открытым на запись AO таблицам.
Не знаешь, как получить список открытых на запись в данный момент таблиц?

Надо отмониторить состояние кластера и понять какие процессы открывают такое количество таблиц.**
Я решил это подсветить здесь для повышения вероятности получить более содержательный ответ.

Мой комментарий : 1) Логи БД должны содержать сам запрос,вызвавший известную ошибку 2) Данную ошибку можно словить и на одной партицированной таблице в одной сессии при отсутствии других партицированных объектов в БД, если постоянно писать в нее данные, например через цепочку операций drop,create,insert. Такой странный сценарий я использовал именно тогда, чтобы найти границы применимости партицированных таблиц в GP. Иными словами, похоже,что drop не обнуляет счётчик заблокированных AO таблиц, который сравнивается с параметром БД max_append_only

Translation of last published secret
Secret 34 (Order beats class, v/2.0 or cashback from 11 to 45% out of the blue)

As we know from https://t.iss.one/mpp_secrets/52, the table size depends on the selected distribution key.
It turns out that the table size also depends on the order of the rows in the table
and this can be used to our advantage if we sort the data by distribution key.

Let's run a test.
For brevity, I note that all tables below are created as columnar with zstd1 compression, i.e.
WITH (appendonly=true,orientation=column,compresstype=zstd,compresslevel=1),
🔸Test 1: 101 million records, low data entropy
Synthetics:

create table t1 as
select a.n, current_date + a.n dt
from (select generate_series(1, 1000000) n) a distributed by(n);

insert into t1
select a.n + trunc(random() * 1000), a.dt
from t1 a
         join (select generate_series(1, 100) n) b on 1 = 1;

Sorting:

create table t1_ordered as
select *
from t1
order by 1 distributed by(n);

select count(*) from t1 -- 101 000 000

Result:
select pg_relation_size('t1') -- 468,795,552
select pg_relation_size('t1_ordered') -- 258 197 512
Space saving: 45%

Test 2: 101 million records, high data entropy
Synthetics:

create table t2 as
select a.n, current_date + a.n dt, random() rnd
from (select generate_series(1, 1000000) n) a distributed by(n);

insert into t2 
select a.n + trunc(random() * 100000000), a.dt, random() rnd
from t3 a
         join (select generate_series(1, 100) n) b on 1 = 1;

Sorting:

create table t2_ordered as
select *
from t2
order by 1 distributed by(n);

Data identity check:
select count(*) from t2 -- 101,000,000

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

=> 0 rows ( sets match )

Result:
select pg_relation_size('t2') -- 1 206 135 536
select pg_relation_size('t2_ordered') -- 1 075 361 608
Space savings: 11%

Recall that entropy R is a measure of chaos in a system, and in this context, the % of unique values ​​in a column, i.e.
t2.rnd has extreme cardinality due to its definition (101 million random numbers),
dt - R is 100 times lower,
R of the distribution key is almost the same as rnd (~100 million random numbers) in test #2 and 100 times less - in the 1st test (no more than 1.001 million random numbers)

🔸If you are interested in the recipe, share your results - let's save the planet from unnecessary garbage!
Extreme cases are considered.

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

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

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

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

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

В точку!

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