Секрет 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
План запроса для NOT IN: Query plan for NOT IN:
Не буду грузить вас тут планом и для exists в вечер Пт просто рез-т:
I won't burden you with a plan for EXISTS on Friday evening, just the result:
Также посмотрим на вариант с LEFT JOIN,который не вошел в опрос, но любит @andre_rumyanec
Let's also look at the option with LEFT JOIN (suggested by @andre_rumyanec)
И таким образом, LEFT JOIN выбился в лидеры.
Единственное, если вы соблазнитесь им и для IN -фильтра, не забудьте обернуть рез-т DISTINCT-ом, т.к. если фильтр табл-а содержит дубли предикатного ключа, то возможно размножение строк.
Ни фото ниже для полного раскрытия темы, сравним планы золотого и серебряного призера, отставшего на 0.25 с.
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 с.
❤1
Секрет 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:
Отберем актуальные (на последнюю версию) записи
Let's select the latest records
Несмотря на то, что 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:
Перераспределим таблицу по хэшу от n
Let's redistribute the table by n
Часто встает задача взять последние версии записей в 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)
❤1👍1
Выполним тот же запрос. Видим, что из плана исчез Redistribute и ожидаемо запрос ускорился :
Let's execute the same query. We see that Redistribute has disappeared from the plan and, as expected, the query has accelerated:
Напрашивается простой вывод
Если хэш табл-ы поменять нельзя и квота в схеме позволяет, то материализация 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:
Чекнем план: Let's check the query plan
Кстати, результат получился даже быстрее, чем в запросе без 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
Работа над ошибками (Секрет 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?
Очень приятно, что среди вас есть те, кто критически подходят к контенту.
Благодарю @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:
В рамках нового подхода будем в каждом прогоне ( чтобы исключить возможность кэширования, которой нет, но все же) создавать новую промежуточную табл-у из 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)
используя ее для отбора актуальных версий уже без Redistribute
using it to select current versions without Redistribute
Затем выполним исходный запрос(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:
Ниже таблица с результатом, где
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.
В заключение, решил проверить свою интуицию и ответить на вопрос - стоит ли игра свеч ?
Сравним исходный запрос с 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.
👍1
Секрет 20( Сила хард-кода )
На днях попросили оптимизировать запрос, создающий спилл 15 TB
в котором идет join 4 таблиц + 2 фильтра: с IN и NOT IN вида:
Secret 20 (The power of hard code)
Recently I was asked to optimize a query that creates a 15 TB spill and looks like this
Подкрутив ключи у таблиц, я неожиданно обнаружил,
что без последнего NOT IN оптимальный запрос отработал в 5 раз быстрее, хотя в f2 всего 5 строк.
Замена NOT IN на NOT EXISTS избавила план от Broadcast, но все же запрос без данного фильтра
работал вдвое быстрее.
И тут родилась идея, а что если заменить данный фильтр на список, сформированный из f2.
Я задался вопросом (для IN) - когда динамический табличный фильтр уступает идентичному IN LIST список по производительности.
Возьмем табл-у foo из 300 млн строк:
Having tweaked the keys of the tables, I unexpectedly discovered
that without the last NOT IN the optimal query worked 5 times faster, although f2 only had 5 rows.
Replacing NOT IN with NOT EXISTS got rid of the plan from Broadcast, but the query without this filter
still worked 2x faster.
And then an idea was born: what if we replace this filter with a list formed from f2.
I asked myself (for IN) - when does a dynamic table filter outperform an identical IN LIST list in performance.
Let's take the foo table with 300 million rows:
Создадим табл-у - фильтр из 10 элементов, через который будем просеивать foo:
Let's create a table-filter of 10 elements, through which we filter the foo:
и константу - список из нее
and the constant - a list from it
Теперь сравним результат запросов A - с динамическим табличным фильтром и Б - с фильтром по списку,
взяв за рез-т время выполнения из плана запроса:
Now let's compare the results of queries A - with a dynamic table filter and B - with a list filter, taking the execution time from the query plan as the result
A:
Б:
Планы даны на фото ниже, где видим, что IN тоже может вызывать Broadcast.
Всего проведено по 3 теста для 5 фильтров из 1,10, ..., 10 000 элементов аналогичным образом, где размер кода для последнего вырос до 50кб.
Рез-ты представлены в табл-е, где выигрыш(проигрыш) представлен в поле Е - отношение средних результатов А к Б в разрезе каждого из 5 фильтров д.б. >1 (<1)
Вывод: Если фильтр содержит до 100 элем-ов, то резонно фильтровать табл-у списком.
Если стата по табл-ам собрана, то для оценки числа строк годится pg_class.reltuples, чтобы не гонять select count(*) по ним вхолостую.
The plans are shown in the photo below, where we see that IN can also call Broadcast.
In total, 3 tests were conducted for 5 filters from 1,10, ..., 10,000 elements in a similar way.
The results are presented in the table, where the gain (loss for value < 1) is presented in field E-the ratio of the average results A to B for each filter.
Conclusion: If the filter contains up to 100 elements, then it's reasonable to filter the table with list.
If the statistics on the tables are collected, then pg_class.reltuples is suitable for assessing the number of rows, to avoid running select count (*) on them.
На днях попросили оптимизировать запрос, создающий спилл 15 TB
в котором идет join 4 таблиц + 2 фильтра: с IN и NOT IN вида:
Secret 20 (The power of hard code)
Recently I was asked to optimize a query that creates a 15 TB spill and looks like this
select distinct < .. >
FROM a
JOIN b t1 on a.credit_acc_num = b1.acc_num
JOIN b t2 on a.debit_acc_num = b2.acc_num
LEFT JOIN b2 .. b3 ON < .. >
WHERE 1 = 1
AND t1.gl_acc_num IN (select id from f1 )
AND t2.gl_acc_num NOT IN (select id from f2)
Подкрутив ключи у таблиц, я неожиданно обнаружил,
что без последнего NOT IN оптимальный запрос отработал в 5 раз быстрее, хотя в f2 всего 5 строк.
Замена NOT IN на NOT EXISTS избавила план от Broadcast, но все же запрос без данного фильтра
работал вдвое быстрее.
И тут родилась идея, а что если заменить данный фильтр на список, сформированный из f2.
Я задался вопросом (для IN) - когда динамический табличный фильтр уступает идентичному IN LIST список по производительности.
Возьмем табл-у foo из 300 млн строк:
Having tweaked the keys of the tables, I unexpectedly discovered
that without the last NOT IN the optimal query worked 5 times faster, although f2 only had 5 rows.
Replacing NOT IN with NOT EXISTS got rid of the plan from Broadcast, but the query without this filter
still worked 2x faster.
And then an idea was born: what if we replace this filter with a list formed from f2.
I asked myself (for IN) - when does a dynamic table filter outperform an identical IN LIST list in performance.
Let's take the foo table with 300 million rows:
create table public.foo WITH (appendonly = true, orientation = column, compresstype = zstd, compresslevel = 1)
as
with a as (
select generate_series(1, 1e8::int) 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 );
Создадим табл-у - фильтр из 10 элементов, через который будем просеивать foo:
Let's create a table-filter of 10 elements, through which we filter the foo:
create table public.tst_flt_10 WITH (appendonly=true,orientation=column)
as select generate_series(1,10) n distributed by(n);
и константу - список из нее
and the constant - a list from it
select string_agg(n::text, ',' order by n) from public.tst_flt_10
Теперь сравним результат запросов A - с динамическим табличным фильтром и Б - с фильтром по списку,
взяв за рез-т время выполнения из плана запроса:
Now let's compare the results of queries A - with a dynamic table filter and B - with a list filter, taking the execution time from the query plan as the result
A:
explain analyze
select a.n, max(a.version_id)
from public.foo a
where a.n in (select n from public.tst_flt_10)
group by 1
Б:
explain analyze
select a.n, max(a.version_id)
from public.foo a
where a.n in (1,2,3,4,5,6,7,8,9,10)
group by 1
Планы даны на фото ниже, где видим, что IN тоже может вызывать Broadcast.
Всего проведено по 3 теста для 5 фильтров из 1,10, ..., 10 000 элементов аналогичным образом, где размер кода для последнего вырос до 50кб.
Рез-ты представлены в табл-е, где выигрыш(проигрыш) представлен в поле Е - отношение средних результатов А к Б в разрезе каждого из 5 фильтров д.б. >1 (<1)
Вывод: Если фильтр содержит до 100 элем-ов, то резонно фильтровать табл-у списком.
Если стата по табл-ам собрана, то для оценки числа строк годится pg_class.reltuples, чтобы не гонять select count(*) по ним вхолостую.
The plans are shown in the photo below, where we see that IN can also call Broadcast.
In total, 3 tests were conducted for 5 filters from 1,10, ..., 10,000 elements in a similar way.
The results are presented in the table, where the gain (loss for value < 1) is presented in field E-the ratio of the average results A to B for each filter.
Conclusion: If the filter contains up to 100 elements, then it's reasonable to filter the table with list.
If the statistics on the tables are collected, then pg_class.reltuples is suitable for assessing the number of rows, to avoid running select count (*) on them.
👍1
Прошел месяц как Аренадата вышла на торги. Рост капитализации более 17% в руб пока ЦБ душит бизнесы ставкой. Лучшее IPO из ИТ компаний РФ. Емнип, все другие компании РФ падали по первой ниже цены размещения.
A month gone since Arenadata went public. Capitalization growth of more than 17% in rubles while the Central Bank stifles businesses with interest rates. It's the best IPO of Russian IT companies. If I remember correctly, all other Russian companies fell at the first below the placement price.
A month gone since Arenadata went public. Capitalization growth of more than 17% in rubles while the Central Bank stifles businesses with interest rates. It's the best IPO of Russian IT companies. If I remember correctly, all other Russian companies fell at the first below the placement price.
Greenplum secrets🎩
Секрет 17 (Однопроходные весы) Однажды многим предстоит сравнить 2 табл-ы. Скорее всего, 95% ( включая меня ) используют запрос вида: Secret 17 (Single-Pass Comparator) Many people will have to compare 2 tables at some point. Most likely, 95% (including…
Сейчас очень порадовал однопроходный метод из Секрета 17. На сравнение 2 табл A и Б порядка ~1 млн строк ушло ~0.5 c, тогда как чекнуть разницу через двойной except заняло > 34 с:
0 rows retrieved in 484 ms (execution: 422 ms, fetching: 62 ms) — fast diff
0 rows retrieved in 17 s 601 ms (execution: 969 ms, fetching: 16 s 632 ms) — 1st except A - B
0 rows retrieved in 16 s 480 ms (execution: 500 ms, fetching: 15 s 980 ms) — 2nd except B-A
Есть идеи, почему такой долгий fetching для операции ?
0 rows retrieved in 484 ms (execution: 422 ms, fetching: 62 ms) — fast diff
0 rows retrieved in 17 s 601 ms (execution: 969 ms, fetching: 16 s 632 ms) — 1st except A - B
0 rows retrieved in 16 s 480 ms (execution: 500 ms, fetching: 15 s 980 ms) — 2nd except B-A
Есть идеи, почему такой долгий fetching для операции ?
select * from A
except
select * from B
Cпилл, создаваемый запросом зависит от нагрузки на сервер со стороны других сессий ?
Do you think the spill generated by the request depends on the load on the server from other sessions?
Do you think the spill generated by the request depends on the load on the server from other sessions?
Anonymous Poll
65%
Yes
35%
No
Greenplum secrets🎩
Cпилл, создаваемый запросом зависит от нагрузки на сервер со стороны других сессий ?
Do you think the spill generated by the request depends on the load on the server from other sessions?
Do you think the spill generated by the request depends on the load on the server from other sessions?
Ответ на опрос в 2х словах: да, зависит.
Обоснование:
У нас ежедневно среди прочих выполняется запрос Q вида
Answer to the quiz above in 2 words: yes, it depends.
Justification:
We have a daily request Q among others
где foo - легковесная дельта distributed randomly из пары дюжин скалярных полей (int,text,date), idl.data_mart - фрагментирована (2 партиции) общим весом 144 GB.
6.11 Q создал спилл 24 TB ( в foo было 600k строк )
7.11 Q выполнился без спилла ( в foo было 6m строк общим весом 0.2 ГБ, т.е. на порядок больше) - план ниже.
Я создал синтетику из 600k строк из 24 int полей, чтобы воспроизвести прецедент 6.11 - запрос выполнился без спилла.
Я проверил гипотезу, а что если по foo не была собрана стата - запрос выполнился почти без спилла (16 GB)
В итоге, у меня остался один вопрос - а как в принципе GP6 смогла создать такой спилл,
если в плане только один Redistribute. Иного объяснения, кроме как то, что join выполнялся в виртуальной памяти 6.11.24,
т.е. на диске, я не нахожу.
where foo is a lightweight delta distributed randomly from a couple dozen scalar fields (int, text, date), idl.data_mart is fragmented (2 partitions) with a total weight of 144 GB.
6.11 Q created a 24 TB spill ( foo had 600k rows)
7.11 Q was executed without a spill ( foo had 6m rows with a total weight of 0.2 GB, i.e. an order of magnitude more) - the plan is below.
I created synthetics from 600k rows from 24 int fields to reproduce the 6.11 precedent - the query was executed without a spill.
I checked the hypothesis, what if there was no statistics collected for foo - the query was executed almost without a spill (16 GB)
As a result, I have ONLY one unresolved question - HOW COME that GP6 was heroically able to create such big spill,
if there is only one Redistribute in the plan. I can't find any other explanation other than that join was executed in virtual memory on 6.11.24, i.e. on disk.
Обоснование:
У нас ежедневно среди прочих выполняется запрос Q вида
Answer to the quiz above in 2 words: yes, it depends.
Justification:
We have a daily request Q among others
select true
from (select counterparty_rk
from stg.foo
) s
where not exists(select 1 from idl.data_mart t where s.counterparty_rk = t.counterparty_rk)
limit 1
где foo - легковесная дельта distributed randomly из пары дюжин скалярных полей (int,text,date), idl.data_mart - фрагментирована (2 партиции) общим весом 144 GB.
6.11 Q создал спилл 24 TB ( в foo было 600k строк )
7.11 Q выполнился без спилла ( в foo было 6m строк общим весом 0.2 ГБ, т.е. на порядок больше) - план ниже.
Я создал синтетику из 600k строк из 24 int полей, чтобы воспроизвести прецедент 6.11 - запрос выполнился без спилла.
Я проверил гипотезу, а что если по foo не была собрана стата - запрос выполнился почти без спилла (16 GB)
В итоге, у меня остался один вопрос - а как в принципе GP6 смогла создать такой спилл,
если в плане только один Redistribute. Иного объяснения, кроме как то, что join выполнялся в виртуальной памяти 6.11.24,
т.е. на диске, я не нахожу.
where foo is a lightweight delta distributed randomly from a couple dozen scalar fields (int, text, date), idl.data_mart is fragmented (2 partitions) with a total weight of 144 GB.
6.11 Q created a 24 TB spill ( foo had 600k rows)
7.11 Q was executed without a spill ( foo had 6m rows with a total weight of 0.2 GB, i.e. an order of magnitude more) - the plan is below.
I created synthetics from 600k rows from 24 int fields to reproduce the 6.11 precedent - the query was executed without a spill.
I checked the hypothesis, what if there was no statistics collected for foo - the query was executed almost without a spill (16 GB)
As a result, I have ONLY one unresolved question - HOW COME that GP6 was heroically able to create such big spill,
if there is only one Redistribute in the plan. I can't find any other explanation other than that join was executed in virtual memory on 6.11.24, i.e. on disk.
👍1
Limit (cost=0.00..15089.37 rows=1 width=1) (actual time=1954.485..1954.485 rows=0 loops=1)
-> Gather Motion 864:1 (slice2; segments: 864) (cost=0.00..15089.37 rows=1 width=1) (actual time=1954.445..1954.445 rows=0 loops=1)
-> Limit (cost=0.00..15089.37 rows=1 width=1) (actual time=0.000..1926.607 rows=0 loops=1)
-> Result (cost=0.00..15089.37 rows=1147 width=1) (actual time=0.000..1926.600 rows=0 loops=1)
-> Result (cost=0.00..15089.37 rows=1147 width=1) (actual time=0.000..1926.596 rows=0 loops=1)
" Filter: (COALESCE((count()), '0'::bigint) = '0'::bigint)"
-> Result (cost=0.00..15089.27 rows=2868 width=8) (actual time=0.000..1926.592 rows=0 loops=1)
-> Hash Right Join (cost=0.00..15089.25 rows=2868 width=8) (actual time=0.000..1926.588 rows=0 loops=1)
Hash Cond: (data_mart.counterparty_rk = foo.counterparty_rk)
-> Result (cost=0.00..1410.20 rows=3407709 width=16) (never executed)
-> HashAggregate (cost=0.00..1355.67 rows=3407709 width=16) (never executed)
Group Key: data_mart.counterparty_rk
-> Sequence (cost=0.00..832.09 rows=3407709 width=8) (never executed)
-> Partition Selector for data_mart (dynamic scan id: 1) (cost=10.00..100.00 rows=1 width=4) (never executed)
Partitions selected: 2 (out of 2)
-> Dynamic Seq Scan on data_mart (dynamic scan id: 1) (cost=0.00..832.09 rows=3407709 width=8) (never executed)
-> Hash (cost=447.69..447.69 rows=2868 width=8) (actual time=0.000..1925.847 rows=0 loops=1)
-> Redistribute Motion 864:864 (slice1; segments: 864) (cost=0.00..447.69 rows=2868 width=8) (actual time=0.000..1925.846 rows=0 loops=1)
Hash Key: foo.counterparty_rk
-> Seq Scan on foo (cost=0.00..431.79 rows=2868 width=8) (actual time=0.000..1040.971 rows=0 loops=1)
" Filter: (COALESCE((counterparty_rk)::text, '-1'::text) <> '-1'::text)"
Planning time: 49.665 ms
(slice0) Executor memory: 711K bytes.
" (slice1) Executor memory: 212K bytes avg x 864 workers, 212K bytes max (seg0)."
" (slice2) Executor memory: 1215K bytes avg x 864 workers, 1215K bytes max (seg0)."
Memory used: 229376kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 2173.226 ms
Думаю, стоит отдельно остановиться на вопросах моего самого активного подписчика @GaRin_1979, вызванных последним квизом:
Итак, по порядку.
Увы, машины времени нет, чтобы получить идентичную табл-у foo. План был на 99% тот, что в посте, исходя из вводных, что я давал.
Если же по таблице foo не было статы (вероятность этого ничтожно мала исходя из GUC), то в плане будет Hash anti-join вместо Right Join
и другой порядок операторов, но на спилл в данном случае это влияет никак, исходя из моих тестов, которые также вошли в пост.
1) Полный запрос дан в посте, просто названия табл другие и чтобы не оттенять суть, я исключил условие where coalesce(..) != '-1', которое осталось в плане.
Запрос выполнился, не упал. Конкретно для этого запроса это впервые за последние 30 дн. В принципе, с авг 23г аналогов
набралось с десяток, когдаб например, пытаешься воспроизвести спилл в pl/pgsql ф-ии на тех же входных параметрах под другой РГ, а он нулевой.
Спилл у нас собирает коллектор ежеминутно из gp_toolkit.gp_workfile_entries - Слава нашим DBA!
Соотв-но, в своих выводах опираюсь на историю спиллов, зафиксированную оным.
2) В gp_toolkit.gp_workfile_entries.optype обнаружен только HashJoin.
В моей реальности, Redistribute Motion ( а в плане он единственный) не может создать спилл больше, чем табл-а, которую он перераспределяет, в которой было 600к строк.
Или я не прав? Гипотеза, что эта таблица весила 24 TB несостоятельна, т.к. она создана за 1 сек. Гипотеза, что редистрибутилась idl.data_mart,
в сжатом виде 144 GB (zstd1) тоже несостоятельна - если взять as is ( т.е. без компрессии) 10% случайных записей из ее 3 млрд строк - это лишь 50 ГБ. Т.е. ее Redistribute мог бы дать 0.5 - 1 TB max.
Если бы в плане был ее Broadcast, то 24 TB бы не отделались, но и время бы было не 2000 сек на запрос.
Т.е. у меня в принципе нет объяснения произошедшему.
3) off
Итак, по порядку.
Увы, машины времени нет, чтобы получить идентичную табл-у foo. План был на 99% тот, что в посте, исходя из вводных, что я давал.
Если же по таблице foo не было статы (вероятность этого ничтожно мала исходя из GUC), то в плане будет Hash anti-join вместо Right Join
и другой порядок операторов, но на спилл в данном случае это влияет никак, исходя из моих тестов, которые также вошли в пост.
1) Полный запрос дан в посте, просто названия табл другие и чтобы не оттенять суть, я исключил условие where coalesce(..) != '-1', которое осталось в плане.
Запрос выполнился, не упал. Конкретно для этого запроса это впервые за последние 30 дн. В принципе, с авг 23г аналогов
набралось с десяток, когдаб например, пытаешься воспроизвести спилл в pl/pgsql ф-ии на тех же входных параметрах под другой РГ, а он нулевой.
Спилл у нас собирает коллектор ежеминутно из gp_toolkit.gp_workfile_entries - Слава нашим DBA!
Соотв-но, в своих выводах опираюсь на историю спиллов, зафиксированную оным.
2) В gp_toolkit.gp_workfile_entries.optype обнаружен только HashJoin.
В моей реальности, Redistribute Motion ( а в плане он единственный) не может создать спилл больше, чем табл-а, которую он перераспределяет, в которой было 600к строк.
Или я не прав? Гипотеза, что эта таблица весила 24 TB несостоятельна, т.к. она создана за 1 сек. Гипотеза, что редистрибутилась idl.data_mart,
в сжатом виде 144 GB (zstd1) тоже несостоятельна - если взять as is ( т.е. без компрессии) 10% случайных записей из ее 3 млрд строк - это лишь 50 ГБ. Т.е. ее Redistribute мог бы дать 0.5 - 1 TB max.
Если бы в плане был ее Broadcast, то 24 TB бы не отделались, но и время бы было не 2000 сек на запрос.
Т.е. у меня в принципе нет объяснения произошедшему.
3) off
Forwarded from Ринат Галямов
Два вопроса:
1) план точно от запросп приведённого в статье? Можно полный запрос, который падал по спилам?
2) хэш таблицы создаются по умолчанию несжатые. И 114 гб вполне могут в несжатом виде занять > 24 тб
+ редистрибутив - и вылетели за лимиты.
Что покажет
Show gp_workfile_compresson
?
1) план точно от запросп приведённого в статье? Можно полный запрос, который падал по спилам?
2) хэш таблицы создаются по умолчанию несжатые. И 114 гб вполне могут в несжатом виде занять > 24 тб
+ редистрибутив - и вылетели за лимиты.
Что покажет
Show gp_workfile_compresson
?
NOT IN ('1','2') пробрасывается при чтении внешней табл-ы по JDBC через PXF?
Is Filter NOT IN ('1','2') pushed down when reading external Oracle table via PXF?
Is Filter NOT IN ('1','2') pushed down when reading external Oracle table via PXF?
Anonymous Quiz
42%
Y
58%
N
Секрет 21(И снова Разделяй и властвуй)
Секрет уходит корнями в эмуляцию MPP в PostgreSQL 9 на одном из пилотных проектов BigData в АШАН.
Мне поручили рефакторинг старой legacy системы, где французы переливали из Oracle ежедневно 0.5 млрд строк
на FTP, csv c которого загружались в PG. Горе в то, что Выгрузка была неинкрементальна и со временем этот объем только отрастал.
Мы с DevOps-ом настроили FDW для прямой шины Oracle => PG и это уже было в 2-3 раза быстрее, но SLA по готовности данных
для топ-менеджмента к 7 утра все еще выполнен не был. Лирика в прошлом, теперь к сути.
Я задумался, а что если разбить поток данных из Oracle на подмножества, подняв для каждого свой sh-скрипт на стороне PG,
который стягивает свою часть данных. Хорошо помню, что идея оказалась состоятельна, причем после перебора число ниток = 16, на которые распараллеливался поток,
оказалось оптимальным и совпало с числом CPU на сервере.
Сегодня я решил проверить, работает ли тот же подход для связки PXF + Oracle в GP, и первый же тест убедил меня, что да,
идея распараллеливания запроса хард-код фильтрами состоятельна и подход можно брать вооружение.
Не буду грузить вас SQL скриптами в этот раз, донесу лишь суть эксперимента и вывод.
Возьмем внешнюю табл-у X в Oracle с 35 полями И запрос Q с прома, который регулярно выгружает данные за период из X в stg табл-у в GP -
за сегодня получим 15 млн строк в среднем за 5 мин 13 сек.
Найдем в X поле с низкой кардинальностью, скажем code имеющее в нашем случае 15 различных коротких значений вида '00', '19' и др. и составим
гистограмму распределения числа строк по кодам.
Допустим, фильтру F code in ('00', '19') соотв-ет 6 млн строк в сумме, остальному набору 13 кодов - 9 млн строк.
Выполним в параллели 2 запроса Q каждый с своим фильтром F и увидим, что запросы выполнились в среднем за 2 мин 7 сек и 2 мин 50 сек соотв-но,
т.е. итоговое время для 2х параллельных ниток составило 2 мин 50 сек, что дает ускорение на 46%.
Можно и дальше повысить коэффициент распараллеливания, но это тема отдельного исследования.
В принципе, если в таблице нет поля, по которому можно распараллелится, можно на источнике создать вью с вычислимым полем,
которое условно будет бить все множество на нужное нам число примерно равных частей, напр. это может быть код первого символа в rowid Oracle табл-ы
по модулю 10, если мы хотим выгружать внешку в 10 параллельных потоков.
Отмечу, что рез-ты усреднены по двум прогонам, в рамках которых они близки к среднему.
Вывод: Выгрузку данных из внешней таблицы можно существенно ускорить, если распараллелить ее собственными средствами.
Секрет уходит корнями в эмуляцию MPP в PostgreSQL 9 на одном из пилотных проектов BigData в АШАН.
Мне поручили рефакторинг старой legacy системы, где французы переливали из Oracle ежедневно 0.5 млрд строк
на FTP, csv c которого загружались в PG. Горе в то, что Выгрузка была неинкрементальна и со временем этот объем только отрастал.
Мы с DevOps-ом настроили FDW для прямой шины Oracle => PG и это уже было в 2-3 раза быстрее, но SLA по готовности данных
для топ-менеджмента к 7 утра все еще выполнен не был. Лирика в прошлом, теперь к сути.
Я задумался, а что если разбить поток данных из Oracle на подмножества, подняв для каждого свой sh-скрипт на стороне PG,
который стягивает свою часть данных. Хорошо помню, что идея оказалась состоятельна, причем после перебора число ниток = 16, на которые распараллеливался поток,
оказалось оптимальным и совпало с числом CPU на сервере.
Сегодня я решил проверить, работает ли тот же подход для связки PXF + Oracle в GP, и первый же тест убедил меня, что да,
идея распараллеливания запроса хард-код фильтрами состоятельна и подход можно брать вооружение.
Не буду грузить вас SQL скриптами в этот раз, донесу лишь суть эксперимента и вывод.
Возьмем внешнюю табл-у X в Oracle с 35 полями И запрос Q с прома, который регулярно выгружает данные за период из X в stg табл-у в GP -
за сегодня получим 15 млн строк в среднем за 5 мин 13 сек.
Найдем в X поле с низкой кардинальностью, скажем code имеющее в нашем случае 15 различных коротких значений вида '00', '19' и др. и составим
гистограмму распределения числа строк по кодам.
Допустим, фильтру F code in ('00', '19') соотв-ет 6 млн строк в сумме, остальному набору 13 кодов - 9 млн строк.
Выполним в параллели 2 запроса Q каждый с своим фильтром F и увидим, что запросы выполнились в среднем за 2 мин 7 сек и 2 мин 50 сек соотв-но,
т.е. итоговое время для 2х параллельных ниток составило 2 мин 50 сек, что дает ускорение на 46%.
Можно и дальше повысить коэффициент распараллеливания, но это тема отдельного исследования.
В принципе, если в таблице нет поля, по которому можно распараллелится, можно на источнике создать вью с вычислимым полем,
которое условно будет бить все множество на нужное нам число примерно равных частей, напр. это может быть код первого символа в rowid Oracle табл-ы
по модулю 10, если мы хотим выгружать внешку в 10 параллельных потоков.
Отмечу, что рез-ты усреднены по двум прогонам, в рамках которых они близки к среднему.
Вывод: Выгрузку данных из внешней таблицы можно существенно ускорить, если распараллелить ее собственными средствами.
Secret 21 (Divide and Conquer Again)
The secret has its roots in the emulation of MPP in PostgreSQL 9 on one of the BigData pilot projects at Auchan.
I was tasked with refactoring an old legacy system where the French were transferring 0.5 billion rows daily from Oracle
to FTP, csv from which they were loaded into PG. The trouble was that the Unloading was non-incremental and over time this volume only grew.
DevOps and I set up an FDW for a direct Oracle => PG bus and it was already 2-3 times faster, but the SLA for data readiness
for top management by 7 am was still not met. The lyrics are in the past, now to the point.
I wondered, what if we split the data flow from Oracle into subsets, raising for each its own sh-script on the PG side,
which pulls its part of the data. I remember well that the idea turned out to be viable, and after sorting the number of threads = 16, on which the flow was parallelized,
turned out to be optimal and coincided with the number of CPUs on the server.
Today I decided to check whether the same approach works for the PXF + Oracle bundle in GP, and the very first test convinced me that yes,
the idea of parallelizing a query with hard code filters is viable and the approach can be taken up.
I will not burden you with SQL scripts this time, I will only convey the essence of the experiment and the conclusion.
Let's take an external table X in Oracle with 35 fields And a query Q from production flow, which regularly unloads data for a period from X to the stg table in GP -
today we will get 15 million rows on average in 5 minutes 13 seconds.
Let's find a low-cardinality field in X, say code, which in our case has 15 different short values like '00', '19', etc., and create a histogram of the distribution of the number of rows by codes.
Let's assume that the filter F code in ('00', '19') corresponds to 6 million rows in total, and the rest of the set of 13 codes corresponds to 9 million rows.
Let's execute 2 parallel queries Q, each with its own filter F, and see that the queries were executed on average in 2 min 7 sec and 2 min 50 sec, respectively,
i.e. the total time for 2 parallel threads was 2 min 50 sec, which gives an acceleration of 46%.
It is possible to further increase the parallelization coefficient, but this is a topic for a separate study.
In principle, if the table does not have a field that can be parallelized, you can create a view with a computable field on the source,
which will conditionally split the entire set into the number of approximately equal parts we need, for example, this could be the code of the first character in the Oracle table rowid
modulo 10, if we want to unload the external data into 10 parallel streams.
Note that the results are averaged over two runs, within which they are close to the average.
Conclusion: Unloading data from an external table can be significantly accelerated if you parallelize it using your own tools.
The secret has its roots in the emulation of MPP in PostgreSQL 9 on one of the BigData pilot projects at Auchan.
I was tasked with refactoring an old legacy system where the French were transferring 0.5 billion rows daily from Oracle
to FTP, csv from which they were loaded into PG. The trouble was that the Unloading was non-incremental and over time this volume only grew.
DevOps and I set up an FDW for a direct Oracle => PG bus and it was already 2-3 times faster, but the SLA for data readiness
for top management by 7 am was still not met. The lyrics are in the past, now to the point.
I wondered, what if we split the data flow from Oracle into subsets, raising for each its own sh-script on the PG side,
which pulls its part of the data. I remember well that the idea turned out to be viable, and after sorting the number of threads = 16, on which the flow was parallelized,
turned out to be optimal and coincided with the number of CPUs on the server.
Today I decided to check whether the same approach works for the PXF + Oracle bundle in GP, and the very first test convinced me that yes,
the idea of parallelizing a query with hard code filters is viable and the approach can be taken up.
I will not burden you with SQL scripts this time, I will only convey the essence of the experiment and the conclusion.
Let's take an external table X in Oracle with 35 fields And a query Q from production flow, which regularly unloads data for a period from X to the stg table in GP -
today we will get 15 million rows on average in 5 minutes 13 seconds.
Let's find a low-cardinality field in X, say code, which in our case has 15 different short values like '00', '19', etc., and create a histogram of the distribution of the number of rows by codes.
Let's assume that the filter F code in ('00', '19') corresponds to 6 million rows in total, and the rest of the set of 13 codes corresponds to 9 million rows.
Let's execute 2 parallel queries Q, each with its own filter F, and see that the queries were executed on average in 2 min 7 sec and 2 min 50 sec, respectively,
i.e. the total time for 2 parallel threads was 2 min 50 sec, which gives an acceleration of 46%.
It is possible to further increase the parallelization coefficient, but this is a topic for a separate study.
In principle, if the table does not have a field that can be parallelized, you can create a view with a computable field on the source,
which will conditionally split the entire set into the number of approximately equal parts we need, for example, this could be the code of the first character in the Oracle table rowid
modulo 10, if we want to unload the external data into 10 parallel streams.
Note that the results are averaged over two runs, within which they are close to the average.
Conclusion: Unloading data from an external table can be significantly accelerated if you parallelize it using your own tools.
Используете подход для ускорения PXF, описанный в Секрете 21 ?
Are you using the PXF acceleration approach described in Secret 21?
Are you using the PXF acceleration approach described in Secret 21?
Anonymous Poll
48%
Y
30%
N
22%
У нас другой метод __We use different approach__