今回も行ってきました。

（※ 時間が立っていますが、書き始めたのは翌日なので…）

Explainとかやっぱり普段意識していない箇所の理解を深めるって面白いですね。

目次と概要は参照してもらえればいいかなって思うので割愛。

とりあえずメモさらします。

Explain

一回、二回の復習

• オブティマイザは最適な実行計画をつくるもの
  • RDBMSは正規かして使うもの
  • どのようなアクセス方法が適切か
  • どのような結合方法が適切か
  • 統計情報をもとに実行計画を作成

• 主なアクセス方法
  • seq scan
  • index scan
    • index scanの方が4倍コストがかかる
    • SSDとかは関係無い？

• 主な結合
  • nested loop join
    • 総当たりの結合
    • 初回コストは発生しない
    • テーブルが小さければ問題ないけど大きいとコストが膨らむ

• sort maerge join
  • 一度ソートしてJOINする
  • ソートするので初回コストは発生
  • INDEXがない列がキーの場合はコストが膨らむ

• hash JOIN
  • メモリ上にハッシュ表を作成
  • ハッシュ表を作成するので初回コストは発生
  • ハッシュ表はメモリ上に展開されているので高速

• explain analyze
  • explain analyzeは実際にsqlを実行する
    • UPDATEも実際に流すので気をつける
    • explain のみであれば統計情報のみ

• cost ... 統計情報からの見積もり情報
  • actual ... 実際の実行した時間

• explain の見方のこつ
  • インデントが一番深いものから確認する
  • コストがかかっているものからやっつける

実際のデバック例

その1

• Tips:部分インデックス
  • 値に偏りが有る場合に有効
  • INDEXの更新が少なくなる
  • CREATE INDEX に WHERE句を追加
    • 部分的にしか貼らないのでINDEXのサイズが小さくなる

CREATE INDEX active_exceptions ON exception(complete) WHERE complete IS false;

• ANALYZEを使うことにより、統計情報が刷新される

その2

• EXPLAIN の rowが妙に切りがいい数値を疑う
  • 統計情報を持っていない場合は、デフォルトで1000行になっているそう。
  • 但し8.0系からは固定値じゃなくなったらしい

• とにかくAnalyze
• 新しいバージョンを使う

その3

• 追記型MVCCとは
  • postgresはレコードをUPDATEすると元のデータは削除して新しいレコードを追加する仕様
  • SELECTした場合はその削除したレコードは表示しないが一度参照してしまう
  • 従って不要領域を回収するVACUUMが必要

• 9.0からVACUUMの仕様がかわった
  • 9.0以前
    • 不要データ削除→データを再配置→不要領域をつめる
  • 9.0以降
    • 必要なデータを別テーブルに移動→不要なデータが残っていた領域を削除
  • VACUUMFULLを途中でキャンセルすると１からやり直し
  • 一時的に２倍のサイズがかかる

その4

• より早いSQLがないか検討する
• 新しいバージョンを使おう

まとめ

• とりあえずVACUUM　Analyze
• 2回以上実行（キャッシュされてる場合がある）
• 不正確な行数の推定を探す
• Postgresのバージョンアップ

余談：ディスクフルになってしまった場合

• データが壊れることはない
  • ディスクフル二なりそうな場合、基本元の状態前の状態に戻る
• WALについては…
  • ディスクフルの状態ではWALに書き込んでしまうとまずいので、プロセスが落ちる可能性がある
  • パーティションのレベルでわけたほうがいい（と、いわれている）

Text Search

そもそも

• 形態素解析 ... 単語ベースに分割
• n-gram ... 文字単位の分割

• 「Postgresはデータベースです」という文章の場合
  • 形態素解析 ...「Postgres」は「データベース」です
  • 3-gram ... 「Pos」...

• 当然N-gramは単語数が多いのでインデックスは大きくなる

使い分けのポイント

• 形態素解析
  • 構文が整ったもの
    • マニュアル等
  • 多機能
    • ランキング、揺らぎ検索
  • 英語文書 ... ステミングあり
• n-gram
  • カタカナ、連語、造語が多い
  • 記号の検索
  • LIKEを使うアプリが既に実装されている

強み／弱み

pg_trgm

• 3-gram
• INDEXはGIN/Gistを利用
• クラッシュリカバリ対応
• ストリーミングレプリケーション対応
• n-gramのINDEX
  • 日本語文書もそのまま使える

• 注意！リビルドが必要
  • KEEPONLYALNUMをコメントしてmake

• 入力されたテキストを、スペースで節を区切る
• 区切った説をそれぞれ３文字づつに分離
  • マルチバイトも問題なし

• ２文字以下の単語はNG
  • 3文字単位のため、２文字以下だとインデックスが利用できない
  • 1文字、2文字の場合は結果は問題?ないんだけど、性能低下

textsearch_senna

• アイルトンセナから命名？
• とにかく高速
• クラッシュリカバリは対応していない
• ストリーミングレプリケーションは対応していない
• 9.1にも最近対応

• 弱点
  • 特殊なINDEX
  • バックアップ
    • pg_dumpであれば問題なし
    • ホットバックアップはダメ
  • レプリケーション
    • pg_pool、slonyは問題なし
    • WALを使わないので、ストリーミングレプリケーションはだめ

• 大きな問題：DBへの更新頻度が高いとと間違った結果を返す
  • Postgresの仕様と関連
    • DELETE、UPDATEを行ったとしてもpostgres上はIDを再利用してしまうから
    • senna上はインデックスを参照するため、定期的なREINDEXが必要
    • 完全には仕様上対応できない

所見

• 弱点がお互い強すぎる？
  • 条件・仕様の見直し
    • 2-gram問題
    • 日本語はどうなの？東京、京都問題とか
  • 複数カラムの問題
    • 文字列結合させる？
    • INDEXはどうなのか？
  • 文字列の大文字小文字について同様に無理
  • 別の外部アプリ
    • それともナレッッジのようなものを別のDBとして格納しOR検索
  • 文字の揺らぎ
  • ハイブリッド
  • 処理は当然シンプル
    • n-gram自体も
    • 説明はしやすいかも
  • スコア判定はできない
    • TF-IDF方式は当然ない

• 今までDROP＋CREATEしていたDBをレプリケーション対応させる際に気にする点
  • VAACUUM FULLの問題
    • タイミング
    • 必須性
    • AutoVacuum

• 全文検索を見直す場合
  • 別の仕様を確立すれば問題なし
    • もともと別のアプリで全文エンジンを使っており、それを見直すとした場合、現状の仕様を引き継ぐのであれば難しい。
    • できないことも多々存在。必要な仕様なのかどうか要検討する必要。

とここまで書いておいてなんだけどホントメモだなあ…

ほとんど↑の内容はこちらを見て頂ければ

問題ありません。

結構大事だと感じた点は

• Explaining_Explain自体古い
  • 最新版が出ると幸せになれる気がする
  • 当時のバージョンでは考えられなかったものが盛り込まれていて(9系のVACUUMの話しとか)、おもしろかったなあと。
• 今回の発表のpg_trigm/sennaはそれぞれで得手不得手がある。
  • 用途によって使い分けが大事

といった所でしょうか。