タイムスタンプの範囲を持つテーブルがあると仮定します（はい、日付を生成できるgenerate_series関数があります）：
CREATE TABLE t AS SELECT d, repeat(md5(d::text), 10) AS padding FROM generate_series(timestamp '1900-01-01', timestamp '2100-01-01', interval '1 day') s(d); VACUUM ANALYZE t;
テーブルには、少し大きくするためのパディング列も含まれています。 次に、単純な範囲クエリを実行して、テーブルで使用可能な約200年のうち1か月のみを返します。 Explainを使用してこのリクエストを実行すると、次のようなメッセージが表示されます。
EXPLAIN SELECT * FROM t WHERE d BETWEEN '2001-01-01' AND '2001-02-01'; QUERY PLAN ------------------------------------------------------------------------ Seq Scan on t (cost=0.00..4416.75 rows=32 width=332) Filter: ((d >= '2001-01-01 00:00:00'::timestamp without time zone) AND (d <= '2001-02-01 00:00:00'::timestamp without time zone)) (2 rows)
私のコンピューターでは、リクエストは約20ミリ秒実行されます。 彼は、テーブル全体（75千行で構成される）を調べる必要があるという事実を考えると、悪くありません。
しかし、タイムスタンプ付きの列にインデックスを作成しましょう（このテキストのすべてのインデックスは基本、つまり明示的に指定されていない場合はbtreeです）：
CREATE INDEX idx_t_d ON t (d);
そして、リクエストを再度実行してみましょう。
QUERY PLAN ------------------------------------------------------------------------ Index Scan using idx_t_d on t (cost=0.29..9.97 rows=34 width=332) Index Cond: ((d >= '2001-01-01 00:00:00'::timestamp without time zone) AND (d <= '2001-02-01 00:00:00'::timestamp without time zone)) (2 rows)
現在は0.5ミリ秒で実行されます。これは、大まかに言って40倍高速です。 ただし、もちろん、式によるインデックスではなく、列に直接作成された単純なインデックスでした。 したがって、最後のクエリではなく、次のクエリを実行して、各月の最初の日ごとにデータを要求すると仮定します。
SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;
ただし、EXPLAIN ANALYZEで示されるように、列自体でインデックスが構築されている間、列で式を実行する必要があるため、インデックスを使用できません。
QUERY PLAN ------------------------------------------------------------------------ Seq Scan on t (cost=0.00..4416.75 rows=365 width=332) (actual time=0.045..40.601 rows=2401 loops=1) Filter: (date_part('day'::text, d) = '1'::double precision) Rows Removed by Filter: 70649 Planning time: 0.209 ms Execution time: 43.018 ms (5 rows)
そのため、彼はシーケンシャルスキャンを実行するだけでなく、数をカウントして、リクエスト時間を43ミリ秒に増やす必要があります。
データベースはいくつかの理由でインデックスを使用できません。 インデックス（少なくともbtree）は、ツリー構造によって提供されるソートされたデータのクエリに基づいており、範囲の最初のクエリがこれから利益を得る場合、2番目（抽出呼び出しで）はそうではありません。
注：もう1つの問題は、インデックスでサポートされている一連の演算子（つまり、インデックス自体で直接実行できる）が非常に限られていることです。 また、抽出機能はサポートされていないため、ビットマップインデックススキャンを使用してクエリで並べ替えの問題を回避することはできません。
理論的には、データベースは条件を一連の条件に変換しようとする場合がありますが、これは非常に複雑で、各式に固有です。 この場合、スケジューラは実際にはテーブル内の最小/最大タイムスタンプを知らないため、このような範囲を「1日に」無数に生成する必要があります。 そのため、ベースは試行さえしません。
しかし、データベースは条件を変換する方法を知りませんが、開発者は原則として知っています。 たとえば、次の形式の条件：
(column + 1) >= 1000
次のように書き換えることはまったく難しくありません。
column >= (1000 - 1)
そして、それはすでにインデックスでうまく機能します。
しかし、私たちのリクエストの場合のように、そのような変換が不可能な場合はどうなりますか：
SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;
この場合、開発者は列dの未知の最小/最大値の同じ問題に直面する必要があり、それでも多くの範囲を生成する必要があります。
さて、この記事は式インデックスに関するものであり、列まで直接構築されたこの時点までは通常のインデックスのみを使用しました。 式で最初のインデックスを作成しましょう：
CREATE INDEX idx_t_expr ON t ((extract(day FROM d))); ANALYZE t;
その結果、次の計画を取得します。
QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on t (cost=47.35..3305.25 rows=2459 width=332) (actual time=2.400..12.539 rows=2401 loops=1) Recheck Cond: (date_part('day'::text, d) = '1'::double precision) Heap Blocks: exact=2401 -> Bitmap Index Scan on idx_t_expr (cost=0.00..46.73 rows=2459 width=0) (actual time=1.243..1.243 rows=2401 loops=1) Index Cond: (date_part('day'::text, d) = '1'::double precision) Planning time: 0.374 ms Execution time: 17.136 ms (7 rows)
これまでのところ、最初の例のインデックスと同じように、速度が40倍向上することはありません。 このクエリはより多くのタプルを返します（2401対32）。 さらに、それらはテーブル全体に分散しており、最初の例ほどローカライズされていません。 したがって、これは2倍の良好な加速であり、実際の生活の多くの状況では、はるかに大きな増加が見られます。
しかし、ベースで複雑な式を持つ条件にインデックスを使用する機能は、この記事で最も興味深い情報ではありません。これが、式でインデックスを作成する理由です。 しかし、これが唯一の利点ではありません。
以下の2つのクエリ実行プラン（式によるインデックスの有無）を見ると、次のことがわかります。
QUERY PLAN ------------------------------------------------------------------------ Seq Scan on t (cost=0.00..4416.75 rows=365 width=332) (actual time=0.045..40.601 rows=2401 loops=1) ...
QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on t (cost=47.35..3305.25 rows=2459 width=332) (actual time=2.400..12.539 rows=2401 loops=1) ...
そうです-式でインデックスを作成すると、評価が大幅に向上しました。 インデックスがない場合、テーブルの大まかな列の統計（MCV +ヒストグラム）のみがあり、データベースは式の評価方法を知りません。
EXTRACT(day FROM d) = 1
したがって、デフォルトの等値比較を適用します。この比較では、すべての行の約0.5％が返されます。テーブルには73050行あるため、推定365行のみになります。 多くの場合、実際のアプリケーションでははるかに大きな推定誤差が見られます。
ただし、インデックスを使用すると、データベースはインデックスの列に関する統計も収集し、この場合、列には式の結果が含まれます。 計画プロセスでは、オプティマイザーはこれに注意を払い、はるかに優れた評価を行います。
これは、不正確な見積もりによって引き起こされる質の悪いクエリプランの問題に対処できる大きな利点です。 ただし、ほとんどの人はこのような便利なツールを認識していません。
そして、このツールの有用性は、バージョン9.4でJSONBデータ型が導入されて初めて増加しました。これは、JSONBドキュメントのコンテンツに関する統計を収集する唯一の方法であるためです。
JSONBドキュメントを使用する場合、2つの基本的なインデックス戦略があります。 次のように、ドキュメント全体でGIN / GISTインデックスを作成できます。
CREATE INDEX ON t USING GIN (jsonb_column);
JSONBフィールドの任意の部分でクエリを実行したり、コンテンツ演算子を使用してサブドキュメントを比較したりすることができます。 これは問題ありませんが、列の基本的な統計情報しかありません。ドキュメントはスカラー値として提供されるため（ドキュメント全体と一致しないか、ドキュメントの範囲を使用しないため）、あまり便利ではありません。
たとえば、式インデックスは次のように作成されます。
CREATE INDEX ON t ((jsonb_column->'id'));
この特定の例では、特定の式に対してのみ有用です。
SELECT * FROM t WHERE jsonb_column ->> 'id' = 123;
ただし、値など、他のJSONキーに向けられたリクエストの場合：
SELECT * FROM t WHERE jsonb_column ->> 'value' = 'xxxx';
これは、ドキュメント全体でGIN / GISTインデックスが役に立たないという意味ではありませんが、選択する必要があります。 または、特定の式に向けられたインデックスを作成します。これは、クエリが特定のキーに移動するときに便利で、式に関する統計情報の利点が追加されます。 または、ドキュメント全体にGIN / GISTインデックスを作成し、任意のキーのリクエストを処理できますが、統計は使用しません。
ただし、この場合、両方のインデックスを同時に作成でき、データベース自体がどちらをどのクエリに使用するかを選択するため、1つの石で両方の鳥を殺すことができます。 また、式によるインデックスのおかげで、正確な統計情報が得られます。
残念ながら、式インデックスとGIN / GISTインデックスは異なる条件を使用します。
-- expression (btree) SELECT * FROM t WHERE jsonb_column ->> 'id' = 123; -- GIN/GiST SELECT * FROM t WHERE jsonb_column @> '{"id" : 123}';
そのため、スケジューラはそれらを同時に使用できません-評価用の式インデックスと実行用のGIN / GISTインデックス。