OSSDB標準教科書8章 パフォーマンスチューニング
OSSDB標準教科書
インデックス
- シーケンシャルスキャン・フルスキャン
- 表全体を検索
- インデックススキャン
- インデックスから検索
- 【補】インデックスは探索木
- B木
- 検索の計算オーダーはO(log(n))
主キーのインデックス
- 最も有効
- 検索条件や結合条件などで利用されることが多いから
- 自動的についてる
\d prod Table "public.prod" Column | Type | Collation | Nullable | Default -----------+---------+-----------+----------+--------- prod_id | integer | | not null | prod_name | text | | | price | integer | | | Indexes: "prod_pkey" PRIMARY KEY, btree (prod_id) Referenced by: TABLE "orders" CONSTRAINT "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
インデックスの作成
\d orders Table "public.orders" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+--------- order_id | integer | | not null | order_date | timestamp without time zone | | | customer_id | integer | | | prod_id | integer | | | qty | integer | | | Indexes: "orders_pkey" PRIMARY KEY, btree (order_id) Foreign-key constraints: "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
orders
表のcustomer_id
列にインデックスを作成
CREATE INDEX orders_customer_id_idx ON orders(customer_id);
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
+ "orders_customer_id_idx" btree (customer_id)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
"orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
- 複合インデックス
- 複数列からなるインデックス
- 【補】順番に使わないと効かないので注意する
インデックスを削除する
DROP INDEX orders_customer_id_idx;
\d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | not null |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
- "orders_customer_id_idx" btree (customer_id)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
"orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
インデックスは万能ではない
- UPDATEのたびにインデックスも更新される
- 行が多いと負荷が増す
- あまり書き換わらない列のほうが向いている
- 列の値が適度にばらついていないと、シーケンシャルスキャンの方が効率がよいこともある
SQL実行プランの分析
- EXPLAIN
- SQLがDB内部で実際にどのように処理されるか確認
インデックスが存在しない場合のSQL実行プラン
EXPLAIN SELECT * FROM zip WHERE newzip = '1500002';
QUERY PLAN -------------------------------------------------------- Seq Scan on zip (cost=0.00..4212.12 rows=1 width=140) Filter: (newzip = '1500002'::bpchar) (2 rows)
Seq Scan
... シーケンシャルなフルスキャン
インデックスが存在する場合のSQL実行プラン
CREATE INDEX zip_newzip_idx ON zip(newzip);
Table "public.zip" Column | Type | Collation | Nullable | Default -----------+--------------+-----------+----------+--------- lgcode | character(5) | | | oldzip | character(5) | | | newzip | character(7) | | | prefkana | text | | | citykana | text | | | areakana | text | | | pref | text | | | city | text | | | area | text | | | largearea | integer | | | koaza | integer | | | choume | integer | | | smallarea | integer | | | change | integer | | | reason | integer | | | Indexes: "zip_newzip_idx" btree (newzip)
EXPLAIN SELECT * FROM zip WHERE newzip = '1500002';
QUERY PLAN ---------------------------------------------------------------------------- Index Scan using zip_newzip_idx on zip (cost=0.42..8.44 rows=1 width=140) Index Cond: (newzip = '1500002'::bpchar) (2 rows)
Index Scan using zip_newzip_idx
... インデックスを使ってくれるようだ- 検索対象1行
インデックスが存在しても必ず使われるわけではない
- 使っても効果がないので使わないでくれるケース
CREATE INDEX zip_largearea_idx ON zip(largearea); EXPLAIN SELECT * FROM zip WHERE largearea = 0;
- largearea列はほとんど0なので、検索条件
largearea = 0
では
インデックスはあまり意味がない
QUERY PLAN ------------------------------------------------------------- Seq Scan on zip (cost=0.00..4212.12 rows=121504 width=140) Filter: (largearea = 0) (2 rows)
largearea = 1
ならバッチリ効く
EXPLAIN SELECT * FROM zip WHERE largearea = 1;
QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using zip_largearea_idx on zip (cost=0.42..696.15 rows=2746 width=140) Index Cond: (largearea = 1) (2 rows)
- 【補】
<>
だとインデックス効かない- インデックスの探索は木の探索で、大小比較で絞り込んでいくわけだから、そりゃそうだ
EXPLAIN SELECT * FROM zip WHERE largearea <> 0;
QUERY PLAN ----------------------------------------------------------- Seq Scan on zip (cost=0.00..4212.12 rows=2746 width=140) Filter: (largearea <> 0) (2 rows)
バキューム処理
PostgreSQLのデータ管理方式
- PostgreSQLのデータ削除は、「削除した印」をつけてるだけ
- 功
- 更新・削除処理の時間面
- 罪
- ストレージの空間面
- 検索処理の時間面
- 功
VACUUMとVACUUM FULL
- VACUUM
- 不要になった行データを回収して再利用可能にする
- 【補】GCみたいな気持ち
- ファイルサイズは変わらない
- 不要になった行データを回収して再利用可能にする
- VACUUM FULL
- さらに行データの物理的な配置を移動させ、ファイルサイズを縮小
- ロックを伴う
VACUUM ANALYZE
- ANALYZE
- EXPLAINに利用される統計情報の更新
- VACUUM ANALYZE
- ANALYZEをVACUUMと同時に発行
自動バキュームデーモン
- VACUUMとANALYZEを自動実行してくれる
- PostgreSQLサーバー側
ps ax | grep autovacuum
26 ? Ss 0:00 postgres: autovacuum launcher
クラスタ
- 行データをインデックスに従って物理的に再配置
- インデックスを使って検索される行データが物理的にまとまる
- ディスクアクセスが減り、検索性能向上が期待される
CLUSTER orders;
- 初回はどのインデックスを使うのかわからないのでエラー
ERROR: there is no previously clustered index for table "orders"
- どのインデックスを使うか、
USING
句で教えてやる
CLUSTER orders USING orders_pkey;
CLUSTER
- どのインデックスを使ってクラスタ化されたか記録される
\d orders Table "public.orders" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+--------- order_id | integer | | not null | order_date | timestamp without time zone | | | customer_id | integer | | | prod_id | integer | | | qty | integer | | | Indexes: - "orders_pkey" PRIMARY KEY, btree (order_id) + "orders_pkey" PRIMARY KEY, btree (order_id) CLUSTER Foreign-key constraints: "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) "orders_prod_id_fkey" FOREIGN KEY (prod_id) REFERENCES prod(prod_id)
- 次回以降は
USING
句不要
CLUSTER orders;
CLUSTER