PostgreSQL インデックスの効率的な使用
インデックスを使用しないことを Postgres プランナーが選択する理由は数多くあります。ほとんどの場合、理由が明白でないとしても、プランナーは正しい選択を行います。同じクエリでインデックススキャンを使用する場合と使用しない場合があっても問題ありません。テーブルから取得される行数は、クエリで取得する特定の定数値によって異なる場合があります。したがって、たとえば、"bar" の値が 2 である行の方が偶然にもずっと多い場合に、クエリプランナーがクエリ select * ファンクションインデックス from foo where bar ファンクションインデックス = 1 にはインデックスを使用するのに対してクエリ select * from foo where bar = 2 にはインデックスを使用しないとしても、それは適切である可能性があります。この場合、シーケンシャルスキャンの方が実際にはインデックススキャンよりもずっと高速である可能性が高いため、クエリプランナーは実際に、そのようにクエリを実行するコストの方が低いと正しく判断しました。
部分インデックス
部分インデックスはテーブルのデータのサブセットのみをカバーします。これは WHERE 句を伴ったインデックスです。インデックスのサイズを減らすことでインデックスの効率を高めるという考えです。インデックスが小さいほど、ストレージ使用量が減り、メンテナンスが容易になり、スキャンが高速になります。
たとえば、サイト上のコメントにユーザーがフラグを付けることを許可し、これによって flagged ブール値が true に設定されるとします。その後、フラグの付いたコメントをバッチで処理します。この場合、次のようにしてインデックスを作成できます。
式インデックス
式インデックスは、データに対する関数または変更に一致するクエリに有用です。Postgres では、その関数の結果にインデックスを付けて、生のデータ値による検索と同等の検索効率を実現できます。たとえば、サインイン用のメールアドレスを保存することをユーザーに求める一方で、認証では大文字と小文字を区別しない場合を考えます。この場合、メールアドレスはそのまま保存しますが、検索は ファンクションインデックス WHERE lower(email) = '' 条件で実行することが可能です。そのようなクエリでインデックスを使用するには、次のような式インデックスを使用するのが唯一の方法です。
もう 1 つの一般的な例として、特定の日付の行を検索する場合に、datetime フィールドにタイムスタンプが保存されているが、日付にキャストされた値によって検索を行うことを考えます。 CREATE INDEX articles_day ON articles ( date(published_at) ) のようなインデックスは、 WHERE date(articles.published_at) = date('2011-03-07') を含むクエリで使用できます。
一意インデックス
一意インデックスは、同じ値を持つ複数の行がテーブルに存在しないことを保証します。一意インデックスを作成することには、データの整合性とパフォーマンスという 2 つの理由でメリットがあります。一意インデックスのルックアップは通常、非常に高速です。
データの整合性に関しては、ActiveModel クラスで validates_uniqueness_of 検証を使用しても、無効なレコードを作成する同時ユーザーがすでに存在する可能性があり、また今後存在すると予想されるため、真の意味での一意性は保証されません。したがって、インデックスまたは一意性制約のどちらかを使用して、常にデータベースレベルで制約を作成するべきです。
複数列インデックス
Postgres では複数列インデックスを作成できますが、作成することに意味がある状況を理解することが重要です。Postgres クエリプランナーでは、ビットマップインデックススキャンを実行することによって、複数の単一列インデックスを 1 つの複数列クエリに結合して使用することができます。一般的に、クエリ条件をカバーするすべての列にインデックスを作成でき、ほとんどの場合に Postgres はインデックスを使用するため、複数列インデックスを作成する前に必ずベンチマークを行い、作成を正当化してください。インデックスには常にコストがかかり、複数列インデックスが最適化できるのはインデックス内の列を同じ順序で参照するクエリに限られるのに対し、複数の単一列インデックスの方が、より多くのクエリにパフォーマンスの向上をもたらします。
ただし、複数列インデックスが明確に意味を持つ状況があります。列 (a, b) のインデックスは、 WHERE a = x AND b = y を含むクエリ、または WHERE a = x のみを使用するクエリで使用できますが、 WHERE b = y を使用するクエリでは使用されません。そのため、これがアプリケーションのクエリパターンと一致する場合は、複数列インデックスのアプローチを検討する価値があります。この場合、 a のみにインデックスを作成すると冗長になることにも注意してください。
B-Tree とソート
B-Tree インデックスのエントリは、デフォルトでは昇順でソートされます。インデックスの異なるソート順を指定することに意味がある場合もあります。たとえば、ページ番号を付けた記事のリストを、公開が最も新しいものが先頭に来るようにソートして表示する場合を考えます。 articles テーブルに published_at 列があるとします。未公開の記事の場合、 published_at の値は NULL です。
Postgres 9.2 以上では、必要なものすべてをインデックスから取得できる (つまり、インデックスのない列に関心がない) 場合、インデックスは必ずしもテーブルを参照する必要はない、という点は注目に値します。これは “インデックスオンリースキャン” と呼ばれる機能です。
published_at のソート順でテーブルをクエリして結果を制限するので、同じ順序でインデックスを作成すると多少のメリットが得られる場合があります。Postgres は、必要な行をインデックスから正しい順序で検索し、データブロックにアクセスしてデータを取得します。インデックスがソートされていない場合、Postgres がデータブロックをシーケンシャルに読み取って結果をソートする可能性が高くなります。
この手法が主に関係するのは “ヌルを末尾にソート” の動作が必要なときの単一列インデックスです。それ以外の場合、インデックスはどの方向にもスキャン可能なので順序はすでに利用可能であるからです。 a ASC, b DESC のように複合的なソート順をクエリが要求するときに複数列インデックスに対して使用する場合、さらに関連性が高くなります。
インデックスの管理とメンテナンス
Postgres ファンクションインデックス のインデックスは、すべての行データを保持するわけではありません。インデックスがクエリで使用され、一致する行が見つかった場合でも、Postgres はディスクにアクセスして行データをフェッチします。さらに、(MVCC に関する記事)で説明した) 行の可視性情報もインデックスに保存されないため、Postgres もディスクにアクセスしてその情報をフェッチする必要があります。
以上を踏まえると、場合によってはインデックスを使用しても実際には意味がない理由がわかります。インデックスは、ディスクルックアップの数を減らせるほどに選択的でなければ、価値がありません。たとえば、十分に大きいテーブルに対するプライマリキールックアップは、インデックスを有効利用します。クエリ条件に一致するテーブルをシーケンシャルスキャンする代わりに、Postgres はターゲット行をインデックスから見つけて、ディスクから選択的にフェッチすることができます。都市ルックアップテーブルのようにごく小さなテーブルに関しては、都市名で検索する場合でもインデックスは望ましくない可能性があります。その場合、Postgres はインデックスを無視してシーケンシャルスキャンを優先することを決定する可能性があります。Postgres は、テーブルの重要な部分にヒットするクエリではシーケンシャルスキャンを実行することを決定します。その列にインデックスがある場合、決して使用されないデッドインデックスになります。また、インデックスには必ずコストがかかり、具体的にはストレージとメンテナンスの面でコストがかかります。
Heroku アプリケーションのための本番環境、ステージング環境、およびその他の環境の運用について詳しくは、「Managing Multiple Environments」(複数の環境の管理) の記事を参照してください。
クエリをチューニングするときや、どのインデックスが最も有効かを見極めるときは必ず、本番環境で使用している (または、使用する予定の) データベースにできるだけ近いデータベースを使用してください。インデックスを使用するかどうかは、Postgres サーバーの設定、テーブル内のデータ、インデックス、クエリなど、いくつかの要因に依存します。たとえば、"テストデータ" の小さなサブセットを載せた開発マシン上でクエリにインデックスを使わせようとしてもうまくいきません。Postgres は、データセットが小さすぎるのでインデックス全体を読み取るのはそのオーバーヘッドに見合わないと判断して、ディスクからデータをフェッチします。ランダム I/O ファンクションインデックス はシーケンシャルよりもずっと低速なため、シーケンシャルスキャンのコストは、インデックスを読み取ってディスク上のデータを選択的に探すことによって発生するランダム I/O のコストよりも低くなります。インデックスチューニングの実行は、本番環境か、できるだけ本番環境に近いステージング環境で行う必要があります。Heroku Postgres データベースプラットフォームでは、ごく簡単な手順で本番データベースを別の環境にコピーできます。
本番データベースにインデックスを適用する準備ができたら、インデックスを作成するとテーブルが書き込みロックされることに注意してください。テーブルが大きい場合、サイトが数時間ダウンする可能性があります。幸いにも、Postgres では CREATE INDEX CONCURRENTLY を使用できます。構築にかかる時間はかなり長くなりますが、書き込みをブロックするロックは必要ありません。通常の CREATE INDEX コマンドでは、書き込みをブロックするが読み取りはブロックしないロックが必要になります。
最後に、しばらく時間が経つと、テーブルの行が頻繁に更新または削除される場合は特に、インデックスは断片化して最適でなくなります。そのような状況では、 REINDEX を実行してインデックスのバランスを回復し、最適化することが必要な場合があります。ただし、親テーブルに書き込みロックがかかるため、大きなインデックスの再インデックス処理には注意が必要です。ライブサイトで同じ結果を得るための 1 つの戦略は、同じテーブルと列に対して別の名前で同時にインデックスを構築し、元のインデックスを削除し、新しいインデックスの名前を変更するというものです。この手順は、時間はかかりますが、ライブテーブルに長時間のロックをかける必要がなくなります。
特定のユースケースに合わせて最適化される B-Tree インデックスの作成と、アプリケーションの裏側で拡大を続けるデータベースを管理するためのオプションに関して、Postgres は多くの柔軟性を提供します。以上のヒントは、データベースを正常な状態に保ち、クエリを高速化するために役立ちます。
計算列のインデックス
指定された一連の入力に対して式から必ず同じ結果が返される場合、その式は決定的です。 COLUMNPROPERTY 関数の IsDeterministic ファンクションインデックス プロパティは、 computed_column_expression が決定的であるかどうかを示します。
computed_column_expression は、決定的である必要があります。 次のすべての条件に該当する場合、computed_column_expression は決定的です。
式で参照される関数が決定的かつ正確である場合。 このような関数には、ユーザー定義関数と組み込み関数の両方があります。 詳細については、「 決定的関数と非決定的関数」を参照してください。 計算列が PERSISTED の場合、関数は不正確になる場合があります。 詳細については、このトピックの後半の「 ファンクションインデックス 保存される計算列でのインデックスの作成 」を参照してください。
列参照が複数の行からデータを取り出していない場合。 たとえば、SUM や AVG などの集計関数は複数の行のデータに依存して計算を行うので、 computed_column_expression は非決定的になります。
computed_column_expression がシステム データ アクセスやユーザー データ アクセスを伴わない場合。
共通言語ランタイム (CLR) 式を含むすべての計算列は決定的であり、インデックスを作成する前に PERSISTED に設定されている必要があります。 CLR ユーザー定義型の式を、計算列の定義に使用できます。 計算列の型が CLR ユーザー定義型の場合、その型が比較可能である限り、計算列にインデックスを作成できます。 詳細については、「 CLR ユーザー定義型」を参照してください。
CAST および CONVERT
SQL Server のインデックス付き計算列で日付データ型の文字列リテラルを参照する場合は、明確な日付形式スタイルを使用して、リテラルを必要な日付型に明示的に変換することをお勧めします。 決定的な日付形式の一覧については、「 CAST および CONVERT」を参照してください。
互換性レベル
照合順序間で行われる Unicode 以外の文字データの暗黙的な変換は、互換性レベルが 80 以下の場合を除いて、非決定論的であると見なされます。
データベース互換性レベルの設定が 90 の場合は、それらの式を含む計算列にインデックスを作成することはできません。 ただし、アップグレードされたデータベースから、このような式を含む既存の計算列をメンテナンスできます。 文字列から日付への暗黙的な変換を行うインデックス付き計算列を使用する場合は、インデックスが破損しないように、データベースやアプリケーション内で LANGUAGE と DATEFORMAT の設定の一貫性を確保してください。
互換性レベル 90 は SQL Server 2005 に該当します。
正確さの要件
computed_column_expression は正確である必要があります。 computed_column_expression は、次の 1 つ以上の条件に該当する場合は正確です。
float データ型または real データ型の式ではない。
式の定義に float データ型や real データ型を使用していない。 たとえば、次のステートメントでは、列は y y で、かつ、正確ではありません。
float 型や real 型の式はすべて不正確であると見なされ、インデックスのキーにできません。つまり、 float 型または real 型の式はインデックス付きビューで使用できますが、キーとしては使用できません。 ファンクションインデックス このことは、計算列にも当てはまります。 float 型や real 型の任意の式が含まれている関数、式、またはユーザー定義関数はすべて不正確であると見なされます。 これには、論理式 (比較) も含まれます。
COLUMNPROPERTY 関数の IsPrecise プロパティは、 computed_column_expression が正確であるかどうかを示します。
データ型の要件
- 計算列で定義された computed_column_expression は、 textデータ型、 ntextデータ型、または image データ型として評価できません。
- image、 ntext、 ファンクションインデックス text、 varchar(max)、 nvarchar(max)、 varbinary(max)、および xml データ型から派生した計算列には、計算列のデータ型をインデックス キー列として使用できる限り、インデックスを作成できます。 ファンクションインデックス
- image、 ntext、および text データ型から派生した計算列は、計算列のデータ型を非キー インデックス列として使用できる限り、非クラスター化インデックスの非キー列 (付加列) にすることができます。
SET オプションの要件
計算列を定義する CREATE TABLE ステートメントまたは ALTER TABLE ステートメントの実行時に、ANSI_NULLS 接続レベルのオプションが ON に設定されている必要があります。 OBJECTPROPERTY 関数の IsAnsiNullsOn プロパティは、このオプションが ON に設定されているかどうかを示します。
インデックスを作成する接続、およびインデックス内の値を変更する INSERT、UPDATE、または DELETE ステートメントを実行するすべての接続では、SET オプションのうち 6 つを ON に設定し、1 つを OFF に設定する必要があります。 これらのオプションの設定が同一ではない接続で実行されるすべての SELECT ステートメントでは、オプティマイザーにより計算列のインデックスが無視されます。
- NUMERIC_ROUNDABORT オプションは OFF に設定し、次のオプションは ON に設定する必要があります。
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL ファンクションインデックス
- QUOTED_IDENTIFIER
ANSI_WARNINGS を ON に設定すると、データベース互換性レベルが 90 以上に設定されている場合、暗黙的に ARITHABORT が ON ファンクションインデックス に設定されます。
保存される計算列でのインデックスの作成
不正確であるが決定的な式で定義される計算列を作成できることがあります。 これは、CREATE TABLE または ALTER TABLE ステートメントで列に PERSISTED マークが付いているときに可能です。
つまり、計算データベース エンジンがテーブルに格納され、計算列が依存する他の列が更新された場合に更新されます。 列データベース エンジンインデックスを作成するときに、およびクエリでインデックスが参照される場合に、これらの永続化された値が使用されます。
このオプションを使用すると、計算列式 (特に .NET Framework で作成された CLR 関数) を返す関数が決定論的で正確であるかどうかを データベース エンジン が正確に証明できない場合に、計算列にインデックスを作成できます。
WORLD(株式会社ワールド)
ライトグレー(011)
ブラック(ファンクションインデックス 019)
トープ(054)
ピンク(072)
ブルー(092)
SQLのチューニング方法
最近のDBはコストベースになるのでオプティマイザによって作成される実行計画が望み通りの計画になっているのかを見ながらチューニングしていく必要があります。
今まで見てきた遅いSQLはとんでもない結合順や使用されるインデックスがなんでここから？といった実行計画になっていた事が大半でした。
そういう時に正しい実行計画をオプティマイザに作ってもらうようにSQLを試行錯誤し変更してチューニングしていく必要があります。絶対的な回答はありませんが下記のようにとれる手段を増やしていくのは有効な手だと思います。
実行計画を知ろう
実行計画をEXPLAIN文などで知ることが大切。これで出される実行計画に結合順やどのキーで結合されたのかなどすべて載っています。開発や遅いSQLを修正するときはとても有用です。
ただし大きな罠があります
それはプログラムではバインド変数を使ってSQLを流しますが、デバッグでは実数を入れることが多々あったり、EXPALINという文字が入ることによってSQLIDが本番と異なっています。実行計画はSQLの形によって発行されるSQLIDごとに作成されるため、プログラムで動いている実行計画とデバッグの実行計画が必ずしも一致するとは限らないことが結構厄介だったりします。
そのため、デバッグで速いのに本番では遅い。といった時はまず使用されている実行計画が違うんだろうと疑う必要もあると思います。
本番でどのような実行計画が流れているか知りたい場合はSQLIDを割り出し、実行計画を出力したりするツールが用意されているのでそれを使って解析するといったフェーズに移ったりします。
何故実行計画が急に狂ったりしてしまうのか？
例えば東京の電車と日付を指定された場合、東京の電車が殆どだと思うので在庫テーブルを日付で絞った後に路線、という結合順でいった方がより絞込りこむ量が減らせそうです。
これが沖縄になると沖縄はモノレールで全国からみると、とても少ない路線数なので路線テーブルを沖縄で絞ってから在庫と結合するのが良いはずです。
こういった殆どは普段は日付から検索にいく方がいいが、実行計画を作られるときに運悪く沖縄って検索されたタイミングで悲劇が起こったりします。
※上記に関しては11g、12cのバージョンでの経験なので最新は変わっているかもしれません
これを防ぐためにindexや適切なテーブル設計、どうしようもない時はヒント句やsqlid指定による実行計画の固定。といった手段をとる必要が出てきます。（個人的にはsqlid指定による実行計画固定はSQLの形が変わると効かなくなる秘伝のだし的なものになるのであまりオススメは出来ないと思います。ヒント句も効いてくれないような場合の最終手段だと思います。）
具体的なチューニング手段
統計情報の最新化
実行計画を作る際の統計情報が古い可能性があります。データの実体と合わない統計情報だと変な実行計画ができかねないので最新の物を作りましょう。
Oracleのデフォルトでは確か自動でとってます。ただ、自動取得のタイミングがデフォルトの時間が夜とかで結構いやなタイミングだったりするのでそこは任意の時間にした方が良さそうです。
また、データのサンプリング数（比率）を変えることも可能です。デフォルトのサンプリング数だと不十分だと判断された場合はこちらも変えましょう。
インデックス/ｉｎｄｅｘ
【素材】
GIZA COTTONは世界三大綿のひとつ。エジプト綿の一種で、繊維の一本一本ががとても長い希少な品種です。エジプト綿には植物性の油脂が適度に含まれているので、光沢のあるとろみ感を生み出せます。そのGIZA COTTONを使い、程よく肉がありながら、軽さが特徴のポンチ素材に仕上げました。マルチファンクション付きでデイリー使いに最適です。マシンウォッシャブル、UVケア、接触冷感、抗菌防臭、抗ピリング機能付き
※この製品は、太陽光線中の紫外線（UV）を通しにくくします。この効果は永久的ではありません。
※この製品は、抗菌防臭加工を施しております。この効果は永久的ではありません。
関連アイテム
￥3,289 (14%OFF)
コメント