"CREATE INDEX" SQL 11/05/95 PostgreSQL PostgreSQL

名前

create index - 二次インデックスを構築する

書式

create [unique] index インデックス名
    on クラス名 [using アクセス方式名]
    ( 属性名 [型クラス], ... )



create [unique] index インデックス名
    on クラス名 [using アクセス方式名]
    ( 関数名 ( 属性名\-1 { , 属性名\-i }
    ) 型クラス )

説明

このコマンドはインデックス名と呼ばれるインデックスを構築する。

アクセス方式名はそのインデックスのために使われるアクセス方式の名前である。デフォルトのアクセス方式は btree である。

上記における最初の書式では、インデックスのキー項目は属性名として指定される。これには関連する演算子クラスがある場合もある。演算子クラスは、特殊なインデックスにおいて使われる演算子を指定するのに使われる。たとえば、4 バイト整数における btree インデックスはint4_opsクラスを使う場合がある。この演算子クラスは 4 バイト整数を比較するための関数を含んでいる。デフォルトの演算子クラスは、その項目型において適切な演算子クラスとなる。

注意：現在のところ、btreeアクセス方式だけが複数属性インデックスをサポートしている。

2 番目の書式を使うと、単一クラスの中の 1 つ以上の属性に適用されるユーザ定義関数関数名の結果によりインデックスを定義できる。これらの関数インデックスを使うと、通常元になるデータに対してある種の変換が必要となるような演算子をベースとするデータに対し、高速なアクセスを行うことができるようになる。たとえば、あなたは "myclass" 内部に、2D 座標型を持つような "pt" と呼ばれる属性を持っているとしよう。ここで、この属性に対してインデックスを張りたいが、あなたは 2D 多角形型のインデックス演算子クラスしか持っていないとする。あなたは、自分で書いた関数(それを"point_to_polygon" とする)と、元々あった多角形演算子クラスを使って、その座標属性に対してインデックスを張ることができる。そうすることにより、演算子のうち一方の"point_to_polygon(myclass.pt)" を参照する既存の多角形演算子を使った問い合わせは、"myclass" 中のすべてのインスタンスについて毎回各多角形の計算をしてそれをその演算子のもう一方の値と比較するかわりに、関数インデックス内に格納してある前もって計算しておいた多角形を使う。関数インデックスを構築するということは、(インデックス用の)記憶領域と実行時間とのトレードオフであることは明らかである。

unique キーワードを使うと、システムはインデックスを生成する際に、(データがすでに存在していれば)値の重複チェックを行ってからデータを追加する。重複しないデータを挿入したり更新したりしようとするとエラーとなる。

Postgres では、二次インデックスに対して btree, rtree およびハッシュ(hash)アクセス方式を提供している。btree アクセス方式は、Lehman-Yao の高並列 btree 実装系を採用している。rtree アクセス方式では Guttman の四分の一分割アルゴリズムを使った標準 rtreeで実装されている。ハッシュアクセス方式は Litwin の線形ハッシュの実装系である。これらすべてのアクセス方法は完全に動的であり、また定期的に最適化される必要はない(たとえばこのケースとして静的ハッシュ方式があげられる)ことをお断りしておく。

このリストは、Postgres システムカタログから以下の問い合わせにより生成されたものである。

