count文の実行計画

MySQL、Oracle、SQL Server ->インデックスがあればフルスキャンされる
PostgreSQL -> 常にテーブルのフルスキャン

• MySQL

(8.0.29)

drop table tab1 ;
create table tab1(col1 int not null,col2 int, col3 int);

drop procedure proc1;

delimiter //
create procedure proc1(in x bigint)
begin
  declare i bigint;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1(col1,col2,col3) select ceil(rand()*100000),ceil(rand()*100000),ceil(rand()*100000);
  end while;
  commit;
end
//
delimiter ;

call proc1(10000);

select count(*) from tab1;
select * from tab1 limit 10;

create  index ind11 on tab1(col1);
create  index ind12 on tab1(col2);

analyze table tab1;

explain  select count(*) from tab1;
→ index ind11

explain  select count(1) from tab1;
→ index ind11

explain  select count(col1) from tab1;
→ index ind11

explain  select count(col2) from tab1;
→ index ind12

explain  select count(col3) from tab1;
→ ALL

インデックスがあれば使用される

• Oracle

(19c)

drop table tab1 purge;
create table tab1(col1 int not null,col2 int,col3 int);

declare
begin
for i in 1..10000 loop
  insert into tab1(col1,col2,col3) values(
      ceil(dbms_random.value()*100000)
    , ceil(dbms_random.value()*100000)
    , ceil(dbms_random.value()*100000)
    );
end loop;
end;
/

commit;

select count(*) from tab1;
select * from tab1 fetch first 10 rows only;

create index ind11 on tab1(col1);
create index ind12 on tab1(col2);

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

explain plan for select count(*) from tab1;
select * from table(dbms_xplan.display(format => 'ALL') );
→ INDEX FAST FULL SCAN  IND11

explain plan for select count(1) from tab1;
select * from table(dbms_xplan.display(format => 'ALL') );
→ INDEX FAST FULL SCAN  IND11

explain plan for select count(col1) from tab1;
select * from table(dbms_xplan.display(format => 'ALL') );
→ INDEX FAST FULL SCAN  IND11

explain plan for select count(col2) from tab1;
select * from table(dbms_xplan.display(format => 'ALL') );
→ INDEX FAST FULL SCAN  IND12

explain plan for select count(col3) from tab1;
select * from table(dbms_xplan.display(format => 'ALL') );
→ TABLE ACCESS FULL

インデックスがあれば使用される

• PostgreSQL

(14)

drop table tab1 ;
create table tab1(col1 int not null ,col2 int, col3 int);

insert into tab1(col1,col2,col3) select 
     ceil(random()*100000)
   , ceil(random()*100000)
   , ceil(random()*100000)
   from generate_series(1,10000) g;

select count(*) from tab1;
select * from tab1 limit 10;

create index ind11 on tab1(col1);
create index ind12 on tab1(col2);

analyze tab1;

explain analyze select count(*) from tab1;
→ Seq Scan on tab1

explain analyze select count(1) from tab1;
→ Seq Scan on tab1

explain analyze select count(col1) from tab1;
→ Seq Scan on tab1

explain analyze select count(col2) from tab1;
→ Seq Scan on tab1

explain analyze select count(col3) from tab1;
→ Seq Scan on tab1

インデックスは使用されない。可視性チェックが必要のためと思われる。

• SQL Server

(2019)

drop table tab1 ;
create table tab1(col1 int not null ,col2 int ,col3 int);

set nocount on
declare @i bigint;
set @i = 1;
begin transaction;
while @i <= 10000
begin
  insert into tab1(col1,col2,col3) select ceiling(rand()*100000),ceiling(rand()*100000),ceiling(rand()*100000);
  set @i = @i + 1;
end
commit;

select count(*) from tab1;
select top(10) * from tab1 ;

create index ind11 on tab1(col1);
create index ind12 on tab1(col2);

update statistics tab1;

set showplan_all on
go

select count(*) from tab1;
→ Index Scan ind12

select count(1) from tab1;
→ Index Scan ind12

select count(col1) from tab1;
→ Index Scan ind12

select count(col2) from tab1;
→ Index Scan ind12

select count(col3) from tab1;
→ Table Scan

インデックスがあれば使用される
見積コストに相違はないが、ind12が使用される。