(5.6)
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int,col12 int);
create table tab2(col21 int,col22 int);
alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 10000000 do
insert into tab1 values(i,i);
set i = i+1;
end while;
end
//
delimiter ;
call proc1();
insert into tab2 select col11,floor(rand() * 10000000)+1 from tab1;
analyze table tab1;
analyze table tab2;
--処理時間確認
select count(*) from (
select col22,sum(col21)
from tab2
group by col22
order by col22
) t
;
set @@session.join_buffer_size=65536;
set @@session.sort_buffer_size=65536;
(2 min 25.74 sec)
set @@session.join_buffer_size=409600;
set @@session.sort_buffer_size=409600;
(2 min 19.32 sec)
set @@session.join_buffer_size=4096000;
set @@session.sort_buffer_size=4096000;
(1 min 51.04 sec)
set @@session.join_buffer_size=40960000;
set @@session.sort_buffer_size=40960000;
(1 min 53.01 sec)
select count(*)
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
;
set @@session.join_buffer_size=65536;
set @@session.sort_buffer_size=65536;
(49.93 sec)
set @@session.join_buffer_size=409600;
set @@session.sort_buffer_size=409600;
(48.71 sec)
set @@session.join_buffer_size=4096000;
set @@session.sort_buffer_size=4096000;
(46.96 sec)
set @@session.join_buffer_size=40960000;
set @@session.sort_buffer_size=40960000;
(46.66 sec)
(12cR1)
--事前準備
show parameter memory_target;
show parameter pga_aggregate_target;
alter system set memory_target = 0 scope=spfile;
alter system set pga_aggregate_target = 0 scope=spfile;
show parameter workarea_size_policy;
show parameter hash_area_size;
show parameter sort_area_size;
--データ準備
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col11 int,col12 int);
create table tab2(col21 int,col22 int);
create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21);
alter table tab1 add constraint cons1 primary key(col11) using index ind11;
alter table tab2 add constraint cons2 primary key(col21) using index ind21;
declare
type t_tab1 is table of tab1%rowtype index by pls_integer;
type t_tab2 is table of tab2%rowtype index by pls_integer;
r_tab1 t_tab1;
r_tab2 t_tab2;
begin
for i in 1..10000000 loop
r_tab1(i).col11 := i;
r_tab1(i).col12 := i;
end loop;
forall i in 1..10000000
insert into tab1 values r_tab1(i);
commit;
end;
/
insert into tab2 select col11,floor(dbms_random.value(1, 10000001)) from tab1;
commit;
exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');
--処理時間確認
set time on
set timing on
select count(*) from (
select col22,sum(col21)
from tab2
group by col22
order by col22
)
;
alter session set hash_area_size = 65536;
alter session set sort_area_size = 65536;
経過: 00:00:27.79
alter session set hash_area_size = 409600;
alter session set sort_area_size = 409600;
経過: 00:00:19.84
alter session set hash_area_size = 4096000;
alter session set sort_area_size = 4096000;
経過: 00:00:08.42
alter session set hash_area_size = 40960000;
alter session set sort_area_size = 40960000;
経過: 00:00:05.58
select count(*)
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
;
alter session set hash_area_size = 65536;
alter session set sort_area_size = 65536;
経過: 00:00:21.28
alter session set hash_area_size = 409600;
alter session set sort_area_size = 409600;
経過: 00:00:10.18
alter session set hash_area_size = 4096000;
alter session set sort_area_size = 4096000;
経過: 00:00:06.28
alter session set hash_area_size = 40960000;
alter session set sort_area_size = 40960000;
経過: 00:00:06.19
(11)
--事前準備
vim postgresql.conf
log_temp_files = 0
sudo systemctl restart postgresql-11
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int,col12 int);
create table tab2(col21 int,col22 int);
create unique index ind11 on tab1(col11);
create unique index ind21 on tab2(col21);
alter table tab1 add constraint cons1 primary key using index ind11;
alter table tab2 add constraint cons2 primary key using index ind21;
insert into tab1 select a,a from generate_series(1,10000000) a;
insert into tab2 select a,floor(random() * 10000000)+1 from generate_series(1,10000000) a;
analyze tab1;
analyze tab2;
--処理時間確認
explain analyze
select col22,sum(col21)
from tab2
group by col22
order by col22
;
set work_mem = '64kB';
Execution Time: 15183.393 ms
set work_mem = '400kB';
Execution Time: 13160.852 ms
set work_mem = '4MB';
Execution Time: 10301.267 ms
set work_mem = '40MB';
Execution Time: 9014.432 ms
explain analyze
select count(*)
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
;
set work_mem = '64kB';
Execution Time: 29995.148 ms
set work_mem = '400kB';
Execution Time: 16802.863 ms
set work_mem = '4MB';
Execution Time: 11868.302 ms
set work_mem = '40MB';
Execution Time: 12154.738 ms
(2014)
--事前準備
exec sp_configure 'show advanced options', 1;
go
reconfigure ;
go
--データ準備
drop table tab1;
drop table tab2;
create table tab1(col11 int not null,col12 int);
create table tab2(col21 int not null,col22 int);
alter table tab1 add constraint cons1 primary key(col11);
alter table tab2 add constraint cons2 primary key(col21);
drop procedure proc1;
create procedure proc1
as
begin
declare @i integer;
set @i = 1;
while @i <= 10000000
begin
insert into tab1 values(@i,@i);
set @i = @i + 1;
end
end
;
exec proc1;
insert into tab2 select col11,floor(rand() * 10000000)+1 from tab1;
update statistics tab1;
update statistics tab2;
--処理時間確認
set statistics time on;
go
select col22,sum(cast(col21 as bigint) )
from tab2
group by col22
;
exec sp_configure 'min memory per query', 512;
go
reconfigure;
go
経過時間 = 589 ミリ秒
exec sp_configure 'min memory per query', 4000;
go
reconfigure;
go
経過時間 = 554 ミリ秒
exec sp_configure 'min memory per query', 40000;
go
reconfigure;
go
経過時間 = 560 ミリ秒
select count(*)
from tab1 t1 inner join tab2 t2
on t1.col11 = t2.col21
;
exec sp_configure 'min memory per query', 512;
go
reconfigure;
go
経過時間 = 2269 ミリ秒
exec sp_configure 'min memory per query', 4000;
go
reconfigure;
go
経過時間 = 2239 ミリ秒
exec sp_configure 'min memory per query', 40000;
go
reconfigure;
go
経過時間 = 2266 ミリ秒