SELECT am.amname AS acc_name, opc.opcname AS ops_name, opr.oprname AS ops_comp FROM pg_am am, pg_amop amop, pg_opclass opc, pg_operator opr WHERE amop.amopid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid ORDER BY acc_name, ops_name, ops_comp;
acc_name|ops_name |ops_comp --------+-----------+-------- btree |abstime_ops|< btree |abstime_ops|<= btree |abstime_ops|= btree |abstime_ops|> btree |abstime_ops|>= btree |bpchar_ops |< btree |bpchar_ops |<= btree |bpchar_ops |= btree |bpchar_ops |> btree |bpchar_ops |>= btree |char16_ops |< btree |char16_ops |<= btree |char16_ops |= btree |char16_ops |> btree |char16_ops |>= btree |char2_ops |< btree |char2_ops |<= btree |char2_ops |= btree |char2_ops |> btree |char2_ops |>= btree |char4_ops |< btree |char4_ops |<= btree |char4_ops |= btree |char4_ops |> btree |char4_ops |>= btree |char8_ops |< btree |char8_ops |<= btree |char8_ops |= btree |char8_ops |> btree |char8_ops |>= btree |char_ops |< btree |char_ops |<= btree |char_ops |= btree |char_ops |> btree |char_ops |>= btree |date_ops |< btree |date_ops |<= btree |date_ops |= btree |date_ops |> btree |date_ops |>= btree |float4_ops |< btree |float4_ops |<= btree |float4_ops |= btree |float4_ops |> btree |float4_ops |>= btree |float8_ops |< btree |float8_ops |<= btree |float8_ops |= btree |float8_ops |> btree |float8_ops |>= btree |int24_ops |< btree |int24_ops |<= btree |int24_ops |= btree |int24_ops |> btree |int24_ops |>= btree |int2_ops |< btree |int2_ops |<= btree |int2_ops |= btree |int2_ops |> btree |int2_ops |>= btree |int42_ops |< btree |int42_ops |<= btree |int42_ops |= btree |int42_ops |> btree |int42_ops |>= btree |int4_ops |< btree |int4_ops |<= btree |int4_ops |= btree |int4_ops |> btree |int4_ops |>= btree |name_ops |< btree |name_ops |<= btree |name_ops |= btree |name_ops |> btree |name_ops |>= btree |oid_ops |< btree |oid_ops |<= btree |oid_ops |= btree |oid_ops |> btree |oid_ops |>= btree |oidint2_ops|< btree |oidint2_ops|<= btree |oidint2_ops|= btree |oidint2_ops|> btree |oidint2_ops|>= btree |oidint4_ops|< btree |oidint4_ops|<= btree |oidint4_ops|= btree |oidint4_ops|> btree |oidint4_ops|>= btree |oidname_ops|< btree |oidname_ops|<= btree |oidname_ops|= btree |oidname_ops|> btree |oidname_ops|>= btree |text_ops |< btree |text_ops |<= btree |text_ops |= btree |text_ops |> btree |text_ops |>= btree |time_ops |< btree |time_ops |<= btree |time_ops |= btree |time_ops |> btree |time_ops |>= btree |varchar_ops|< btree |varchar_ops|<= btree |varchar_ops|= btree |varchar_ops|> btree |varchar_ops|>= hash |bpchar_ops |= hash |char16_ops |= hash |char2_ops |= hash |char4_ops |= hash |char8_ops |= hash |char_ops |= hash |date_ops |= hash |float4_ops |= hash |float8_ops |= hash |int2_ops |= hash |int4_ops |= hash |name_ops |= hash |oid_ops |= hash |text_ops |= hash |time_ops |= hash |varchar_ops|= rtree |bigbox_ops |&& rtree |bigbox_ops |&< rtree |bigbox_ops |&> rtree |bigbox_ops |<< rtree |bigbox_ops |>> rtree |bigbox_ops |@ rtree |bigbox_ops |~ rtree |bigbox_ops |~= rtree |box_ops |&& rtree |box_ops |&< rtree |box_ops |&> rtree |box_ops |<< rtree |box_ops |>> rtree |box_ops |@ rtree |box_ops |~ rtree |box_ops |~= rtree |poly_ops |&& rtree |poly_ops |&< rtree |poly_ops |&> rtree |poly_ops |<< rtree |poly_ops |>> rtree |poly_ops |@ rtree |poly_ops |~ rtree |poly_ops |~=

int24_ops演算子クラスは、int2 データのインデックスを構築してからint4 データとの比較を行う際に有用である。同様に、int42_opsでは int4 データを int2 データと比較するためのインデックスをサポートしている。

演算子クラスoidint2_ops ,oidint4_ops ,とoidchar16_opsは、複数キーインデックスをシミュレートするために関数インデックスを使う場合に使用するが、複数キーインデックスがサポートされたためにこれらはもはや必要とされない。

Postgres の問い合わせ最適化機構では、インデックスされた属性が

< <= = >= >

を使った比較で呼び出される際は常に、スキャンするのにbtree インデックスを使うことを考える。いずれの長方形(box)クラスにおいても、Postgres では"box" データ型におけるインデックスがサポートされている。それらの違いは、bigbox_opsでは乗算および巨大な浮動小数点座標における減算において浮動小数点例外が発生するのを防ぐために、長方形の座標をスケールダウンを行うことである。長方形を含む項目が 2 万個以上ある場合はbigbox_opsを使う方がよいだろう。

Postgres の問い合わせ最適化機構では、インデックスされた属性が

<< &< &> >> @ ~= &&

を使った比較で呼び出される際は常に、rtree インデックスを使うことを考える。Postgres の問い合わせ最適化機構では、インデックスされた属性が= を使った比較で呼び出される際は常に、ハッシュインデックスを使うことを考える。

例

-- -- 従業員クラスに年齢属性を使って btree インデックスを生成する -- create index 従業員インデックス on 従業員 using btree (年齢 int4_ops)

-- 従業員名で btree インデックスを生成する -- create index 従業員名 on 従業員 using btree (名前 char16_ops)

-- -- 都市の境界長方形の rtree インデックスを生成する -- create index 都市境界 on 都市 using rtree (境界長方形 box_ops)

-- -- 変換関数の結果における長方形演算子を効率的に使用する -- ような座標属性の rtree インデックスを生成する。このような -- 修飾子は where point2box(points.pointloc) = boxes.box -- のようになるだろう。 create index pointloc on points using rtree (point2box(location) box_ops)

翻訳者

堀田倫英 <hotta@net-newbie.com>