インデックスの利用と作成

PostgreSQLでクエリ性能を改善する

Amy McCarty

Instructor

インデックスの概要

概要

検索を高速化するため列キーを整列して作成
書籍の索引に似る
データ位置への参照

理由

クエリが高速

適用箇所

よくフィルターする列
主キー

インデックスの例

ingredient	recipe
tomatoes	spaghetti & meatballs
green onions	fried rice
eggs	fried rice
ground beef	spaghetti & meatballs
pasta	spaghetti & meatballs
rice	fried rice
soy sauce	fried rice

SELECT *
FROM cookbook
WHERE recipe = 'fried rice'

キー兼ポインタとしてのインデックス

2つの表。インデックス表には recipe 列と連番のポインタ列（_12〜_18）。インデックス付き表は左の表にポインタ列を追加。矢印で、Index 表の fried rice の4件が、Table with Index の4件に対応。

既存インデックスの確認

pg_tables

information_schema に類似
- Postgres 固有
データベースのメタデータ

既存インデックスの確認

pg_tables

information_schema に類似
- Postgres 固有
データベースのメタデータ

SELECT * FROM pg_indexes

schemaname	tablename	indexname	tablespace	indexdef
food	dinner	recipe_index	null	CREATE INDEX recipe_index ...

インデックスの作成

食材、レシピ、提供人数の列がある食卓。 tomatoes - spaghetti & meatballs - 4. green onions - fried rice - 2. eggs - fried rice - 2. ground beef - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. rice - fried rice - 2. soy sauce - fried rice - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);

使うべきか、避けるべきか

インデックスを使う場面

大規模テーブル
よく使うフィルター条件
主キー

インデックスを避ける場面

小規模テーブル
NULL が多い列
更新が頻繁なテーブル
- 断片化しやすい
- データを書き込み先が2か所

更新が頻繁なテーブル

2つの表。インデックス表には recipe 列と連番のポインタ列（_12〜_19）。インデックス付き表は左の表にポインタ列を追加。矢印で、Index 表の spaghetti and meatballs の4件が、Table with Index の4件に対応。3件は Index 表上部にまとまり、1件は最下部（basil の新規行に対応）。

インデックスの評価

クエリプランナー

大きな鍋を囲む複数のシェフ

EXPLAIN
SELECT * 
FROM cookbook

クエリプラン

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)

コスト（時間）の推定

練習しましょう！

PostgreSQLでクエリ性能を改善する