Postgres SQLのチューニング
SQLの実行の流れ
パーサ
SQLの文法をチェック
間違っていればerrorとして返す
オプティマイザ
統計情報を元にクエリの実行計画を立ててくれる
カタログマネージャー：
統計情報を要求され、オプティマイザにそれを渡す
テーブル
オプティマイザによる実行計画を元に、テーブルにアクセスする
用語
パーサ
構文解析を行うプログラムの総称
オプティマイザ
最適化を図るための機能やソフトウェアの総称
統計情報
表やインデックス、使用している領域、データの種類、データの分布等のデータ特性を表す情報
レコード数、サイズ、nullの数など
実行計画
どのようにテーブルに対して検索を行なうかが書かれた、SQLの実行手順書
カタログマネージャー
オプティマイザが実行計画を作成するうえでオプティマイザに重要な情報を提供する
indexの種類について
線形探索
テーブルの中身全てを参照していくイメージ
算量は、数列の長さがNの時に、最悪N回の探索が必要になる。これをO(N)
二分探索木
検索する範囲を1/2に縮めながら探索をしていき、最悪の場合、探索範囲が1になるまで検索すること
入力される数列の長さN」を「2で割り続けて1になった時の割った回数」が「探索回数(t)」
O(log N)
B-Treeインデックス←DBMSでは主にこれが使用されている
B-Treeは1つのノードがm個(m>=2)の子ノードを保持することができる
「入力される数列の長さN」を「mで割り続けて1になった時の割った回数」が「探索回数(t)」
t=log2N / log2m
ビットマップインデックス
データからビッドデータを作成してそれをインデックスとして使用
ビットデータのサイズが小さいなどの利点もあるが、インデックス更新するたびに、全てのビットデータを更新する
ハッシュインデックス
ハッシュ値をインデックスとして持つ
「=」で比較する際は一発で見つけてくれるが、それ以外は線形探索
実行計画の読み方(SELECT * FROM deals;)
Explain
実際にクエリは実行せず、使用するクエリの実行計画を表示
EXPLAIN SELECT * FROM deals;
QUERY PLAN
XN Seq Scan on deals (cost=0.00..2449230.72 rows=244923072 width=219)
見方
cost : オプティマイザが様々なプランの中から最適なプランを選ぶための指標
あくまで目安で、算出方法は雑らしい
...の左側が1行目の結果を表示するまでのコスト
...の右側が最後の行までを抽出するコスト
こっちのほうが大事
右側が12桁くらいで2時間ほどかかるかも？
Explain Analyze(*dealsテーブルに対して実際に実行はしてません)
実際にクエリを実行するため、処理時間なども取得可能
EXPLAIN ANALYZE SELECT * FROM deals;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on badge_list (cost=0.00..1708.22 rows=244923072 width=219) (actual time=0.126..34.021 rows=24322 loops=1)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Planning time: 0.065 ms
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------
QUERY PLAN | Execution time: 49.411 ms
見方
actual time：実際にかかった処理時間
rows：実行結果として、実際に戻ってきた行数
loops：ステップの実行回数
Planning time：解析されたクエリから実行計画を生成
し、最適化するのに要した時間
Execution time：実行時間
実行計画に書かれる演算子
分類
演算子
説明
表スキャン
Seq Scan
indexを使わずに上から順番にスキャンしていく
Index Scan
indexを使用したスキャン
Index Only Scan
インデックスのみを利用してデータを読み取るスキャン。テーブルアクセスしない
結合
Nested Loops
片方のテーブルから結果を取り出し、その結果をもう1つのテーブルの各行に対して問い合わせて、2つのテーブルを結合。データ量が少ない場合に良い
Hash Join / Hash
ハッシュ値同士で結合。メモリーの空き容量が大きい場合に良い
Merge Join
並び替えられたデータ同士の結合。データ量が多い場合に良い
ソートとグルーピング
Sort / Sort key
順番の並び替え
--
GroupAggregate
集約関数にGroup by
--
HashAggregate
レコードをグルーピングするのに一時的なハッシュテーブルを使用。
ソートキーを使用する
ソートキー
使用効果
設定することで、テーブルスキャン時に対象外のテーブル領域にアクセスをしなくなる
使用対象
日付などの増加する型が一般的に対象となる
付与方法
create table cust_sales_date_single
sortkey (c_custkey)
as
select * from cust_sales_date;
注意
頻繁にジョインの対象となる場合、該当カラムをソートキー及び複合ソートキーに設定
複合ソートキー
all
key
even
s
create table cust_sales_date_compound
compound sortkey (c_custkey, c_region, c_mktsegment, d_date)
as
select * from cust_sales_date;
注意
複合インデックスを(company_id, created_date )に設定をして、「company_id」のみをWHERE句に指定するとインデックスが無効になる
インターリーブソートキー
あ
create table cust_sales_date_interleaved
interleaved sortkey (c_custkey, c_region, c_mktsegment, d_date)
as
select * from cust_sales_date;
注意
ID 列、日付、タイムスタンプなど、一定間隔で増加する属性を持つ列では使用しない
ディストキーを使用する
使用効果
ノードやスライス間で均一にデータを配分することで、ジョイン時にデータ転送を極小化する
使用対象
company_idなど
指定するカラムの値が出来るだけ均一に分散していること
ジョイン時に結合キーとしてしようされる
where句に指定するものは適していない
付与方法
create table cust_sales_date_compound
distkey (c_custkey,)
as
select * from cust_sales_date;
注意
テーブルに対して1つしか設定出来ない
圧縮を使用する
使用効果
データ圧縮により、1回で読み込めるデータ件数を増やす
種類
種類
意味
RAW
非圧縮
BYTEDICT
反復される値にディクショナリを使用
DELTA/DELTA32K
連続する値(date)などを最適化
MOSTLY8,MOSTLY16,
MOSTLY32
大半の値が低い範囲のビットの桁に集中している場合に
最適化
RUNLENGTH
同一の値が連続する場合に最適化
TEXT255/TEXT32K
テキスト内の単語にディクショナリを使用
付与方法
analyze compression table名;
最適な圧縮アルゴリズムを表示
注意