SQLチューニング(パーティションワイズ集約)

hashパーティション
 パーティション数=10
1000万件のテーブルを作成
個別キー数 = 100件でグループ化

MySQL

(8.0.29)

drop table tab1;
create table tab1(
col1 int
,col2 int
)
partition by hash(col1)
(partition p00,
partition p01,
partition p02,
partition p03,
partition p04,
partition p05,
partition p06,
partition p07,
partition p08,
partition p09)
;

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i int;

set i = 1;
start transaction;
while i <= 10000000 do
insert into tab1 select floor(rand() * 10000000)+1,mod(floor(rand() * 10000000)+1,100);
set i = i + 1;
end while;
commit;

end
//
delimiter ;

call proc1();

select count(*) from tab1;
select count(*) from tab1 partition(p00);
select count(*) from tab1 partition(p01);
select count(*) from tab1 partition(p02);
select count(*) from tab1 partition(p03);
select count(*) from tab1 partition(p04);
select count(*) from tab1 partition(p05);
select count(*) from tab1 partition(p06);
select count(*) from tab1 partition(p07);
select count(*) from tab1 partition(p08);
select count(*) from tab1 partition(p09);

analyze table tab1;

explain format=tree
select col2,count(*) from tab1
group by col2
order by col2
;

explain format=tree
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1 partition(p00) group by col2
union all
select col2,count(*) cnt from tab1 partition(p01) group by col2
union all
select col2,count(*) cnt from tab1 partition(p02) group by col2
union all
select col2,count(*) cnt from tab1 partition(p03) group by col2
union all
select col2,count(*) cnt from tab1 partition(p04) group by col2
union all
select col2,count(*) cnt from tab1 partition(p05) group by col2
union all
select col2,count(*) cnt from tab1 partition(p06) group by col2
union all
select col2,count(*) cnt from tab1 partition(p07) group by col2
union all
select col2,count(*) cnt from tab1 partition(p08) group by col2
union all
select col2,count(*) cnt from tab1 partition(p09) group by col2
) A
group by A.col2
order by A.col2
;

-- 結果

パーティションテーブルの場合
5.97 sec

パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
6.06 sec

非パーティションテーブルの場合
5.92 sec

SQL書き換えでパーティション毎に集約しても効果なし

Oracle

(19c)

drop table tab1 purge;

create table tab1(
col1 int
,col2 int
)
partition by hash(col1)
( partition p00
, partition p01
, partition p02
, partition p03
, partition p04
, partition p05
, partition p06
, partition p07
, partition p08
, partition p09
);

declare
begin
for i in 1..10000000 loop
insert into tab1 select floor(dbms_random.value(1, 10000001) ), mod( floor(dbms_random.value(1, 10000001) ),100) from dual;
end loop;
end;
/

commit;

set time on
set timing on

exec dbms_stats.gather_table_stats(user, 'TAB1');

explain plan for
select col2,count(*) from tab1
group by col2
order by col2
;

select * from table(dbms_xplan.display(format=>'ALL') );

explain plan for
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1 partition(p00) group by col2
union all
select col2,count(*) cnt from tab1 partition(p01) group by col2
union all
select col2,count(*) cnt from tab1 partition(p02) group by col2
union all
select col2,count(*) cnt from tab1 partition(p03) group by col2
union all
select col2,count(*) cnt from tab1 partition(p04) group by col2
union all
select col2,count(*) cnt from tab1 partition(p05) group by col2
union all
select col2,count(*) cnt from tab1 partition(p06) group by col2
union all
select col2,count(*) cnt from tab1 partition(p07) group by col2
union all
select col2,count(*) cnt from tab1 partition(p08) group by col2
union all
select col2,count(*) cnt from tab1 partition(p09) group by col2
) A
group by A.col2
order by A.col2
;

-- 結果

パーティションテーブルの場合
経過: 00:00:01.50

パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
経過: 00:00:00.51

非パーティションテーブルの場合
経過: 00:00:01.55

SQL書き換えでパーティション毎に集約の効果はある

PostgreSQL

(14)
https://tech-lab.sios.jp/archives/11829

drop table tab1 CASCADE;

create table tab1(
col1 int
,col2 int
)
partition by hash(col1);

create table tab1p00 partition of tab1 for values with (modulus 10,remainder 0);
create table tab1p01 partition of tab1 for values with (modulus 10,remainder 1);
create table tab1p02 partition of tab1 for values with (modulus 10,remainder 2);
create table tab1p03 partition of tab1 for values with (modulus 10,remainder 3);
create table tab1p04 partition of tab1 for values with (modulus 10,remainder 4);
create table tab1p05 partition of tab1 for values with (modulus 10,remainder 5);
create table tab1p06 partition of tab1 for values with (modulus 10,remainder 6);
create table tab1p07 partition of tab1 for values with (modulus 10,remainder 7);
create table tab1p08 partition of tab1 for values with (modulus 10,remainder 8);
create table tab1p09 partition of tab1 for values with (modulus 10,remainder 9);

\pset pager 0
\d+ tab1

insert into tab1
select floor(random() * 10000000)+1
, mod( (floor(random() * 10000000)+1)::int,100)
from generate_series(1,10000000)
;

select count(*) from tab1;
select count(*) from tab1p00;

analyze tab1;

explain analyze
select col2,count(*) from tab1
group by col2
order by col2
;

set enable_partitionwise_aggregate = on;
set enable_partitionwise_aggregate = off;
show enable_partitionwise_aggregate;

explain analyze
select A.col2,sum(A.cnt) from
(
select col2,count(*) cnt from tab1p00 group by col2
union all
select col2,count(*) cnt from tab1p01 group by col2
union all
select col2,count(*) cnt from tab1p02 group by col2
union all
select col2,count(*) cnt from tab1p03 group by col2
union all
select col2,count(*) cnt from tab1p04 group by col2
union all
select col2,count(*) cnt from tab1p05 group by col2
union all
select col2,count(*) cnt from tab1p06 group by col2
union all
select col2,count(*) cnt from tab1p07 group by col2
union all
select col2,count(*) cnt from tab1p08 group by col2
union all
select col2,count(*) cnt from tab1p09 group by col2
) A
group by A.col2
order by A.col2
;

-- 結果

パーティションテーブルで、enable_partitionwise_aggregate がoffの場合
Planning Time: 0.112 ms
Execution Time: 2743.284 ms

パーティションテーブルで、enable_partitionwise_aggregate がonの場合
Planning Time: 0.174 ms
Execution Time: 2137.928 ms

パーティションテーブルで、SQLレベルでパーティション毎に集約した場合
Planning Time: 0.213 ms
Execution Time: 2056.535 ms

非パーティションテーブルの場合
Planning Time: 0.051 ms
Execution Time: 2075.723 ms

パーティションワイズ集約の効果はすこしある。
また、SQL書き換えでパーティション毎に集約した場合とenable_partitionwise_aggregateをonにした場合は同等

非パーティションテーブルはパーティションワイズ集約後と同等の処理時間

SQL Server

(2019)

特定パーティションのみのデータ取得方法がない模様のため、検証不可