Пришло время спросить вас, а вы используете секционировани в вашем Greenplum ? It's time to ask you, do you use partitions in your Greenplum?
Anonymous Poll
70%
Y
16%
N
12%
Нет, но планирую. No, but I plan to.
1%
Да, но пожалел об этом. Yes, but I regretted it
Секрет 12(Секунда часик бережет)
Через меня проходит много кода, поэтому поделюсь часто используемыми командами, которые настолько облегчают жизнь
что для каждой я создал snippet в DataGrip(макрос), чтобы не набивать ее каждый раз.
Secret 12 (A second saves an hour)
I handle a lot of code, so I'll share frequently used commands that make life so much easier
that I created a snippet in DataGrip (macro) for each one so I don't have to type it in every time.
1) ключ распределения табл-ы (далее хэш) table distribution key (hereinafter hash)
select pg_get_table_distributedby('public.foo'::regclass)
Важно отметить, если при создании табл-ы из другой (CTAS) хэш не указан,
то получим DISTRIBUTED RANDOMLY, но если создается пустая через DDL
It is important to note that if the hash is not specified when creating a table from another (CTAS), then we will get DISTRIBUTED RANDOMLY, but if an empty one is created through DDL
, e.g.
create table t( a int, b bool), то хэшу GP назначит 1 колонку__then GP will assign first column to the hash*/: DISTRIBUTED BY(a)
Ежели 1) вернул NULL, значит перед нами вью __If 1) returned NULL, then we have a view
2) select pg_get_viewdef('public.foo') - вывод тела вью без хедера /*get view body without header*/(CREATE OR REPLACE ...),
т.е. ее /*only*/ SELECT ...
3) Код функции: — Function code
select pg_get_functiondef('public.fn_foo'::regproc)
Если ошибка, значит ф-я перегружена, т.е. существуют одноименные функции с разным набором параметров( словил 1 раз за 5 лет ).
If there is an error, it means the function is overloaded, i.e. there are functions with the same name with a different set of parameters (I caught it once in 5 years).
В этом случае есть вариант с перечислением типов параметров In this case we use the option with enumeration of parameter types
, e.g.
select pg_get_functiondef('public.foo(text, text, text, text, text, boolean)'::regprocedure)
либо вариант с oid, используемый ниже or the oid-specified variant below
4) Иногда надо найти ф-ии, скажем любые 5 в схеме etl, в тексте которых есть 'DROP CASCADE' :
Sometimes you need to find functions, say any 5 in the 'etl' scheme containing 'DROP CASCADE'
Как ни странно, простой вариант, без GROUP BY, я не нашел.
Кто знает - делитесь!
Oddly enough, I haven't found a simple option without GROUP BY.
If anyone knows, please share!
5) Часто надо узнать быстро число строк в табл-е , e.g. public.foo:
В relname передаем табл-у без схемы, и в первой паре колонок получаем число строк и схему табл-ы.
Хотя reltuples не всегда 100% точное, для анализа вполне хватает.
Если оно = 0, то вот это точно - либо по табл-е нет статистики, либо она партицированная.
Намеренно даю вариант, который может вернуть данные из разных схем, т.к. он быстрее в использовании.
6)
Как мы знаем (см. Секрет 1,6), GP частенько тупит, когда запрос идет к пустой табл-е, сопряженной с другими сущностями.
Есть сведения, что выключение оптимизатора GPORCA может ускорять выполнение запроса, как бы нам и хотелось.
В таком случае, если времянка идет по конвейеру перекладки данных, чтобы проверить число строк, только что записанных в нее, есть быстрый способ
проверить сессионную переменную
GET DIAGNOSTICS v_cnt = ROW_COUNT; ( аналог sql%rowcount в Oracle ).
Если v_cnt = 0, то можно отключить GPORCA, включив Postgres query optimizer:
SET optimizer=off
и либо выполнить следующий SQL запрос
либо просто пропустить его, если известно, что результат будет одинаков.
К слову, если перед выполнением каждого запроса необходимо логировать текущий режим оптимизатора(Legacy или GPORCA),
то select current_setting('optimizer') в помощь.
Через меня проходит много кода, поэтому поделюсь часто используемыми командами, которые настолько облегчают жизнь
что для каждой я создал snippet в DataGrip(макрос), чтобы не набивать ее каждый раз.
Secret 12 (A second saves an hour)
I handle a lot of code, so I'll share frequently used commands that make life so much easier
that I created a snippet in DataGrip (macro) for each one so I don't have to type it in every time.
1) ключ распределения табл-ы (далее хэш) table distribution key (hereinafter hash)
select pg_get_table_distributedby('public.foo'::regclass)
Важно отметить, если при создании табл-ы из другой (CTAS) хэш не указан,
то получим DISTRIBUTED RANDOMLY, но если создается пустая через DDL
It is important to note that if the hash is not specified when creating a table from another (CTAS), then we will get DISTRIBUTED RANDOMLY, but if an empty one is created through DDL
, e.g.
create table t( a int, b bool), то хэшу GP назначит 1 колонку__then GP will assign first column to the hash*/: DISTRIBUTED BY(a)
Ежели 1) вернул NULL, значит перед нами вью __If 1) returned NULL, then we have a view
2) select pg_get_viewdef('public.foo') - вывод тела вью без хедера /*get view body without header*/(CREATE OR REPLACE ...),
т.е. ее /*only*/ SELECT ...
3) Код функции: — Function code
select pg_get_functiondef('public.fn_foo'::regproc)
Если ошибка, значит ф-я перегружена, т.е. существуют одноименные функции с разным набором параметров( словил 1 раз за 5 лет ).
If there is an error, it means the function is overloaded, i.e. there are functions with the same name with a different set of parameters (I caught it once in 5 years).
В этом случае есть вариант с перечислением типов параметров In this case we use the option with enumeration of parameter types
, e.g.
select pg_get_functiondef('public.foo(text, text, text, text, text, boolean)'::regprocedure)
либо вариант с oid, используемый ниже or the oid-specified variant below
4) Иногда надо найти ф-ии, скажем любые 5 в схеме etl, в тексте которых есть 'DROP CASCADE' :
Sometimes you need to find functions, say any 5 in the 'etl' scheme containing 'DROP CASCADE'
with a as
(
select string_agg(pg_get_functiondef(p.oid), ',' order by 1) fn
from pg_proc p,
pg_namespace n
where p.pronamespace = n.oid
and n.nspname = 'etl'
group by p.oid
)
select fn from a
where upper(fn) like '%DROP%CASCADE%'
limit 5
Как ни странно, простой вариант, без GROUP BY, я не нашел.
Кто знает - делитесь!
Oddly enough, I haven't found a simple option without GROUP BY.
If anyone knows, please share!
5) Часто надо узнать быстро число строк в табл-е , e.g. public.foo:
select reltuples, s.nspname, *
from pg_class c,
pg_namespace s
where relname = 'foo'
and s.oid = c.relnamespace;
В relname передаем табл-у без схемы, и в первой паре колонок получаем число строк и схему табл-ы.
Хотя reltuples не всегда 100% точное, для анализа вполне хватает.
Если оно = 0, то вот это точно - либо по табл-е нет статистики, либо она партицированная.
Намеренно даю вариант, который может вернуть данные из разных схем, т.к. он быстрее в использовании.
6)
Как мы знаем (см. Секрет 1,6), GP частенько тупит, когда запрос идет к пустой табл-е, сопряженной с другими сущностями.
Есть сведения, что выключение оптимизатора GPORCA может ускорять выполнение запроса, как бы нам и хотелось.
В таком случае, если времянка идет по конвейеру перекладки данных, чтобы проверить число строк, только что записанных в нее, есть быстрый способ
проверить сессионную переменную
GET DIAGNOSTICS v_cnt = ROW_COUNT; ( аналог sql%rowcount в Oracle ).
Если v_cnt = 0, то можно отключить GPORCA, включив Postgres query optimizer:
SET optimizer=off
и либо выполнить следующий SQL запрос
либо просто пропустить его, если известно, что результат будет одинаков.
К слову, если перед выполнением каждого запроса необходимо логировать текущий режим оптимизатора(Legacy или GPORCA),
то select current_setting('optimizer') в помощь.
👍4
5) Often you need to quickly check presence of data in a table, e.g. public.foo:
In relname we pass a table without a schema, and in the first pair of columns we get the number of rows and the table schema.
Although reltuples is not always 100% accurate, it is quite sufficient for analysis.
If it = 0, then this is for sure - either there are no statistics for the table, or it is partitioned.
I intentionally give the option that can return data from different schemas, because it is faster to use.
6)
As we know (see Secret 1.6), GP often slows down when a query goes to an empty table joined with other entities.
There is information that disabling the GPORCA optimizer can speed up query execution, as we would like.
In this case, if the temporary one goes along the data transfer pipeline, in order to check the number of rows just written to it, there is a quick way
to check the session variable
GET DIAGNOSTICS v_cnt = ROW_COUNT; (analogous to sql%rowcount in Oracle).
If v_cnt = 0, then you can disable GPORCA by enabling Postgres query optimizer:
SET optimizer=off
and either execute the following SQL query
or simply skip it if you know that the result will be the same.
By the way, if you need to log the current optimizer mode (Legacy or GPORCA) before executing each query, then select current_setting('optimizer') will help.
select reltuples, s.nspname, *
from pg_class c,
pg_namespace s
where relname = 'foo'
and s.oid = c.relnamespace;
In relname we pass a table without a schema, and in the first pair of columns we get the number of rows and the table schema.
Although reltuples is not always 100% accurate, it is quite sufficient for analysis.
If it = 0, then this is for sure - either there are no statistics for the table, or it is partitioned.
I intentionally give the option that can return data from different schemas, because it is faster to use.
6)
As we know (see Secret 1.6), GP often slows down when a query goes to an empty table joined with other entities.
There is information that disabling the GPORCA optimizer can speed up query execution, as we would like.
In this case, if the temporary one goes along the data transfer pipeline, in order to check the number of rows just written to it, there is a quick way
to check the session variable
GET DIAGNOSTICS v_cnt = ROW_COUNT; (analogous to sql%rowcount in Oracle).
If v_cnt = 0, then you can disable GPORCA by enabling Postgres query optimizer:
SET optimizer=off
and either execute the following SQL query
or simply skip it if you know that the result will be the same.
By the way, if you need to log the current optimizer mode (Legacy or GPORCA) before executing each query, then select current_setting('optimizer') will help.
Секрет 13 (Контора пишет)
Как-то прилетела задачка протестировать список из 100 запросов, предоставив на выходе план и время выполнения каждого.
Т.к. лень - двигатель прогресса, я автоматизировал основную задачу - получить то что нужно для конкретного запроса.
Для этого создал ф-ю, которая возвращает план запроса в JSON
Далее создал журнал для результата
И проверил, что все работает
и что любая метрика легко доступна из узла плана через jsonpath, e.g.
Сам план представляет массив из 1 элемента
Дальше выгрузил список SQL запросов из XLS в табл-у, по которой пробежался в цикле методом выше и задача была решена.
Как-то прилетела задачка протестировать список из 100 запросов, предоставив на выходе план и время выполнения каждого.
Т.к. лень - двигатель прогресса, я автоматизировал основную задачу - получить то что нужно для конкретного запроса.
Для этого создал ф-ю, которая возвращает план запроса в JSON
CREATE OR REPLACE FUNCTION public.estimate_cost(query text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
query_explain text;
explanation json;
BEGIN
query_explain :=e'EXPLAIN (ANALYZE, FORMAT JSON) ' || query;
EXECUTE query_explain INTO explanation;
RETURN explanation;
END;
$function$
Далее создал журнал для результата
create table public.tst_plan (query_plan json, id int) DISTRIBUTED BY (id)
И проверил, что все работает
insert into public.tst_plan
select *, 1 from public.estimate_cost_0('select generate_series(1,10000)')
и что любая метрика легко доступна из узла плана через jsonpath, e.g.
SQL> select (query_plan->0)->>'Execution Time' as exec_tm from public.tst_plan where id = 1
exec_tm
-------
1.444
Сам план представляет массив из 1 элемента
select query_plan from public.tst_plan where id = 1
query_plan
—————————————————————————————-
[
{
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.00,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.055,
"Actual Total Time": 0.770,
"Actual Rows": 10000,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Startup Cost": 0.00,
"Total Cost": 0.00,
"Plan Rows": 1,
"Plan Width": 1,
"Actual Startup Time": 0.024,
"Actual Total Time": 0.024,
"Actual Rows": 1,
"Actual Loops": 1
}
]
},
"Planning Time": 2.391,
"Triggers": [
],
"Slice statistics": [
{
"Slice": 0,
"Executor Memory": 57512
}
],
"Statement statistics": {
"Memory used": 311296
},
"Settings": {
"Optimizer": "Pivotal Optimizer (GPORCA)"
},
"Execution Time": 1.444
}
]
Дальше выгрузил список SQL запросов из XLS в табл-у, по которой пробежался в цикле методом выше и задача была решена.
👍7❤2
Secret 13 (Office writes)
Once I was given a task to test a list of 100 queries, providing a plan and execution time for each.
Since laziness is the engine of progress, I automated the main task - to get what is needed for a specific query.
For this, I created a function that returns a query plan in JSON
Then I created a log for the result
and checked that everything works.
and that any metric is easily accessible from the plan node via jsonpath, e.g.
The plan itself is an array of 1 element.
Then I unloaded the list of SQL queries from XLS into a table, which I ran through in a loop using the method above, and the problem was solved.
Once I was given a task to test a list of 100 queries, providing a plan and execution time for each.
Since laziness is the engine of progress, I automated the main task - to get what is needed for a specific query.
For this, I created a function that returns a query plan in JSON
CREATE OR REPLACE FUNCTION public.estimate_cost(query text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
query_explain text;
explanation json;
BEGIN
query_explain :=e'EXPLAIN (ANALYZE, FORMAT JSON) ' || query;
EXECUTE query_explain INTO explanation;
RETURN explanation;
END;
$function$
Then I created a log for the result
create table public.tst_plan (query_plan json, id int) DISTRIBUTED BY (id)
and checked that everything works.
insert into public.tst_plan
select *, 1 from public.estimate_cost_0('select generate_series(1,10000)')
and that any metric is easily accessible from the plan node via jsonpath, e.g.
SQL> select (query_plan->0)->>'Execution Time' as exec_tm from public.tst_plan where id = 1
exec_tm
-------
1.444
The plan itself is an array of 1 element.
select query_plan from public.tst_plan where id = 1
query_plan
—————————————————————————————-
[
{
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 0.00,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.055,
"Actual Total Time": 0.770,
"Actual Rows": 10000,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Startup Cost": 0.00,
"Total Cost": 0.00,
"Plan Rows": 1,
"Plan Width": 1,
"Actual Startup Time": 0.024,
"Actual Total Time": 0.024,
"Actual Rows": 1,
"Actual Loops": 1
}
]
},
"Planning Time": 2.391,
"Triggers": [
],
"Slice statistics": [
{
"Slice": 0,
"Executor Memory": 57512
}
],
"Statement statistics": {
"Memory used": 311296
},
"Settings": {
"Optimizer": "Pivotal Optimizer (GPORCA)"
},
"Execution Time": 1.444
}
]
Then I unloaded the list of SQL queries from XLS into a table, which I ran through in a loop using the method above, and the problem was solved.
Вы используете регламентный VACUUM в вашем DWH ? -- Do you use scheduled VACUUM in your DWH from vendor?
Anonymous Poll
68%
Y
10%
N
2%
Отказались ввиду отсутствия UPDATE/DELETE -- Deactivated due to lack of UPDATE/DELETE
12%
А что это ? -- What is this?
7%
Нет, Обязанность VACUUM возложена на разработчика -- No, Developer is responsible for VACUUM
Секрет 14 (Разделяй и властвуй или причина 1 Redistribute Motion )
Попросили проанализировать, почему запрос, который генерит наш фреймворк усиленно порождает спилл и падает с ошибкой workfile per query size limit exceeded.
Сам UPDATE приводить не буду, он довольно сложен, но вот эта его часть из CTE меня насторожила :
Secret 14 (Divide and conquer or Redistribute Motion-reason 1)
I was asked to analyze why the query that our framework generates is actively generating a spill and crashing with the error workfile per query size limit exceeded.
I will not provide the UPDATE itself, it is quite complex, but this part of it from the CTE alerted me:
Анализ показал, что invalid_id в таблице T одинаковы
Несмотря на то, что T имеет ~ 2 млрд записей что типично для нашего DWH,
запрос * тоже оказался несовместим с жизнью нашего GP и выполниться не смог.
Analysis showed that invalid_id in table T are the same
Despite the fact that T has ~2 billion records, which is typical for our DWH,
the query * also turned out to be incompatible with the life of our GP and could not be executed.
Сделав сэмпл foo из 1 000 записей T выполним запрос * и заглянем в план: Having made a sample foo of 1,000 T records, we execute the query * and look at the plan:
Причина ясна - Redistribute Motion и ни о каком локальном вычислении на нодах в парадигме shared nothing речи не идет.
С учетом, что T распределена не по invalid_id, GP пытается тиражировать каждую ноду навсе остальные одну конкретную ноду - жертву, т.к. invalid_id раскиданы по всему кластеру, хоть они и одинаковы. Т.е. по сути GP делает в рамках *
времянкуDISTRIBUTED REPLCATED из 2 млрд строк на одном сегменте.чтобы только у него была вся инфо для расчета row_number().
The reason is clear - Redistribute Motion and there is no chance for any local computation on nodes in the shared nothing paradigm.
Considering that T is not distributed by invalid_id, GP tries to replicate each node to one specific node - the victim,
since invalid_id are scattered throughout the cluster, although they are the same. That is, in essence, GP makes within the framework of *
a temporary storage of 2 billion rows on one segment Z. So that only Z has all the information for calculating row_number().
Попросили проанализировать, почему запрос, который генерит наш фреймворк усиленно порождает спилл и падает с ошибкой workfile per query size limit exceeded.
Сам UPDATE приводить не буду, он довольно сложен, но вот эта его часть из CTE меня насторожила :
Secret 14 (Divide and conquer or Redistribute Motion-reason 1)
I was asked to analyze why the query that our framework generates is actively generating a spill and crashing with the error workfile per query size limit exceeded.
I will not provide the UPDATE itself, it is quite complex, but this part of it from the CTE alerted me:
select row_number() over (partition by invalid_id order by version_id) as vsn_rank, hash_diff, invalid_id from T (*)
Анализ показал, что invalid_id в таблице T одинаковы
Несмотря на то, что T имеет ~ 2 млрд записей что типично для нашего DWH,
запрос * тоже оказался несовместим с жизнью нашего GP и выполниться не смог.
Analysis showed that invalid_id in table T are the same
Despite the fact that T has ~2 billion records, which is typical for our DWH,
the query * also turned out to be incompatible with the life of our GP and could not be executed.
Сделав сэмпл foo из 1 000 записей T выполним запрос * и заглянем в план: Having made a sample foo of 1,000 T records, we execute the query * and look at the plan:
Gather Motion 720:1 (slice2; segments: 720) (cost=0.00..431.06 rows=1000 width=24) (actual time=1741.861..1800.971 rows=1000 loops=1)
-> Result (cost=0.00..431.00 rows=2 width=24) (actual time=1735.509..1736.502 rows=1000 loops=1)
-> WindowAgg (cost=0.00..431.00 rows=2 width=24) (actual time=1735.505..1736.368 rows=1000 loops=1)
Partition By: invalid_id
Order By: version_id
-> Sort (cost=0.00..431.00 rows=2 width=49) (actual time=1735.472..1735.573 rows=1000 loops=1)
" Sort Key: invalid_id, version_id"
Sort Method: quicksort Memory: 23872kB
-> Redistribute Motion 720:720 (slice1; segments: 720) (cost=0.00..431.00 rows=2 width=49) (actual time=22.711..1726.052 rows=1000 loops=1)
Hash Key: invalid_id
-> Seq Scan on foo (cost=0.00..431.00 rows=2 width=49) (actual time=20.166..20.191 rows=7 loops=1)
Planning time: 19.026 ms
(slice0) Executor memory: 971K bytes.
" (slice1) Executor memory: 459K bytes avg x 720 workers, 460K bytes max (seg0)."
" (slice2) Executor memory: 191K bytes avg x 720 workers, 319K bytes max (seg305). Work_mem: 145K bytes max."
Memory used: 229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 2407.517 ms
Причина ясна - Redistribute Motion и ни о каком локальном вычислении на нодах в парадигме shared nothing речи не идет.
С учетом, что T распределена не по invalid_id, GP пытается тиражировать каждую ноду на
времянку
The reason is clear - Redistribute Motion and there is no chance for any local computation on nodes in the shared nothing paradigm.
Considering that T is not distributed by invalid_id, GP tries to replicate each node to one specific node - the victim,
since invalid_id are scattered throughout the cluster, although they are the same. That is, in essence, GP makes within the framework of *
a temporary storage of 2 billion rows on one segment Z. So that only Z has all the information for calculating row_number().
Проверим, а всегда ли нас ждет такой печальный конец для оконной функции?
Перераспределим 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)
Видим, что 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:
В итоге, исходный запрос лечению не подлежит, т.к. проблема должна решаться выше, на уровне бизнес-анализа,
ибо расчет ранга по данному техническому полю смысла не имеет - баг!
Но мораль истории в том, что в 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.
Перераспределим 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.
👍6
Каков размер вашего ХД чисто в пром контуре ( без DR, S3, Hadoop и пр. )? __What is the size of your DWH in prod zone only (without DR,S3, Hadoop etc ) __?
Anonymous Poll
10%
< 0.1 TB
3%
< 1 TB
16%
< 10 TB
28%
< 100 TB
4%
< 250 TB
15%
< 500 TB
7%
< 750 TB
8%
< 1 PB
4%
< 10 PB
4%
< 100 PB
Каста, а вы играете в интеллектуальные игры ? Elite, do you play mind games?
Anonymous Poll
9%
Шашки Checkers
14%
Нарды Backgammon
36%
Шахматы Chess
7%
Го Go
18%
Покер Poker
5%
Блэкджек Blackjack
9%
Преферанс Preference
7%
На сцене( в театре, квартет и т.д. ) On stage (in the theater, quartet, etc.)
13%
Имаджинариум __Imaginarium__
41%
Ничего из вышеперчисленного None of the above
Секрет 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:
Благодарен @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
Спасибо @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:
ч.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:
🔥8👍1
Вышла полезная статья, к которой я оставил интересующий меня комментарий, который автор отклонил к публикации. Он был о том как в алгоритмическом способе ускорения count(distinct) (№2) можно сгладить перекос данных, добавив в ключ GROUP BY - день продажи.
Кмк при этом объем входных (для группировки) данных пропорционально увеличится на всех сегментах, а значит перекос не уйдет
А вам понятно ?
Кмк при этом объем входных (для группировки) данных пропорционально увеличится на всех сегментах, а значит перекос не уйдет
А вам понятно ?
Хабр
Кейс оптимизации запросов для Greenplum
Всем привет! Меня зовут Андрей , я работаю дата аналитиком в Data Team продукта Dialog.X5/Insights в X5 Tech. Мы предоставляем аналитику по продажам и покупательскому поведению на данных X5...
Секрет 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
Результат взвешивания уже показал статистическую разницу The weighing result already gives a statistical difference
Ого, Выигрыш от того, что не поленились и задали ключ - 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)
Невероятно, но разница выросла в ~7 раз Incredibly, the difference has increased by ~7 times
Цифры говорят сами за себя. Мне хватило тестов на одной колонке (т.к. 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
Заинтересовался кейсом от поддержки, почему вакуум на проме и 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
👏6👍4❤1
Секрет 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
Обнаружил аналог, предложенный Feng Tian из Vitessedata
который читает табл-ы 1 раз, избегая join-а:
I discovered another one suggested by Feng Tian from Vitessedata
which reads tables once, avoiding join:
Как думаете какой способ лучше ?
Which method do you think is better?
Однажды многим предстоит сравнить 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?
🔥1
Какой способ для поиска разницы лучше в вариантах выше ? __Which method is better for finding the difference in the options above?__
Anonymous Poll
42%
1(join)
58%
2 (no join)
Секрет 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:
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.
Т.к. мнения разделились, провел 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.
🔥1
Таблицу просеиваете через другую используя NOT IN или NOT EXISTS ? Do you filter a table through another using NOT IN or NOT EXISTS?
Anonymous Poll
33%
not in
67%
not exists