統計情報がない時の実行計画

まとめ
MySQLは動的に統計情報を取得している模様
PostgreSQLはおおよその件数程度は動的に取得している模様
Oracleは一貫性のない見積値を使用している模様

MySQL

(8.0.26)

drop table tab1;

create table tab1(col1 int primary key,col2 int,col3 int);

create index ind1 on tab1(col2);

drop procedure proc1;

delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(i,i,i);
end while;
commit;
end
//
delimiter ;

call proc1(100000);

select count(*) from tab1;

select * from mysql.innodb_table_stats where table_name = 'tab1';
select * from mysql.innodb_index_stats where table_name = 'tab1';

-- 永続的オプティマイザ統計を削除
delete from mysql.innodb_table_stats where table_name = 'tab1';
delete from mysql.innodb_index_stats where table_name = 'tab1';

show variables like 'innodb_stats%';

-- 更新時の統計再取得無効化
alter table tab1 STATS_AUTO_RECALC=0;

explain
select * from tab1;

explain
select * from tab1 where col1 = 123;

explain
select * from tab1 where col2 = 123;

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tab1 | NULL | ref | ind1 | ind1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
※トランケートしても変化なし

explain
select * from tab1 where col3 = 123;

統計情報がない場合、動的に統計情報を取得している模様

Oracle

(19c)

drop table tab1 purge;

create table tab1(col1 int primary key,col2 int,col3 int);

create index ind1 on tab1(col2);

declare
begin
for i in 1..100000 loop
insert into tab1 values(i,i,i);
commit;
end loop;
end;
/

select count(*) from tab1;

select * from user_tab_statistics where table_name in ('TAB1');
select * from user_ind_statistics where table_name in ('TAB1');

-- インデックス統計削除
exec dbms_stats.delete_index_stats(user,'IND1');

show parameter optimizer_dynamic_sampling

alter session set optimizer_dynamic_sampling = 0;

explain plan for
select * from tab1;
select * from table(dbms_xplan.display() );

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30222 | 1151K| 102 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TAB1 | 30222 | 1151K| 102 (0)| 00:00:01 |
--------------------------------------------------------------------------
↓トランケート後
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 3198 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TAB1 | 82 | 3198 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
※再度10万件ロードしても見積件数は変化しない

explain plan for
select * from tab1 where col1 = 123;
select * from table(dbms_xplan.display() );

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C008464 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
※一意制約のため、正しい見積件数になっている

explain plan for
select * from tab1 where col2 = 123;
select * from table(dbms_xplan.display() );

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 302 | 11778 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 302 | 11778 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND1 | 121 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
↓トランケート後
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
※再度10万件ロードしても見積件数は変化しない

explain plan for
select * from tab1 where col3 = 123;
select * from table(dbms_xplan.display() );

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 302 | 11778 | 102 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB1 | 302 | 11778 | 102 (0)| 00:00:01 |
--------------------------------------------------------------------------
↓トランケート後
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB1 | 1 | 39 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
※再度10万件ロードしても見積件数は変化しない

統計情報がなく、動的統計も無効の場合の見積もり件数は根拠不明で一貫性がない

PostgreSQL

(14)

vi postgresql.conf
track_counts = off

show track_counts;

drop table tab1;

create table tab1(col1 int primary key,col2 int,col3 int);

create index ind1 on tab1(col2);

insert into tab1 select g,g,g from generate_series(1,100000) g;

select count(*) from tab1;

select * from pg_class where relname = 'tab1';
select * from pg_statistic where starelid = 'tab1'::regclass::oid;

explain
select * from tab1;

Seq Scan on tab1 (cost=0.00..1644.64 rows=110364 width=12)
↓トランケート後
Seq Scan on tab1 (cost=0.00..30.40 rows=2040 width=12)
※再度10万件ロードすると見積件数は元に戻る

explain
select * from tab1 where col1 = 123;

Index Scan using tab1_pkey on tab1 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (col1 = 123)
※一意制約のため、正しい見積件数になっている

explain
select * from tab1 where col2 = 123;

Bitmap Heap Scan on tab1 (cost=12.57..580.35 rows=552 width=12)
Recheck Cond: (col2 = 123)
-> Bitmap Index Scan on ind1 (cost=0.00..12.43 rows=552 width=0)
Index Cond: (col2 = 123)
↓トランケート後
Bitmap Heap Scan on tab1 (cost=4.23..14.79 rows=10 width=12)
Recheck Cond: (col2 = 123)
-> Bitmap Index Scan on ind1 (cost=0.00..4.23 rows=10 width=0)
Index Cond: (col2 = 123)
※再度10万件ロードすると見積件数は元に戻る

explain
select * from tab1 where col3 = 123;

Seq Scan on tab1 (cost=0.00..1920.55 rows=552 width=12)
Filter: (col3 = 123)
↓トランケート後
Seq Scan on tab1 (cost=0.00..35.50 rows=10 width=12)
Filter: (col3 = 123)
※再度10万件ロードすると見積件数は元に戻る

統計情報がない場合、おおよその件数程度は動的に取得している模様。

SQL Server

(2019)

delete from sys.stats where object_name(object_id) = 'tab1'
go
delete from sys.stats_columns where object_name(object_id) = 'tab1'
go

メッセージ 259、レベル 16、状態 1、行 10
システムカタログへのアドホック更新は許可されません。
メッセージ 259、レベル 16、状態 1、行 12
システムカタログへのアドホック更新は許可されません。

インデックスの統計を DROP STATISTICS で削除することはできません。インデックスが存在する限り、統計は維持されます。

統計情報がない状態が作れず、検証不可