PostgreSQL: CREATE TABLEでインデックスを作成する方法
2024-04-02
PostgreSQL の CREATE TABLE でインデックスを作成する方法
PostgreSQL では、CREATE TABLE ステートメント内で直接インデックスを作成できます。これは、テーブル作成とインデックス作成を同時に済ませたい場合に便利です。
方法
CREATE TABLE ステートメントに INDEX キーワードを追加することで、インデックスを定義できます。
例
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
INDEX idx_name (name)
);
上記の例では、users テーブルを作成し、同時に以下のインデックスも作成しています。
- 主キーインデックス:
id列
詳細
-
インデックスの種類:
btree: 最も一般的なインデックス。B木構造を使用します。hash: ハッシュテーブルを使用。等価比較に最適化されています。gist: GiST (Generalized Search Tree) を使用。空間データや複雑なデータ型に適しています。- その他:
spgist,gin,brinなど
-
オプション:
USING: 使用するインデックスメソッドを指定します。WHERE: インデックスを作成する行をフィルタリングします。
補足
CREATE TABLEでインデックスを作成した場合、後からALTER TABLEを使ってインデックスを追加したり、削除したりすることもできます。- インデックスは、データ検索のパフォーマンスを向上させるために使用されます。
- どの列にインデックスを作成するべきかは、テーブルのデータとクエリパターンによって異なります。
基本的なインデックス
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE,
INDEX idx_name (name)
);
インデックスオプション
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category VARCHAR(255) NOT NULL,
price NUMERIC(10,2) NOT NULL,
INDEX idx_category_price (category, price) USING btree WHERE price > 100
);
このコードは、以下のインデックスを持つ products テーブルを作成します。
- 複合インデックス:
category列とprice列 - B木構造を使用
priceが 100 より大きい行のみインデックス化
部分インデックス
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(255) NOT NULL,
INDEX idx_open_orders (customer_id, order_date) WHERE status = 'open'
);
- 複合インデックス:
customer_id列とorder_date列 statusが 'open' の行のみインデックス化
PostgreSQL でインデックスを作成する他の方法
ALTER TABLE を使用する
テーブル作成後にインデックスを追加したい場合は、ALTER TABLE ステートメントを使用できます。
ALTER TABLE users ADD INDEX idx_age (age);
このコードは、users テーブルに age 列に対するインデックス idx_age を追加します。
CREATE INDEX を使用する
CREATE TABLE ステートメントとは別に、CREATE INDEX ステートメントを使用してインデックスを作成することもできます。
CREATE INDEX idx_city ON users (city);
SQL GUI ツールを使用する
pgAdmin や DBeaver などの SQL GUI ツールを使用して、インデックスを作成することもできます。
これらのツールは、インデックスの作成を視覚的に行うことができるので、初心者にとって使いやすいというメリットがあります。
- テーブル作成時にインデックスを作成したい場合は、
CREATE TABLEステートメント内で直接インデックスを作成するのが最も効率的です。 - 複雑なインデックスを作成したい場合は、
CREATE INDEXステートメントを使用するのが柔軟性があります。 - 初心者の場合は、SQL GUI ツールを使用するのが最も簡単です。
- PostgreSQL の
contribモジュールには、btree_gistやbrinなどの特殊なインデックスを作成するためのツールが含まれています。 - PostgreSQL の拡張機能を使用して、独自のインデックスを作成することもできます。
これらの方法は、より高度なニーズを持つユーザー向けです。
postgresql
