makoto_blog

パパさん_blog

プログラミング勉強メモや雑記など。

SQL 実行計画の読み方

環境

準備

ユーザーとデータベース作成

postgresユーザーにスイッチ

$ sudo -u postgres -i

ユーザー作成

$ createuser -d -U postgres -P makoto

DB作成

$ createdb makotodb --encoding=UTF-8 --lc-collate=C --lc-ctype=C --owner=makoto --template=template0

以降は以下のコマンドで接続

psql -U makoto -h localhost -d makotodb

テーブル作成

create table shops (
  shop_id    char(5) not null,
  shop_name  varchar(64),
  area       varchar(64),
   constraint pk_shops primary key (shop_id));

create table items (
  item_id       char(5)  not null, 
  shop_id       char(5) not null,
  item_name     char(32) not null, 
  price         integer  not null, 
  primary key (item_id, shop_id));

データ投入

insert into shops values('10000', 'オレンジストア', '東京');
insert into shops values('20000', 'コンビニ作之助', '埼玉');

insert into items values('10001', '10000', 'pc', 40000);
insert into items values('10002', '10000', 'モニタ', 12000);
insert into items values('10003', '10000', 'マウス', 5000);

insert into items values('20001', '20000', 'ジュース', 120);
insert into items values('20002', '20000', 'あんまん', 100);
insert into items values('20003', '20000', 'チョコ', 150);

こんな感じになります。

makotodb=> select * from shops;
 shop_id |   shop_name    | area 
---------+----------------+------
 10000   | オレンジストア | 東京
 20000   | コンビニ作之助 | 埼玉
(2 rows)

makotodb=> select * from items;
 item_id | shop_id |              item_name               | price 
---------+---------+--------------------------------------+-------
 10001   | 10000   | pc                                   | 40000
 10002   | 10000   | モニタ                               | 12000
 10003   | 10000   | マウス                               |  5000
 20001   | 20000   | ジュース                             |   120
 20002   | 20000   | あんまん                             |   100
 20003   | 20000   | チョコ                               |   150
(6 rows)

実行計画確認

統計情報

スキーマ確認

makotodb=> select current_schema;
 current_schema 
----------------
 public
(1 row)

統計情報更新

makotodb=> analyze;

或いはスキーマとテーブルを指定

analyze public.shops;

統計情報がいつ更新されたか確認

select relname
     , n_live_tup
     , n_dead_tup
     , last_autovacuum
     , last_analyze
     , last_autoanalyze
  from pg_stat_all_tables
 where schemaname = 'public' -- ここにスキーマ
 order by
       relname
;

以下にて確認

 relname | n_live_tup | n_dead_tup | last_autovacuum |         last_analyze          | last_autoanalyze 
---------+------------+------------+-----------------+-------------------------------+------------------
 items   |          6 |          0 |                 | 2018-11-11 15:02:05.138966+09 | 
 shops   |          2 |          0 |                 | 2018-11-11 15:02:05.122272+09 | 
(2 rows)

実行計画確認

フルスキャン

makotodb=> explain select * from shops;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on shops  (cost=0.00..1.02 rows=2 width=35)
(1 row)

以下の結合ではHashとなる。Nested Loops が見たかったのですが。

makotodb=> select s.shop_name
makotodb->      , i.item_name
makotodb->   from shops s
makotodb->  inner join
makotodb->        items i
makotodb->     on s.shop_id = i.shop_id;
   shop_name    |              item_name               
----------------+--------------------------------------
 オレンジストア | pc                              
 オレンジストア | モニタ                             
 オレンジストア | マウス                             
 コンビニ作之助 | ジュース                            
 コンビニ作之助 | あんまん                            
 コンビニ作之助 | チョコ                             
(6 rows)

実行計画

makotodb=> explain select s.shop_name
makotodb->      , i.item_name
makotodb->   from shops s
makotodb->  inner join
makotodb->        items i
makotodb->     on s.shop_id = i.shop_id;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=1.04..2.15 rows=6 width=60)
   Hash Cond: (i.shop_id = s.shop_id)
   ->  Seq Scan on items i  (cost=0.00..1.06 rows=6 width=44)
   ->  Hash  (cost=1.02..1.02 rows=2 width=28)
         ->  Seq Scan on shops s  (cost=0.00..1.02 rows=2 width=28)
(5 rows)

実行計画は以下の順に処理される事をあらわしています。

  • shops をフルスキャン

    • ネストの深いところから先に実行
    • 小さい方(rows=2)からHashを作る。
  • Hash を作る。

  • items をフルスキャン し、shop_id でHash Join

この例では無いですが、ツリー表示で同じインデントの階層の上に位置するテーブルが先に実行される。
また、Nested Loops の場合は、内部表に結合キーで一意になるようにする。駆動表が小さく。内部表が大きいと効率が良い。内部表に、結合キーで一意にアクセスできるなら、内部表が何億件レコードであろうと一発で返せるからです。

do read tableA -- 駆動表

  do read tableB -- 内部表

    ここで一発で内部表のループが終わる。内部表がどんなに大きくても。

  end do

end do